MySQL 行锁与表锁详解
MySQL 行锁与表锁详解
MySQL 的存储引擎从 MyISAM 发展到 InnoDB，锁机制也从表锁进化到了行锁。InnoDB 引擎通过支持事务和行级锁定，在高并发环境下提供了更好的性能表现。
案例分析
假设我们要批量修改订单状态为"待客服确认"的记录。为了演示行锁特性，我们将使用一个简单的表 lock_demo，包含字段 id, key_field, 和 value_field。
-- Transaction A
SET autocommit = 0;
UPDATE lock_demo SET value_field='1001' WHERE id=1;
COMMIT;
-- Transaction B
UPDATE lock_demo SET value_field='2001' WHERE id=2;
UPDATE lock_demo SET value_field='1002' WHERE id=1; -- 等待直到A提交
总结: 当多个事务尝试修改同一行时，后发起的事务会被阻塞，直到前一个事务提交或回滚。这避免了数据不一致问题如脏读等。
行锁变表锁的原因
如果查询条件未使用索引，则 MySQL 可能会使用表锁而非行锁。例如：
-- Transaction A
UPDATE lock_demo SET value_field='1002' WHERE key_field=1;
CREATE INDEX idx_key ON lock_demo(key_field);
-- Transaction B
UPDATE lock_demo SET value_field='2002' WHERE key_field=2;
要点: InnoDB 行锁是基于索引实现的，若索引失效则自动转为表锁。
行锁与表锁的特点
- 行锁：适合高并发场景，但开销较大。
- 表锁：适用于全表更新或多表复杂查询，开销较小但并发能力低。
事务属性
事务具有 ACID 特性（原子性、一致性、隔离性和持久性），确保数据操作的一致性和可靠性。
事务隔离级别
| 隔离级别 | 读一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 读未提交 | 最低 | 是 | 是 | 是 |
| 读已提交 | 语句级 | 否 | 是 | 是 |
| 可重复读 | 事务级 | 否 | 否 | 是 |
| 序列化 | 最高 | 否 | 否 | 否 |
查看当前隔离级别：
SHOW VARIABLES LIKE 'transaction_isolation';
锁类型
- 共享锁：允许多个读操作同时进行。
- 排他锁：阻止其他事务对该行进行任何操作。
示例代码：
-- 获取排他锁
SELECT * FROM lock_demo WHERE id=4 FOR UPDATE;
-- 获取共享锁
SELECT * FROM lock_demo WHERE id=4 LOCK IN SHARE MODE;
表锁操作
-- 加共享读锁
LOCK TABLE myisam_lock READ;
-- 加独占写锁
LOCK TABLE myisam_lock WRITE;
查看锁状态
SHOW OPEN TABLES WHERE In_use > 0;
SHOW STATUS LIKE 'table_locks%';
结论
了解 MySQL 的行锁和表锁机制对于优化数据库性能至关重要。正确使用索引可以有效避免不必要的表锁转换。
