数据库遇到了死锁，你该如何排查？

您所在的位置：网站首页 › 数据库死锁问题排查 › 数据库遇到了死锁，你该如何排查？

数据库遇到了死锁，你该如何排查？

2024-07-12 02:42| 来源: 网络整理| 查看: 265

背景

前段时间做了很多慢SQL的优化工作，这周刚好又被反馈服务出现了死锁导致了业务报错。看了一下云数据库的告警日志，发现出现了比较多的事务未提交、死锁、等待行锁的严重警告。都是一些棘手的运维工作，涉及到业务流程的梳理、SQL的优化等工作。

今天趁这个机会，我们一起看下如何去分析这些问题，主要看下等待行锁、死锁。

数据库有哪几种锁？

每次说数据库锁，感觉一大堆。其实如果按照一定的纬度去整理下，还是比较清晰的。如图:

MySQL锁

力度划分：表锁、页锁、行锁

算法划分：Record Lock、Gap Lock、Next-key Lock

实现机制：乐观锁、悲观锁

兼容性：排它锁、共享锁、意向锁

每次说锁，其实也要跟它的隔离级别挂钩才行，其实都是为了去实现某一个功能才产生的。所以不可以一概而论，总之记住几个大的背景：

不同的隔离级别才会有不同的锁，比如RR才会出现Gap Lock，因为要避免幻读的问题，所有要把它相邻的数据也要锁住。锁是作用在索引上的，包含：聚簇索引、非聚簇索引如何看日志？代码语言：javascript复制SET GLOBAL innodb_status_output=ON; -- 开启输出 SET GLOBAL innodb_status_output_locks=ON; -- 开启锁信息输出 SHOW ENGINE INNODB STATUS\G

通过SHOW ENGINE INNODB STATUS可以看到锁的一些信息

先确定一下自己数据库的隔离级别信息，我现在数据库的版本是8.0.26 。

代码语言：javascript复制如果是比较老的数据库 select @@tx_isolation; select @@global.tx_isolation; ERROR 1193 (HY000): Unknown system variable 'tx_isolation' 如果报错，说明你的数据库比较新，需要采用新的查询语句。 mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.26 | +-----------+ 1 row in set (0.01 sec) mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ select @@session.transaction_isolation;

查看锁的情况

代码语言：javascript复制# 老版本 select * from information_schema.innodb_locks; # 高版本 SELECT ENGINE_LOCK_ID, THREAD_ID, OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks; 等待行锁

这个问题也会比较常见，如果出现一个事务获取了锁，如果它不释放或者提交的话，那么后面的人就一直获取不到锁。如果获取锁的时间过长的话，后面的流程就会一直卡住。

代码语言：javascript复制-- 建表 CREATE TABLE `id_test_rr` ( `pk` int NOT NULL, `id` int DEFAULT NULL, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `idx_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- 准备数据 INSERT INTO id_test_rr values(1, 1, 'a'); INSERT INTO id_test_rr values(2, 3, 'b'); INSERT INTO id_test_rr values(3, 5, 'c'); INSERT INTO id_test_rr values(4, 7, 'c'); INSERT INTO id_test_rr values(5, 5, 'b');

事务1：

代码语言：javascript复制mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM id_test_rr WHERE id = 5; Query OK, 2 rows affected (0.01 sec)

这个时候不要提交事务，看一下现在加锁的情况。

5，3 加了一把X锁5，5加了一把X锁3 主键加了一个X锁5主键加了一个X锁7，4加了GAP Lock，X，这里因为是RR，为防止幻读需要加GAP来保证。

事务2：

代码语言：javascript复制mysql> update id_test_rr set name = 'x' where id = 5; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

当事务1提交的时候，事务2马上就获取到了锁。

如何设置超时时间：

（1）互斥条件：一个资源每次只能被一个进程使用。（2）请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。（3）不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。（4）循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

其实，产生死锁的条件无非就是这4个条件，其实大学里学习操作系统的时候就有学习到过。解决死锁，也只需要让它们只要有一个条件不满足就可以了。

死锁的过程分析

建表语句

代码语言：javascript复制CREATE TABLE t (i INT) ENGINE = InnoDB; INSERT INTO t (i) VALUES(1);

事务1：

代码语言：javascript复制mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; +------+ | i | +------+ | 1 | +------+ 1 row in set (0.00 sec)

image-20211204215103399

事务2：

代码语言：javascript复制mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) DELETE FROM t WHERE i = 1;

image-20211204215206747

然后事务1继续进行删除操作

代码语言：javascript复制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://lotabou/2020/God-Damn-MySQL-Locks/https://www.cnblogs.com/xiaoboluo768/p/5171425.htmlhttps://zhuanlan.zhihu.com/p/296545804https://blog.csdn.net/weixin_45437022/article/details/115448563https://www.cnblogs.com/my_life/articles/10219594.html

【本文地址】

数据库遇到了死锁，你该如何排查？

数据库遇到了死锁，你该如何排查？

今日新闻

推荐新闻