事务与锁

一、事务基础

1.1 事务概念

1.1.1 什么是事务

事务：一组数据库操作，要么全部成功，要么全部失败。

东巴文理解：

事务 = 原子操作
转账操作 = 扣款 + 到账
要么都成功，要么都失败
不能只完成一半

1.1.2 ACID特性

原子性（Atomicity）：

• 事务是不可分割的工作单位
• 要么全部完成，要么全部不完成

一致性（Consistency）：

• 事务必须使数据库从一个一致性状态变换到另一个一致性状态
• 数据完整性约束不被破坏

隔离性（Isolation）：

• 多个事务并发执行时，一个事务的执行不应影响其他事务的执行

持久性（Durability）：

• 事务一旦提交，对数据库的改变是永久性的

1.2 事务控制语句

1.2.1 开启事务

语法格式：

START TRANSACTION;
-- 或
BEGIN;

示例：

-- 开启事务
START TRANSACTION;

-- 执行SQL语句
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

1.2.2 提交事务

语法格式：

COMMIT;

示例：

-- 转账事务
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

1.2.3 回滚事务

语法格式：

ROLLBACK;

示例：

-- 转账事务
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 发现错误，回滚事务
ROLLBACK;

1.2.4 保存点

语法格式：

SAVEPOINT 保存点名;
ROLLBACK TO SAVEPOINT 保存点名;
RELEASE SAVEPOINT 保存点名;

示例：

START TRANSACTION;

INSERT INTO orders (id, amount) VALUES (1, 100);
SAVEPOINT sp1;

INSERT INTO orders (id, amount) VALUES (2, 200);
SAVEPOINT sp2;

INSERT INTO orders (id, amount) VALUES (3, 300);

-- 回滚到保存点sp2
ROLLBACK TO SAVEPOINT sp2;

-- 提交事务
COMMIT;
-- 最终只插入id=1和id=2的记录

1.3 自动提交

1.3.1 查看自动提交

-- 查看自动提交状态
SHOW VARIABLES LIKE 'autocommit';

1.3.2 设置自动提交

-- 关闭自动提交
SET autocommit = 0;

-- 开启自动提交
SET autocommit = 1;

注意：

• MySQL默认开启自动提交
• 每条SQL语句都是一个事务
• 关闭自动提交后需要手动COMMIT

二、隔离级别

2.1 隔离级别介绍

2.1.1 四种隔离级别

2.1.2 并发问题

脏读：

• 读取到其他事务未提交的数据

不可重复读：

• 同一事务中，两次读取同一数据得到不同结果
• 其他事务修改了数据

幻读：

• 同一事务中，两次查询结果数量不同
• 其他事务插入或删除了数据

2.2 查看和设置隔离级别

2.2.1 查看隔离级别

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

-- 查看全局隔离级别
SELECT @@global.transaction_isolation;

2.2.2 设置隔离级别

语法格式：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;

示例：

-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

2.3 各隔离级别演示

2.3.1 读未提交

设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

演示脏读：

-- 会话A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 未提交

-- 会话B
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 读取到未提交的数据
COMMIT;

-- 会话A
ROLLBACK;  -- 回滚

2.3.2 读已提交

设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

演示不可重复读：

-- 会话A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 第一次查询

-- 会话B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- 提交

-- 会话A
SELECT balance FROM accounts WHERE id = 1;  -- 第二次查询，结果不同
COMMIT;

2.3.3 可重复读

设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

演示避免不可重复读：

-- 会话A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 第一次查询

-- 会话B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- 提交

-- 会话A
SELECT balance FROM accounts WHERE id = 1;  -- 第二次查询，结果相同
COMMIT;

注意：MySQL的REPEATABLE READ通过MVCC和Next-Key Lock避免了幻读。

2.3.4 串行化

设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

演示避免幻读：

-- 会话A
START TRANSACTION;
SELECT * FROM accounts WHERE id > 0;  -- 第一次查询

-- 会话B
START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (3, 1000);  -- 被阻塞
COMMIT;

-- 会话A
SELECT * FROM accounts WHERE id > 0;  -- 第二次查询，结果相同
COMMIT;

三、锁机制

3.1 锁的类型

3.1.1 表锁

特点：

• 锁定整张表
• 开销小，加锁快
• 并发度低

语法格式：

LOCK TABLES 表名 READ|WRITE;
UNLOCK TABLES;

示例：

