MySQL事务管理：事务控制与锁机制详解

杨凯凡

于 2025-05-20 09:05:11 发布

阅读量791

点赞数 20

分类专栏：数据库从入门到高可用：SQL与优化实战文章标签： mysql 数据库

本文链接：https://blog.csdn.net/ytwoshuai/article/details/148079899

版权

数据库从入门到高可用：SQL与优化实战专栏收录该内容

13 篇文章

订阅专栏

引言

各位数据库爱好者们好！今天我们要深入探讨MySQL中确保数据一致性和完整性的两大核心机制——事务和锁 🛡️。事务就像数据库操作的"原子弹"，要么全部成功，要么全部失败；而锁机制则是数据库的"交通警察"，协调并发访问避免混乱。在当今高并发的应用环境中，理解这些机制至关重要！本教程将带你从理论到实践，全面掌握MySQL事务和锁的精髓，让你的数据库既能高效并发又能保持数据一致！💪

一、事务的ACID特性：数据库的四大支柱

1.1 原子性(Atomicity)：全有或全无

原子性就像数字支付，要么转账成功，要么完全回退 💸：

特点：

事务是不可分割的最小工作单元
所有操作要么全部成功提交(COMMIT)
任何一个操作失败都会导致整个事务回滚(ROLLBACK)

示例：

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 如果第二条语句失败，第一条也会自动回滚
COMMIT;

1.2 一致性(Consistency)：数据始终合法

一致性就像会计的借贷平衡，数据必须永远满足预定义的规则 ⚖️：

特点：

事务执行前后，数据库从一个一致状态变到另一个一致状态
保证所有约束（主键、外键、唯一性等）不被破坏
应用层定义的业务规则也能得到维护

示例：

-- 转账事务保证了总金额不变
-- 事务前：A(500) + B(500) = 1000
-- 事务后：A(400) + B(600) = 1000

1.3 隔离性(Isolation)：并发事务互不干扰

隔离性就像独立办公室，每个事务感觉不到其他事务的存在 🚪：

特点：

并发执行的事务相互隔离
通过锁机制实现不同级别的隔离
防止脏读、不可重复读、幻读等问题

1.4 持久性(Durability)：永久保存

持久性就像刻在石头上的字，一旦提交就永久保存 🗿：

特点：

事务提交后，修改将永久保存在数据库中
即使系统崩溃，数据也不会丢失
通过redo log等机制保证

二、事务隔离级别：并发控制的平衡术

2.1 四种标准隔离级别

隔离级别就像社交距离，决定事务间的"亲密程度" 📏：

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	❌	❌	❌	⚡⚡⚡⚡
READ COMMITTED	✅	❌	❌	⚡⚡⚡
REPEATABLE READ (默认)	✅	✅	❌	⚡⚡
SERIALIZABLE	✅	✅	✅	⚡

2.2 隔离问题详解

脏读：读到其他事务未提交的数据 🧻：

-- 事务A
START TRANSACTION;
UPDATE users SET age = 30 WHERE id = 1; -- 未提交

-- 事务B（READ UNCOMMITTED能读到A未提交的修改）
SELECT age FROM users WHERE id = 1; -- 结果为30

不可重复读：同一事务内两次读取结果不同 🔄：

-- 事务A
START TRANSACTION;
SELECT age FROM users WHERE id = 1; -- 结果为25

-- 事务B
UPDATE users SET age = 30 WHERE id = 1;
COMMIT;

-- 事务A再次读取
SELECT age FROM users WHERE id = 1; -- 结果变为30

幻读：同一事务内两次查询返回的行数不同 👻：

-- 事务A
START TRANSACTION;
SELECT COUNT(*) FROM users WHERE age > 20; -- 返回10

-- 事务B
INSERT INTO users(name, age) VALUES('新用户', 25);
COMMIT;

-- 事务A再次查询
SELECT COUNT(*) FROM users WHERE age > 20; -- 返回11

2.3 设置隔离级别

查看当前隔离级别：

SELECT @@transaction_isolation;
-- 或(旧版本)
SELECT @@tx_isolation;

设置会话级隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

设置全局隔离级别（需重启生效）：

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

三、显式事务控制：精准掌控事务边界

3.1 基本事务操作

事务控制就像开关，精确控制操作范围 🔘：

开始事务：

START TRANSACTION;
-- 或
BEGIN;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

保存点(Savepoint)：

START TRANSACTION;
INSERT INTO orders VALUES(...);
SAVEPOINT sp1;
UPDATE inventory SET ...;
-- 可以回滚到保存点
ROLLBACK TO sp1;
COMMIT;

3.2 事务实战案例

银行转账经典案例 💰：

START TRANSACTION;

