编程开源技术交流，分享技术与知识

网站首页 > 开源技术 正文

这个不可思议的死锁你会解吗?

wxchong 2024-12-05 17:05:56 开源技术 39 ℃ 0 评论

| 作者 王起帆，腾讯CSIG数据库产品中心后台开发工程师，目前主要参与DBbrain开发工作，热爱技术，欢迎留言进行交流。


我们都知道，数据库系统中，不同线程并发访问数据，为了保护数据，在执行SQL语句时候需要对数据加锁。而死锁是一个经常遇到问题，SQL语句加锁和事物隔离级别，访问的索引是不是唯一，访问数据是否存在都有关系，往往死锁分析非常复杂。这篇文章将介绍一个“简单的死锁”，这个死锁产生的事物中SQL语句都只有一条，而且业务非常简单就是删除一条记录。两个事物同时执行以下两个SQL语句就有可能死锁。

DELETE FROM dept_manager WHERE num = 0;DELETE FROM dept_manager WHERE dept_no = 'd001';


一、死锁模拟




死锁模拟

首先介绍下表结构，这个表除了主键索引 PRIMARY，还有一个唯一索引 num 和一个非唯一索引 dept_no ，建表语句如下：


CREATE TABLE `dept_manager` ( `emp_no` int() NOT , `dept_no` char() NOT , `num` int() NOT , `to_date` date NOT , PRIMARY KEY (`emp_no`), unique index(`num`), KEY `dept_no` (`dept_no`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;


然后再准备下数据：


INSERT INTO `dept_manager` VALUES (1001,'d001',0,'1991-10-01'), (1002,'d005',,'9999-01-01'), (1005,'d002',,'1989-12-17'), (1007,'d002',,'9999-01-01'), (1008,'d004',,'1988-09-09'), (1009,'d004',,'1992-08-02'), (1010,'d005',9,'1996-08-30');


使用执行两个sql很难，使用 mysqlslap 来高并发碰碰运气：


# mysqlslap --create-schema dldb -q "begin;DELETE FROM dept_manager WHERE num = 0; rollback;" --number-of-queries=100000 -uroot -p123456 &# mysqlslap --create-schema dldb -q "begin;DELETE FROM dept_manager WHERE dept_no = 'd001'; rollback;" --number-of-queries=100000 -uroot -p123456 &


这两个事物非常都是删除一行相同的数据 (1001,'d001',0,'1991-10-01'）只不过一个根据索引 num ，一个根据索引 dept_no 。



二、原因分析



1. 数据是怎么找到的？


要说清楚死锁产生原因，就要先理清楚这条SQL是怎么执行的，会在那些地方加锁。在此之前先说说数据库是怎么找到我们要删除的这行数据的。下面两幅图展示根据年龄为30来查记录的示意图。首先根据 name 为 seven, 在 name 这个辅助索引查找，但是只能拿到主键的 id。随后再根据主键id 去主键查找，这个过程称为回表。访问数据是要通过索引的，而且数据就在主键索引上面，所以加锁就是加在索引上面的。



2. Delete 是怎么执行的


Delete 删除数据其实并不是把数据删除了，只是把数据标记一下，表示这里可以复用的，如果下次这里有数据要插入就可以直接复用原来空间里。所以Delete 和 Update 操作比较类似。Delete 和 Update 是根据条件找到第一条数据，进行修改，然后找到第二条数据，以此类推直到再也查不到符合条件的数据。


3. 加锁分析


我们以 DELETE FROM dept_manager WHERE num = 0; 为例，只有一个条件 num = 0, 因该是根据 num = 0 在 num 索引中找到对应的主键id, 随后根据主键 id,找到对应记录，标记成可复用状态。除了删除数据行记录，对应的索引也需要维护下，其他索引对应位置也需要标记成删除状态。这个表中主键索引 PRIMARY，唯一索引 num，非唯一索引 dept_no 的对应位置都会加上锁。同理第二个SQL语句执行时候，加锁位置也是一样的。（可重复度隔离级别上，非唯一索引还要加上间隙锁）。


既然加锁上一样的，那应该是在不同索引加锁顺序是不一样的。推测下对于 WHERE num = 0 应该先在 num 上加锁，随后在主键加锁，最后在 dept_no上，num ->PRIMARY-> dept_no。WHERE dept_no = 'd001';加锁顺序应该是dept_no -> PRIMARY -> num。尽管这条SQL数据很简单，但是由于数据中索引比较多，加锁顺序也不一样，导致了死锁。




三、场景验证



可以用 show engine innodb status ，来查看最近一次死锁日志。事物1等待索引dept上的锁 0: len 4; hex 64303031; asc d001;; 这里“64303031” 16进制转为字符为“d001” 与 WHERE dept_no = 'd001' 相对应。事物2持有这个锁的，事物1持有的锁没有显示，应该是主键上的锁，这是符合预期的。


------------------------LATEST DETECTED DEADLOCK------------------------2021-- :: 0x70000a6b1000*** () TRANSACTION:TRANSACTION 1681994, ACTIVE 0 sec updating or deletingmysql tables in use , locked LOCK WAIT  lock struct(s), heap size 1136,  row lock(s), undo log entries MySQL thread id , OS thread handle 123145456488448, query id 343687 localhost 127.0.0. root updatingDELETE FROM dept_manager WHERE num = 0*** () WAITING FOR THIS LOCK TO BE GRANTED: #请求 dept_no上锁RECORD LOCKS space id 367 page no 5 n bits  index dept_no of table `employees`.`dept_manager` trx id 1681994 lock_mode X locks rec but not gap waitingRecord lock, heap no  PHYSICAL RECORD: n_fields ; compact format; info bits 0 0: len ; hex 64303031; asc d001;; : len ; hex 800003e9; asc ;;
*** () TRANSACTION:TRANSACTION 1681554, ACTIVE 0 sec starting index readmysql tables in use , locked  lock struct(s), heap size 1136,  row lock(s)MySQL thread id 106, OS thread handle 123145477099520, query id 341105 localhost 127.0.0. root updatingDELETE FROM dept_manager WHERE dept_no = 'd001'*** () HOLDS THE LOCK(S): # 持有 dept_no 上锁RECORD LOCKS space id 367 page no 5 n bits  index dept_no of table `employees`.`dept_manager` trx id 1681554 lock_mode XRecord lock, heap no  PHYSICAL RECORD: n_fields ; compact format; info bits 0 0: len ; hex 64303031; asc d001;; : len ; hex 800003e9; asc ;;
*** () WAITING FOR THIS LOCK TO BE GRANTED: # 请求主键RECORD LOCKS space id 367 page no  n bits  index PRIMARY of table `employees`.`dept_manager` trx id 1681554 lock_mode X locks rec but not gap waitingRecord lock, heap no  PHYSICAL RECORD: n_fields 6; compact format; info bits  0: len ; hex 800003e9; asc ;; : len 6; hex 00000019aa4a; asc J;; : len ; hex c000001b80ede; asc , ;; : len ; hex 64303031; asc d001;; : len ; hex 80000000; asc ;; 5: len ; hex f8f41; asc A;;
*** WE ROLL BACK TRANSACTION ()


四、总结



本文介绍的样例中，尽管SQL语句很简单，但由于表中有多个索引，对索引的访问顺序不同，造成死锁风险。为了避免数据库中发生死锁，建议：


1. 尽量开启死锁检测；


2. 尽量使用小事务，在业务允许范围内，将隔离级别改成读已提交，可以减少不些不必要的锁；


3. 避免全表扫描；


4. 避免较多索引；


5. 不同事务对表和行操作的顺序尽量一致。


Tags：

猜你喜欢

本文暂时没有评论，来添加一个吧(●'◡'●)

欢迎 你 发表评论:

最近发表
标签列表
赣ICP备2023011894号-2