-- 加读锁
LOCK TABLES accounts READ;
SELECT * FROM accounts;
UNLOCK TABLES;

-- 加写锁
LOCK TABLES accounts WRITE;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
UNLOCK TABLES;

3.1.2 行锁

特点：

• 锁定一行或多行
• 开销大，加锁慢
• 并发度高
• InnoDB支持

示例：

-- 行锁（通过事务）
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 加排他锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

3.2 共享锁与排他锁

3.2.1 共享锁（S锁）

特点：

• 允许多个事务同时读取
• 不允许修改

语法格式：

SELECT * FROM 表名 WHERE 条件 LOCK IN SHARE MODE;
-- 或（MySQL 8.0+）
SELECT * FROM 表名 WHERE 条件 FOR SHARE;

示例：

-- 会话A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;  -- 加共享锁

-- 会话B
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;  -- 成功
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 被阻塞
COMMIT;

-- 会话A
COMMIT;  -- 释放锁

3.2.2 排他锁（X锁）

特点：

• 只允许一个事务修改
• 其他事务不能读取或修改

语法格式：

SELECT * FROM 表名 WHERE 条件 FOR UPDATE;

示例：

-- 会话A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 加排他锁

-- 会话B
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 被阻塞
COMMIT;

-- 会话A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- 释放锁

3.3 意向锁

3.3.1 意向锁概念

意向锁：表级锁，表示事务打算在表中的行上加锁。

类型：

• 意向共享锁（IS）：打算在行上加共享锁
• 意向排他锁（IX）：打算在行上加排他锁

作用：

• 提高表锁和行锁的兼容性判断效率
• 避免逐行检查

3.3.2 锁兼容性

3.4 记录锁、间隙锁、临键锁

3.4.1 记录锁（Record Lock）

特点：

• 锁定索引记录
• 防止其他事务修改或删除

示例：

-- 锁定id=1的记录
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

3.4.2 间隙锁（Gap Lock）

特点：

• 锁定索引记录之间的间隙
• 防止其他事务插入
• 不包含记录本身

示例：

-- 锁定id在1到3之间的间隙（不包含1和3）
SELECT * FROM accounts WHERE id > 1 AND id < 3 FOR UPDATE;

3.4.3 临键锁（Next-Key Lock）

特点：

• 记录锁 + 间隙锁
• 锁定记录和前面的间隙
• 防止幻读

示例：

-- 锁定id<=3的记录和间隙
SELECT * FROM accounts WHERE id <= 3 FOR UPDATE;

四、死锁

4.1 死锁概念

4.1.1 什么是死锁

死锁：两个或多个事务互相等待对方释放锁，导致永久阻塞。

示例：

-- 会话A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 锁定id=1

-- 会话B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- 锁定id=2

-- 会话A
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待id=2的锁

-- 会话B
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- 等待id=1的锁
-- 死锁发生

4.2 死锁检测

4.2.1 查看死锁

-- 查看死锁信息
SHOW ENGINE INNODB STATUS;

