前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >数据库遇到了死锁,你该如何排查?

数据库遇到了死锁,你该如何排查?

作者头像
chengcheng222e
发布2021-12-09 13:43:54
9620
发布2021-12-09 13:43:54
举报
文章被收录于专栏:简栈文化
背景

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

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

数据库有哪几种锁?

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

MySQL锁

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

算法划分:Record LockGap LockNext-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马上就获取到了锁。

如何设置超时时间:

代码语言:javascript
复制
show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
|Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout|  50  |
+--------------------------+-------+
死锁
死锁产生的条件

(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://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
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-12-04,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 简栈文化 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 背景
  • 数据库有哪几种锁?
  • 如何看日志?
  • 等待行锁
  • 死锁
    • 死锁产生的条件
      • 死锁的过程分析
      • 参考地址
      相关产品与服务
      云数据库 SQL Server
      腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档