背景
前段时间做了很多慢SQL
的优化工作,这周刚好又被反馈服务出现了死锁导致了业务报错。看了一下云数据库的告警日志,发现出现了比较多的事务未提交
、死锁
、等待行锁
的严重警告。都是一些棘手的运维工作,涉及到业务流程的梳理、SQL的优化等工作。
今天趁这个机会,我们一起看下如何去分析这些问题,主要看下等待行锁、死锁。
数据库有哪几种锁?
每次说数据库锁,感觉一大堆。其实如果按照一定的纬度去整理下,还是比较清晰的。如图:
力度划分:表锁、页锁、行锁
算法划分:Record Lock
、Gap Lock
、Next-key Lock
实现机制:乐观锁、悲观锁
兼容性:排它锁、共享锁、意向锁
每次说锁,其实也要跟它的隔离级别挂钩才行,其实都是为了去实现某一个功能才产生的。所以不可以一概而论,总之记住几个大的背景:
- 不同的隔离级别才会有不同的锁,比如
RR
才会出现Gap Lock
,因为要避免幻读的问题,所有要把它相邻的数据也要锁住。 - 锁是作用在索引上的,包含:聚簇索引、非聚簇索引
如何看日志?
1 | SET GLOBAL innodb_status_output=ON; -- 开启输出 |
通过SHOW ENGINE INNODB STATUS
可以看到锁的一些信息
先确定一下自己数据库的隔离级别信息,我现在数据库的版本是8.0.26
。
1 | 如果是比较老的数据库 |
查看锁的情况
1 | # 老版本 |
等待行锁
这个问题也会比较常见,如果出现一个事务获取了锁,如果它不释放或者提交的话,那么后面的人就一直获取不到锁。如果获取锁的时间过长的话,后面的流程就会一直卡住。
1 | -- 建表 |
事务1:
1 | mysql> BEGIN; |
这个时候不要提交事务,看一下现在加锁的情况。
- 5,3 加了一把X锁
- 5,5加了一把X锁
- 3 主键加了一个X锁
- 5主键加了一个X锁
- 7,4加了GAP Lock,X,这里因为是RR,为防止幻读需要加GAP来保证。
事务2:
1 | mysql> update id_test_rr set name = 'x' where id = 5; |
当事务1提交的时候,事务2马上就获取到了锁。
如何设置超时时间:
1 | show variables like 'innodb_lock_wait_timeout'; |
死锁
死锁产生的条件
(1) 互斥条件:一个资源每次只能被一个进程使用。
(2)请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
(3)不剥夺条件:进程已获得的资源,在末使用完之前,不能强行剥夺。
(4)循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
其实,产生死锁的条件无非就是这4个条件,其实大学里学习操作系统的时候就有学习到过。解决死锁,也只需要让它们只要有一个条件不满足就可以了。
死锁的过程分析
建表语句
1 | CREATE TABLE t (i INT) ENGINE = InnoDB; |
事务1:
1 | mysql> BEGIN; |
事务2:
1 | mysql> BEGIN; |
然后事务1继续进行删除操作
1 | mysql> DELETE FROM t WHERE i = 1; |
事务2报错了:Deadlock found when trying to get lock;
死锁产生了!因为事务1需要锁X来删除行,而事务2拿着锁X并正在等待事务1释放锁S。看看2个事务的状态:
- 事务1: 拿着锁S,等待着事务2释放锁X
- 事务2: 拿着锁X,等待着事务1释放锁S
参考地址
- https://lotabout.me/2020/God-Damn-MySQL-Locks/
- https://www.cnblogs.com/xiaoboluo768/p/5171425.html
- https://zhuanlan.zhihu.com/p/296545804
- https://blog.csdn.net/weixin_45437022/article/details/115448563
- https://www.cnblogs.com/my_life/articles/10219594.html
如果大家喜欢我的文章,可以关注个人订阅号。欢迎随时留言、交流。如果想加入微信群的话一起讨论的话,请加管理员微信号:chengcheng222e
,他会拉你们进群。