-- 检查账户余额是否充足
SELECT balance INTO @balance FROM accounts WHERE user_id = 1 FOR UPDATE;
IF @balance < 100 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
END IF;

-- 执行转账
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 记录交易日志
INSERT INTO transaction_log(from_user, to_user, amount) VALUES(1, 2, 100);

COMMIT;

四、锁机制详解：并发控制的基石

4.1 锁的类型矩阵

MySQL锁就像多层次的安保系统 🚧：

锁类型	描述	兼容性	使用场景
共享锁(S锁)	允许其他事务读但不能写	与S锁兼容，与X锁不兼容	SELECT … LOCK IN SHARE MODE
排他锁(X锁)	禁止其他事务读和写	与其他任何锁都不兼容	SELECT … FOR UPDATE
意向共享锁(IS)	表示事务打算在表某些行加S锁	表级锁，提高冲突检测效率	自动添加
意向排他锁(IX)	表示事务打算在表某些行加X锁	表级锁，提高冲突检测效率	自动添加

4.2 行锁与表锁

行级锁：

锁定索引记录（即使表没有索引，InnoDB也会创建隐藏聚簇索引）
开销大，并发度高
InnoDB默认使用行锁

表级锁：

锁定整个表
开销小，并发度低
MyISAM只支持表锁

锁升级：
当行锁太多或涉及无索引列更新时，InnoDB可能升级为表锁

4.3 加锁方式示例

显式加锁：

-- 加共享锁（其他事务可读不可写）
SELECT * FROM accounts WHERE user_id = 1 LOCK IN SHARE MODE;

-- 加排他锁（其他事务不可读不可写）
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;

隐式加锁：

INSERT/UPDATE/DELETE自动加排他锁
普通SELECT不加锁（快照读）

五、死锁检测与处理：破解并发僵局

5.1 死锁产生条件

死锁就像交通堵塞，多个事务互相等待 🔄：

必要条件：

互斥条件：资源一次只能被一个事务占用
请求与保持：事务持有资源同时请求新资源
不剥夺条件：已分配资源不能被强制剥夺
循环等待：事务间形成头尾相接的等待环

5.2 死锁示例

-- 事务A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 锁住id=1
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; -- 等待B释放id=2

-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE user_id = 2; -- 锁住id=2
UPDATE accounts SET balance = balance + 50 WHERE user_id = 1; -- 等待A释放id=1

5.3 死锁处理策略

MySQL自动检测：

使用等待图(wait-for graph)检测死锁
选择代价小的事务作为牺牲者(victim)回滚
返回1213错误：Deadlock found when trying to get lock

避免死锁的最佳实践：

保持事务短小精悍
按固定顺序访问表和行
合理设计索引，减少锁冲突
使用低隔离级别（如READ COMMITTED）
添加锁等待超时：innodb_lock_wait_timeout=50

手动处理死锁：

-- 查看当前锁情况
SHOW ENGINE INNODB STATUS;

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

-- 终止造成死锁的事务
KILL [transaction_id];

六、高级锁机制与应用

6.1 间隙锁(Gap Lock)：解决幻读的利器

间隙锁锁定索引记录间的"间隙" 🔍：

特点：

只在REPEATABLE READ及以上隔离级别生效
防止其他事务在范围内插入新记录（解决幻读）
InnoDB自动为范围查询加间隙锁

示例：

-- 锁定age在20-30之间的间隙，防止插入age=25的新记录
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;

6.2 临键锁(Next-Key Lock)：行锁+间隙锁

临键锁是InnoDB默认的行锁算法 🔐：

特点：

行锁 + 前间隙锁的组合
既锁定记录本身，也锁定前面的间隙
有效防止幻读和不可重复读

6.3 乐观锁与悲观锁策略

悲观锁（默认）：

认为冲突很常见，先加锁再访问
SELECT ... FOR UPDATE
适合写多读少的高冲突场景

乐观锁：

认为冲突很少，先修改再检查冲突
通过版本号或时间戳实现
适合读多写少的低冲突场景

乐观锁实现示例：

-- 添加version字段
ALTER TABLE products ADD version INT DEFAULT 0;

-- 更新时检查版本
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE product_id = 1001 AND version = 5;
-- 检查影响行数是否为1

七、事务与锁性能优化

7.1 监控事务与锁

查看当前运行事务：

SELECT * FROM information_schema.INNODB_TRX;

查看锁等待情况：

SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

7.2 关键参数调优

InnoDB事务相关参数：

# 事务超时时间(秒)
innodb_lock_wait_timeout = 50

# 死锁检测(开启会消耗资源，高并发可考虑关闭)
innodb_deadlock_detect = ON

# 事务日志缓冲区大小
innodb_log_buffer_size = 16M

# 事务日志文件大小
innodb_log_file_size = 256M