MySQL事务隔离级别与锁总结

最新推荐文章于 2025-12-18 19:34:09 发布

原创最新推荐文章于 2025-12-18 19:34:09 发布 · 200 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

数据库专栏收录该内容

2 篇文章

订阅专栏

本文深入探讨了数据库事务的四种隔离级别：读未提交、读已提交、可重复读和可串行化，以及不同类型的锁：表锁、行锁、间隙锁、读锁、写锁、乐观锁和悲观锁。分析了各种隔离级别和锁机制如何影响并发性能，提供了优化策略。

一、事务隔离级别

读未提交：可以读到其它线程里未提交事务的数据。会出现脏读，脏读的解决办法就是使用读已提交
读已提交：读取到的数据都是已提交事务的数据。会出现不可重复读，比如S1线程的事务处理中，S2线程修改了数据并且S2里的事务已提交，S1在事务提交前再次读取了数据发现和之前读取的不一样了，会造成程序判断逻辑异常，不可重复读的解决办法就是使用可重复读
可重复读：从事务的开始至结束，所有访问到的数据都是一致的，即使数据在访问之后被修改了，还是返回第一次访问的数据。会出现幻读，比如在一个事务中S1，查询了一个表中没有记录R，刚好之后在另外一个事务S2中插入这条记录R，那么S1还傻傻的添加了一条记录R，所以最终数据库中出现了两条记录R，这个就是幻读，幻读的解决办法之一就是使用可串行化，另外间隙锁也可以解决幻读。
可串行化：事务的最高隔离级别，在每个读的数据行上，加上锁，使之不可能互相冲突，因此会导致出现大量的超时现象。比如S1查询了某个表T1，则在相应的行/表上加上X锁，与此同时S2也查询了T1表，则又在表上加上了另外一个X锁，如果此时S1对表T1进行修改，则会阻塞等待S2释放X锁，很有可能会超时。而且死锁的概率也大大提高，比如上面这个情况S1在等待S2释放X锁，如果此时S2也对T1进行修改，也会等待1释放X锁，所以出现了循环等待，即死锁。

常看当前数据库的事务隔离级别：show variables like ‘tx_isolation’;
设置当前连接的事务隔离级别：set tx_isolation=‘repeatable-read’;

二、锁

分类
a. 表锁、行锁、间隙锁（Gap Lock）
b. 读锁、写锁
c. 乐观锁、悲观锁
总结 - 表锁还是行锁：
a. 执行非索引条件查询执行的是表锁。
b. 执行索引查询是否是加行锁，要看Mysql执行计划，因为Mysql执行优化器可能会把数据量小的表索引查询退化为全表扫描，如果是群表扫描则是表锁。
c. 主键索引、唯一索引用的是行锁。
d. 普通索引的查询，要看索引值是否有相同的，如果只有一条走行锁，如果有多条走表锁（参考：https://www.cnblogs.com/zyy1688/p/9983122.html）。
间隙锁：
a. 可以解决幻读问题，需要在RR事务隔离级别下，使用间隙锁来解决幻读的问题。
b. 其实一般实际项目使用分布式锁来解决并发导致的幻读问题。
c. 间隙锁 & 行锁的区别
行锁与行锁之间是互斥的，但是间隙锁与间隙锁之间是不互斥的，举例如下：
表t中的字段c是索引字段，并且存在c=5和c=10的两条记录，分两种情况讨论：
i. 表中存在c=7记录，则sessionB会被block，说明行锁之间是互斥的
ii. 表中不存在c=7记录，则sessionA执行完之后，会把5~10的间隙锁起来，锁起来不让插入新的记录。然后sessionB执行后也是加间隙锁，间隙锁是不互斥的，所以sesionB的sql语句可以执行不阻塞。
行锁分析：
a. 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。
b. 命令为：show status like’innodb_row_lock%’;

c. 对命令执行结果的字段说明如下：
i. Innodb_row_lock_current_waits: 当前正在等待锁定的数量
ii. Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
iii. Innodb_row_lock_time_avg: 每次等待所花平均时间
ix. Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
x. Innodb_row_lock_waits:系统启动后到现在总共等待的次数

d. 尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
探索系列：
a. 探索如下场景
i. S1开始事务
ii. S1查看表table1
iii. S2插入一条记录
iv. S1查看表table1，应该是看不到这条记录的
v. S1修改table1的某一条记录
vi. S1查看表table1，能查询到S2添加的记录吗？我感觉应该可以，因为这个是幻读读一种
**答案：**其实是错误的！实际演示结果是还是看不到S2添加的记录；当S1也添加相同的记录时，报记录已存在，再次查看还是看不到S2添加的记录；当S1修改了这条添加的记录，再次查看才能看到这条添加的记录。这就是幻读。
b. 间隙锁是锁前后间隔，那么如果在这之外的记录被修改为条件值，会阻塞吗？
i. 插入记录1,4,7,10
ii. S1锁定4做更新，则1_4和47会上间隙锁，如果这个时候添加一条记录为8，并且修改8为4，那么会阻塞吗？
**答案：**会阻塞，间隙锁是不能添加间隙锁区间里的值，同时不能更新为间隙锁区间里的值。

三、其他

幻读的影响：
a. binlog语意问题（https://blog.youkuaiyun.com/new_buff_007/article/details/104249866）
查看死锁日志：
a. 查看近期死锁日志信息：show engine innodb status\G;
并发性能优化策略：
a. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
b. 合理设计索引，尽量缩小锁的范围
c. 尽可能减少检索条件，避免间隙锁
d. 尽量控制事务大小，减少锁定资源量和时间长度
e. 尽可能低级别事务隔离