MySQL 第六天 核心基础：事务与锁机制（附实战 SQL + 高频面试题）

一、核心知识点：事务（Transaction）

事务是数据库中一组不可分割的操作序列，要么全部执行成功，要么全部执行失败，核心遵循 ACID 原则：

• 原子性（Atomicity）：事务是最小执行单位，不可拆分（比如转账时 “扣钱” 和 “加钱” 必须同时成功 / 失败）。

• 一致性（Consistency）：事务执行前后，数据库的完整性约束（如主键唯一、金额总和不变）保持不变。

• 隔离性（Isolation）：多个事务并发执行时，彼此互不干扰（隔离级别决定干扰程度）。

• 持久性（Durability）：事务提交后，修改永久生效，即使数据库崩溃也不会丢失。

特性	全称	核心含义	实现机制
A	原子性	要么全成功，要么全失败	Undo Log 回滚
C	一致性	事务前后数据合法完整	约束 + ACID 共同保证
I	隔离性	并发事务互不干扰	MVCC + 锁机制
D	持久性	提交后永久生效	Redo Log 落盘

2. 事务的控制语句（实战 SQL）

-- 1. 创建账户表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(50) NOT NULL COMMENT '用户名',
    balance DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '账户余额',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 插入测试数据
INSERT INTO account (user_name, balance) VALUES 
('张三', 1000.00), 
('李四', 500.00);

-- 3. 事务基础操作（转账案例：张三给李四转200元）
-- 开启事务
START TRANSACTION; 
-- 步骤1：张三扣200元
UPDATE account SET balance = balance - 200 WHERE user_name = '张三';
-- 步骤2：李四加200元
UPDATE account SET balance = balance + 200 WHERE user_name = '李四';

-- 验证数据（未提交前，仅当前会话可见）
SELECT * FROM account;

-- 提交事务（确认修改，永久生效）
COMMIT;

-- 若执行中出错，回滚事务（撤销所有操作）
-- ROLLBACK;

-- 4. 事务保存点（可选）
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_name = '张三';
SAVEPOINT sp1; -- 设置保存点
UPDATE account SET balance = balance + 100 WHERE user_name = '李四';
ROLLBACK TO sp1; -- 回滚到保存点（仅撤销第二个更新）
COMMIT;

3. 事务隔离级别（重点）

MySQL 默认隔离级别是REPEATABLE READ（可重复读），可通过以下 SQL 查看 / 修改：

-- 1. 查看当前会话隔离级别
SELECT @@SESSION.transaction_isolation;
-- 查看全局隔离级别
SELECT @@GLOBAL.transaction_isolation;

-- 2. 修改隔离级别（会话级）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- 可重复读（默认）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;     -- 串行化

-- 3. 验证隔离级别问题（以“读未提交”为例，模拟脏读）
-- 会话1：开启事务，更新数据但不提交
START TRANSACTION;
UPDATE account SET balance = balance - 200 WHERE user_name = '张三';

-- 会话2：设置隔离级别为读未提交，读取未提交的数据（脏读）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM account WHERE user_name = '张三'; -- 能读到会话1未提交的800元

-- 会话1：回滚事务
ROLLBACK;

-- 会话2：再次读取，数据恢复为1000元（脏读问题体现）
SELECT balance FROM account WHERE user_name = '张三';

隔离级别	脏读	不可重复读	幻读	特点
READ UNCOMMITTED	✅	✅	✅	最低级别，性能最高
READ COMMITTED	❌	✅	✅	解决脏读，Oracle 默认级别
REPEATABLE READ	❌	❌	❌	解决脏读 + 不可重复读，MySQL 默认（通过间隙锁解决幻读）
SERIALIZABLE	❌	❌	❌	最高级别，完全串行执行，性能最低

二、核心知识点：锁机制

1.锁的分类（基础）

• 按粒度分：行锁（InnoDB）、表锁（MyISAM/InnoDB）、页锁；

• 按性质分：共享锁（S 锁，读锁）、排他锁（X 锁，写锁）；

• 按意图分：意向共享锁（IS）、意向排他锁（IX）。

2. 实战 SQL：InnoDB 行锁 / 表锁操作

-- 1. 排他锁（X锁）：默认DML操作（INSERT/UPDATE/DELETE）自动加排他锁
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_name = '张三'; -- 行级排他锁
-- 此时其他会话无法修改张三的记录，直到当前事务提交/回滚