输出：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-01 12:00:00 0x7f8a8c0b6700
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 140234567891456, query id 100 localhost root updating
UPDATE accounts SET balance = balance + 100 WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` trx id 12345 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
...

4.2.2 死锁检测配置

-- 查看死锁检测配置
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- 开启死锁检测
SET GLOBAL innodb_deadlock_detect = ON;

4.3 死锁预防

4.3.1 预防策略

1. 按固定顺序访问表：

-- 不推荐：不同事务访问顺序不同
-- 事务A：先访问表1，再访问表2
-- 事务B：先访问表2，再访问表1

-- 推荐：所有事务按相同顺序访问
-- 事务A：先访问表1，再访问表2
-- 事务B：先访问表1，再访问表2

2. 大事务拆小事务：

-- 不推荐：大事务
START TRANSACTION;
-- 大量操作
COMMIT;

-- 推荐：小事务
START TRANSACTION;
-- 少量操作
COMMIT;

3. 尽量使用索引访问：

-- 不推荐：无索引，锁全表
UPDATE accounts SET balance = balance - 100 WHERE name = '张三';

-- 推荐：有索引，锁行
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

4. 设置锁等待超时：

-- 查看锁等待超时时间
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 设置锁等待超时时间（秒）
SET innodb_lock_wait_timeout = 10;

4.4 死锁处理

4.4.1 自动回滚

MySQL检测到死锁后，自动回滚其中一个事务。

查看被回滚的事务：

SHOW ENGINE INNODB STATUS;

4.4.2 手动处理

-- 查看当前运行的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看当前的锁
SELECT * FROM information_schema.INNODB_LOCKS;

-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 杀死事务
KILL 事务ID;

五、MVCC

5.1 MVCC概念

5.1.1 什么是MVCC

MVCC（多版本并发控制）：通过保存数据的历史版本，实现非阻塞读。

优点：

• 读写不冲突
• 提高并发性能
• 实现快照读

5.2 实现原理

5.2.1 隐藏字段

InnoDB每行数据包含：

• DB_TRX_ID：最后修改该行的事务ID
• DB_ROLL_PTR：回滚指针，指向undo log
• DB_ROW_ID：行ID（无主键时使用）

5.2.2 Undo Log

作用：

• 保存数据的历史版本
• 支持事务回滚
• 支持MVCC

示例：

当前数据：
id=1, name='张三', DB_TRX_ID=100, DB_ROLL_PTR=0x123

Undo Log（旧版本）：
id=1, name='李四', DB_TRX_ID=99, DB_ROLL_PTR=0x456

5.2.3 Read View

Read View：事务进行快照读时产生的读视图。

包含内容：

• m_ids：当前活跃的事务ID列表
• min_trx_id：最小活跃事务ID
• max_trx_id：下一个事务ID
• creator_trx_id：创建该Read View的事务ID

可见性判断：

1. DB_TRX_ID < min_trx_id：可见
2. DB_TRX_ID >= max_trx_id：不可见
3. DB_TRX_ID在m_ids中：不可见
4. DB_TRX_ID不在m_ids中：可见

5.3 快照读与当前读

5.3.1 快照读

特点：

• 读取历史版本
• 不加锁
• 提高并发性能

示例：

-- 快照读
SELECT * FROM accounts WHERE id = 1;

5.3.2 当前读

特点：

• 读取最新版本
• 加锁
• 保证数据一致性

示例：

-- 当前读
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
INSERT INTO accounts VALUES (...);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
DELETE FROM accounts WHERE id = 1;

六、实战案例

6.1 转账事务

-- 创建账户表
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);

INSERT INTO accounts VALUES
(1, '张三', 1000.00),
(2, '李四', 1000.00);

-- 转账事务
DELIMITER //
CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amount DECIMAL(10, 2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '转账失败' AS result;
    END;
    
    START TRANSACTION;
    
    -- 检查余额
    IF (SELECT balance FROM accounts WHERE id = from_id) >= amount THEN
        -- 扣款
        UPDATE accounts SET balance = balance - amount WHERE id = from_id;
        -- 到账
        UPDATE accounts SET balance = balance + amount WHERE id = to_id;
        COMMIT;
        SELECT '转账成功' AS result;
    ELSE
        ROLLBACK;
        SELECT '余额不足' AS result;
    END IF;
END //
DELIMITER ;

-- 调用存储过程
CALL transfer(1, 2, 100.00);

6.2 库存扣减

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    stock INT
);

INSERT INTO products VALUES (1, 'iPhone', 100);

-- 扣减库存（避免超卖）
START TRANSACTION;

-- 使用排他锁锁定商品
SELECT stock FROM products WHERE id = 1 FOR UPDATE;

-- 检查库存
IF (SELECT stock FROM products WHERE id = 1) > 0 THEN
    UPDATE products SET stock = stock - 1 WHERE id = 1;
    COMMIT;
    SELECT '扣减成功' AS result;
ELSE
    ROLLBACK;
    SELECT '库存不足' AS result;
END IF;

6.3 批量插入事务

-- 批量插入数据
START TRANSACTION;

INSERT INTO orders (id, amount) VALUES (1, 100);
INSERT INTO orders (id, amount) VALUES (2, 200);
INSERT INTO orders (id, amount) VALUES (3, 300);

COMMIT;

七、本章小结

7.1 核心要点

✅ 事务具有ACID特性 ✅ 隔离级别解决并发问题 ✅ 锁机制保证数据一致性 ✅ MVCC实现非阻塞读 ✅ 死锁需要预防和处理

7.2 验证清单

完成本章学习后，请确认您能够：

• 理解事务的ACID特性
• 使用事务控制语句
• 设置和理解隔离级别
• 理解锁的类型和机制
• 处理死锁问题