MySQL死锁

培妮到海角

简介

在MySQL数据库中，死锁是指多个事务同时竞争同一资源，并且彼此互相等待对方释放资源而无法继续执行的情况，导致数据库操作无法完成，从而以最小的成本自动回滚事务的行为。
排查

方法1

1. show engine innodb status;

复制代码

执行以上命令会得到大量日志，在LATEST DETECTED DEADLOCK与TRANSACTIONS之间寻找sql 语句，以此定位死锁源头。示例如下：

1. ......
2. ------------------------
3. LATEST DETECTED DEADLOCK
4. ------------------------
5. 2024-01-23 23:48:30 0x1f00
6. *** (1) TRANSACTION:
7. TRANSACTION 805714, ACTIVE 12 sec starting index read
8. mysql tables in use 1, locked 1
9. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
10. MySQL thread id 3, OS thread handle 8076, query id 762 localhost ::1 root updating
11. update test set name = 'C++语言' where id = 2
12. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
13. RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805714 lock_mode X locks rec but not gap waiting
14. Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
15. 0: len 4; hex 00000002; asc     ;;
16. 1: len 6; hex 0000000c4b53; asc     KS;;
17. 2: len 7; hex 34000002a80923; asc 4     #;;
18. 3: len 3; hex 432b2b; asc C++;;
20. *** (2) TRANSACTION:
21. TRANSACTION 805715, ACTIVE 9 sec starting index read
22. mysql tables in use 1, locked 1
23. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
24. MySQL thread id 4, OS thread handle 7936, query id 766 localhost ::1 root updating
25. delete from test where id = 1
26. *** (2) HOLDS THE LOCK(S):
27. RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap
28. Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
29. 0: len 4; hex 00000002; asc     ;;
30. 1: len 6; hex 0000000c4b53; asc     KS;;
31. 2: len 7; hex 34000002a80923; asc 4     #;;
32. 3: len 3; hex 432b2b; asc C++;;
34. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
35. RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap waiting
36. Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
37. 0: len 4; hex 00000001; asc     ;;
38. 1: len 6; hex 0000000c4b46; asc     KF;;
39. 2: len 7; hex ad000002b10110; asc        ;;
40. 3: len 1; hex 43; asc C;;
42. *** WE ROLL BACK TRANSACTION (1)
43. ------------
44. TRANSACTIONS
45. ------------
46. Trx id counter 805727
47. Purge done for trx's n:o < 805727 undo n:o < 0 state: running but idle
48. History list length 36
49. LIST OF TRANSACTIONS FOR EACH SESSION:
50. ---TRANSACTION 283132143509864, not started
51. 0 lock struct(s), heap size 1136, 0 row lock(s)
52. ---TRANSACTION 283132143508992, not started
53. 0 lock struct(s), heap size 1136, 0 row lock(s)
54. ---TRANSACTION 283132143508120, not started
55. 0 lock struct(s), heap size 1136, 0 row lock(s)
56. ---TRANSACTION 283132143507248, not started
57. 0 lock struct(s), heap size 1136, 0 row lock(s)
58. ......

复制代码

方法2

1. show status like 'innodb_row_lock%'

复制代码

执行以上命令后会得到一个表格，

1. Innodb_row_lock_current_waits        0     //如果这个数字非零，表示有事务正在等待其他事务持有的行级锁。
2. Innodb_row_lock_time               18756 //以毫秒为单位。它表示所有事务在竞争锁资源时，所消耗的总时间
3. Innodb_row_lock_time_avg           3126  //平均每个InnoDB行级锁的持有时间，毫秒为单位。用总的锁持有时间除以锁请求次数来计算。
4. Innodb_row_lock_time_max           7921  //单个InnoDB行级锁的最大持有时间，毫秒为单位。这个值表示所有事务竞争锁资源中的最长时间。
5. Innodb_row_lock_waits                 6     //这个指标表示有多少次事务在竞争锁资源时需要等待。

复制代码

方法3

该查询是用来获取当前正在运行的事务（INNODB_TRX表）、空闲状态的线程（PROCESSLIST表，COMMAND为Sleep）、线程对应的进程信息（threads表）、线程对应的当前执行中的SQL语句（events_statements_current表）的一些相关信息。

1. SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT
2. FROM information_schema.INNODB_TRX a
3. LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep'
4. LEFT JOIN performance_schema.threads c ON b.id = c.PROCESSLIST_ID
5. LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

复制代码

解决

MySQL会自动解决死锁问题，代价就是MySQL自行找到成本最低的事务，自动回滚。所以说解决方案不是解锁，而是避免。
避免

• 降低事务粒度：轻量级的事务，锁定更少的资源，不容易发生死锁。
  
• 尽快提交事务：锁能更快的释放。
  
• 合理的索引设计: 合理设计数据库表的索引可以减少锁竞争，提高查询效率。
  
• 一致的访问顺序: 当应用程序涉及多个表时，保持一致的访问顺序可以避免死锁。例如，如果事务A先锁定表X，再锁定表Y，那么事务B也应该按照相同的顺序锁定表X和表Y，从而避免死锁。
  

举例

前置准备

创建一张表，并补充两条数据

1. CREATE TABLE `test` (
2.   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
3.   `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
4.   PRIMARY KEY (`id`)
5. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
7. INSERT INTO `temp`.`test` (`id`, `name`) VALUES (1, 'C');
8. INSERT INTO `temp`.`test` (`id`, `name`) VALUES (2, 'C++');

复制代码

死锁

开两个终端用于模拟线上环境，逐行执行以下内容：
步骤终端A终端B1start transaction;start transaction;2select * from test where id = 1 for update3/delete from test where id = 24update test set name = 'C++语言' where id = 2;/5终端显示Deadlock found when trying to get lock; try restarting transactiondelete from test where id = 16commitcommitfor update 语句是作为一种锁机制使用的，它可以防止其他事务在当前事务中正在读取的行上进行修改，常用于实现悲观锁。
看表格分析：
1-3步：流畅执行。
第4步：终端A开始阻塞了，需要等终端B的事务提交，才能执行。
第5步：终端B想执行，就得等终端A的for update锁释放，此时是互相等待的局面，于是死锁产生了，因为终端A还在阻塞，强大的MySQL检测到了死锁，于是在终端A，提示了错误。注意此时终端A的事务，按照最小成本回滚策略，已经被自动回滚。
第6步：终端A再commit无济于事，终端B提交事务，则正常执行。
什么是最小成本回滚策略？

在 MySQL 中，当发生死锁时，MySQL 使用一种叫做"最小成本回滚"（InnoDB 中称为"最小编号事务回滚"）的策略来选择一个事务作为牺牲者并进行回滚，最小成本回滚策略是 MySQL 的默认行为，它会自动选择牺牲者并回滚事务。
最小成本回滚策略的原理是选择最小成本的事务作为牺牲者。评估算法如下：
回滚事务所涉及的操作数量：回滚操作的数量越小，成本越低。
回滚事务所占用的系统资源：回滚事务占用的系统资源越少，成本越低。
回滚事务已执行的工作量：已执行的工作量越少，成本越低。

来源:https://www.cnblogs.com/phpphp/p/17985765
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！