-- 2. 共享锁（S锁）：通过SELECT ... LOCK IN SHARE MODE添加
START TRANSACTION;
SELECT balance FROM account WHERE user_name = '张三' LOCK IN SHARE MODE; -- 行级共享锁
-- 其他会话可加共享锁，但无法加排他锁

-- 3. 手动加表锁（InnoDB也支持，慎用）
LOCK TABLES account WRITE; -- 写表锁（阻塞所有读写）
LOCK TABLES account READ;  -- 读表锁（允许其他读，阻塞写）
UNLOCK TABLES; -- 释放表锁

-- 4. 验证行锁与表锁的区别
-- 会话1：行锁（仅锁定张三的记录）
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_name = '张三';

-- 会话2：修改李四的记录（不受影响，行锁粒度小）
UPDATE account SET balance = balance + 100 WHERE user_name = '李四'; -- 可正常执行

-- 会话2：修改张三的记录（被阻塞，直到会话1提交/回滚）
UPDATE account SET balance = balance + 100 WHERE user_name = '张三'; -- 阻塞

3. 间隙锁（Next-Key Lock，重点）

InnoDB 在REPEATABLE READ级别下，会对查询范围加间隙锁，防止幻读：

-- 1. 插入测试数据（制造间隙：id=1、2，间隙为(2, +∞)）
INSERT INTO account (user_name, balance) VALUES ('王五', 800.00); -- id=3

-- 2. 会话1：范围查询加间隙锁
START TRANSACTION;
SELECT * FROM account WHERE id > 2 FOR UPDATE; -- 锁定id>2的记录+间隙（2,+∞）

-- 3. 会话2：插入id=4的记录（被阻塞，幻读防护）
INSERT INTO account (id, user_name, balance) VALUES (4, '赵六', 600.00); -- 阻塞

-- 4. 会话1：提交事务，会话2解除阻塞
COMMIT;

三、高频面试题（附解析）

1. 基础题：事务的 ACID 分别指什么？InnoDB 如何保证持久性？

• A（原子性）：通过 Undo Log 实现，事务回滚时撤销已执行的操作；

• C（一致性）：由原子性、隔离性、持久性共同保证，外加约束（主键、外键）；

• I（隔离性）：通过锁机制 + MVCC（多版本并发控制）实现；

• D（持久性）：通过 Redo Log 实现，事务提交时先写 Redo Log 到磁盘，即使数据库崩溃，重启后可通过 Redo Log 恢复数据。

2.实战题：InnoDB 行锁为什么会升级为表锁？如何避免？

• 原因：行锁基于索引加锁，若查询未命中索引（如用非索引字段 WHERE 条件），InnoDB 会升级为表锁；

-- account表仅id是索引，user_name无索引
START TRANSACTION;
UPDATE account SET balance = 0 WHERE user_name = '张三'; -- 未命中索引，表锁

避免方案：

1.确保 UPDATE/DELETE 的 WHERE 条件字段加索引；

2.避免使用 SELECT * FOR UPDATE（精准查询字段）；

3.控制事务大小，缩短锁持有时间。

3. 综合题：转账场景中，如何避免死锁？

死锁原因：两个事务互相持有对方需要的锁（如 A 扣张三、B 扣李四，同时 A 等李四的锁、B 等张三的锁）。

-- 方案1：固定加锁顺序（所有事务先锁id小的记录）
-- 事务1
START TRANSACTION;
SELECT * FROM account WHERE id = 1 FOR UPDATE; -- 先锁id=1（张三）
SELECT * FROM account WHERE id = 2 FOR UPDATE; -- 再锁id=2（李四）
UPDATE account SET balance = balance - 200 WHERE id = 1;
UPDATE account SET balance = balance + 200 WHERE id = 2;
COMMIT;

-- 事务2（同顺序）
START TRANSACTION;
SELECT * FROM account WHERE id = 1 FOR UPDATE; -- 先锁id=1
SELECT * FROM account WHERE id = 2 FOR UPDATE; -- 再锁id=2
UPDATE account SET balance = balance + 200 WHERE id = 1;
UPDATE account SET balance = balance - 200 WHERE id = 2;
COMMIT;

-- 方案2：设置事务超时时间
SET innodb_lock_wait_timeout = 5; -- 锁等待5秒超时，自动回滚
-- 方案3：减少锁持有时间（简化事务逻辑，快速提交）