MySQL事务与锁机制

引言

事务和锁是数据库并发控制的核心。本文系统讲解MySQL的事务和锁机制。

一、事务基础

1.1 ACID特性

-- Atomicity (原子性)：全部成功或全部失败
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Consistency (一致性)：数据完整性约束

-- Isolation (隔离性)：事务间互不影响

-- Durability (持久性)：提交后永久保存

1.2 事务操作

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
ROLLBACK TO sp1;

二、隔离级别

2.1 四种隔离级别

-- 1. READ UNCOMMITTED (读未提交)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 问题：脏读、不可重复读、幻读

-- 2. READ COMMITTED (读已提交)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 问题：不可重复读、幻读

-- 3. REPEATABLE READ (可重复读，MySQL默认)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 问题：幻读（InnoDB通过MVCC解决）

-- 4. SERIALIZABLE (串行化)
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 无并发问题，但性能最差

-- 查看隔离级别
SELECT @@transaction_isolation;

2.2 并发问题

-- 脏读：读到未提交的数据
-- 事务A修改但未提交，事务B读到修改后的数据

-- 不可重复读：同一查询多次结果不同
-- 事务A读取数据，事务B修改并提交，事务A再次读取结果不同

-- 幻读：范围查询时出现新数据
-- 事务A查询范围数据，事务B插入数据并提交，事务A再次查询出现新行

三、MVCC机制

3.1 MVCC原理

-- Multi-Version Concurrency Control（多版本并发控制）
-- 通过保存数据的多个版本实现读写并发

-- 关键字段：
-- trx_id: 事务ID
-- roll_pointer: 回滚指针
-- DB_ROW_ID: 隐藏主键

3.2 Read View

-- Read View包含：
-- 1. m_ids: 活跃事务ID列表
-- 2. min_trx_id: 最小活跃事务ID
-- 3. max_trx_id: 下一个要分配的事务ID
-- 4. creator_trx_id: 创建Read View的事务ID

-- 可见性判断：
-- trx_id < min_trx_id: 可见
-- trx_id >= max_trx_id: 不可见
-- min_trx_id <= trx_id < max_trx_id:
--   trx_id在m_ids中: 不可见
--   trx_id不在m_ids中: 可见

四、锁机制

4.1 锁类型

-- 1. 表锁
LOCK TABLES users READ;  -- 读锁
LOCK TABLES users WRITE; -- 写锁
UNLOCK TABLES;

-- 2. 行锁（InnoDB）
-- 共享锁（S锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（X锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 3. 意向锁
-- 意向共享锁（IS）
-- 意向排他锁（IX）

-- 4. 间隙锁（Gap Lock）
-- 锁定索引记录间的间隙

-- 5. 临键锁（Next-Key Lock）
-- 行锁 + 间隙锁

4.2 锁兼容性

       | S锁 | X锁 |
-------|-----|-----|
S锁    | ✓   | ✗   |
X锁    | ✗   | ✗   |

五、死锁

5.1 死锁示例

-- 事务A
START TRANSACTION;
UPDATE users SET name = 'A' WHERE id = 1;
-- 等待...
UPDATE users SET name = 'A' WHERE id = 2;

-- 事务B
START TRANSACTION;
UPDATE users SET name = 'B' WHERE id = 2;
-- 等待...
UPDATE users SET name = 'B' WHERE id = 1;

-- 结果：死锁！

5.2 死锁检测与处理

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

-- 死锁检测
-- InnoDB自动检测死锁并回滚其中一个事务

-- 预防死锁：
-- 1. 按相同顺序访问资源
-- 2. 缩短事务时间
-- 3. 降低隔离级别
-- 4. 添加合适的索引

六、性能优化

6.1 锁优化

-- 1. 使用索引避免全表扫描
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. 缩小事务范围
-- ✗ 不好
START TRANSACTION;
-- 大量业务逻辑
-- 数据库操作
COMMIT;

-- ✓ 好
-- 业务逻辑
START TRANSACTION;
-- 数据库操作
COMMIT;

-- 3. 使用较低的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 4. 避免长事务
-- 设置超时时间
SET innodb_lock_wait_timeout = 50;

七、监控与诊断

7.1 查看锁信息

-- 查看当前锁
SELECT * FROM performance_schema.data_locks;

-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;

-- 查看事务
SELECT * FROM information_schema.innodb_trx;

-- 查看死锁
SHOW ENGINE INNODB STATUS;