MySQL死锁
简介
在MySQL数据库中，死锁是指多个事务同时竞争同一资源，并且彼此互相等待对方释放资源而无法继续执行的情况，导致数据库操作无法完成，从而以最小的成本自动回滚事务的行为。
排查
方法1
show engine innodb status;
执行以上命令会得到大量日志，在LATEST DETECTED DEADLOCK与TRANSACTIONS之间寻找sql 语句，以此定位死锁源头。示例如下：
......
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-23 23:48:30 0x1f00
*** (1) TRANSACTION:
TRANSACTION 805714, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 8076, query id 762 localhost ::1 root updating
update test set name = 'C++语言' where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 6; hex 0000000c4b53; asc KS;;
2: len 7; hex 34000002a80923; asc 4 #;;
3: len 3; hex 432b2b; asc C++;;
*** (2) TRANSACTION:
TRANSACTION 805715, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 7936, query id 766 localhost ::1 root updating
delete from test where id = 1
*** (2) HOLDS THE LOCK(S):
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
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 6; hex 0000000c4b53; asc KS;;
2: len 7; hex 34000002a80923; asc 4 #;;
3: len 3; hex 432b2b; asc C++;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 0000000c4b46; asc KF;;
2: len 7; hex ad000002b10110; asc ;;
3: len 1; hex 43; asc C;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 805727
Purge done for trx's n:o < 805727 undo n:o < 0 state: running but idle
History list length 36
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283132143509864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143508992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143508120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143507248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
......
方法2
show status like 'innodb_row_lock%'
执行以上命令后会得到一个表格，
Innodb_row_lock_current_waits 0 //如果这个数字非零，表示有事务正在等待其他事务持有的行级锁。
Innodb_row_lock_time 18756 //以毫秒为单位。它表示所有事务在竞争锁资源时，所消耗的总时间
Innodb_row_lock_time_avg 3126 //平均每个InnoDB行级锁的持有时间，毫秒为单位。用总的锁持有时间除以锁请求次数来计算。
Innodb_row_lock_time_max 7921 //单个InnoDB行级锁的最大持有时间，毫秒为单位。这个值表示所有事务竞争锁资源中的最长时间。
Innodb_row_lock_waits 6 //这个指标表示有多少次事务在竞争锁资源时需要等待。
方法3
该查询是用来获取当前正在运行的事务（INNODB_TRX表）、空闲状态的线程（PROCESSLIST表，COMMAND为Sleep）、线程对应的进程信息（threads表）、线程对应的当前执行中的SQL语句（events_statements_current表）的一些相关信息。
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
FROM information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep'
LEFT JOIN performance_schema.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
解决
MySQL会自动解决死锁问题，代价就是MySQL自行找到成本最低的事务，自动回滚。所以说解决方案不是解锁，而是避免。
避免
降低事务粒度：轻量级的事务，锁定更少的资源，不容易发生死锁。
尽快提交事务：锁能更快的释放。
合理的索引设计: 合理设计数据库表的索引可以减少锁竞争，提高查询效率。
一致的访问顺序: 当应用程序涉及多个表时，保持一致的访问顺序可以避免死锁。例如，如果事务A先锁定表X，再锁定表Y，那么事务B也应该按照相同的顺序锁定表X和表Y，从而避免死锁。
举例
前置准备
创建一张表，并补充两条数据
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `temp`.`test` (`id`, `name`) VALUES (1, 'C');
INSERT INTO `temp`.`test` (`id`, `name`) VALUES (2, 'C++');
死锁
开两个终端用于模拟线上环境，逐行执行以下内容：
| 步骤 | 终端A | 终端B |
|---|---|---|
| 1 | start transaction; | start transaction; |
| 2 | select * from test where id = 1 for update | |
| 3 | / | delete from test where id = 2 |
| 4 | update test set name = 'C++语言' where id = 2; | / |
| 5 | 终端显示Deadlock found when trying to get lock; try restarting transaction | delete from test where id = 1 |
| 6 | commit | commit |
for update 语句是作为一种锁机制使用的，它可以防止其他事务在当前事务中正在读取的行上进行修改，常用于实现悲观锁。
看表格分析：
1-3步：流畅执行。
第4步：终端A开始阻塞了，需要等终端B的事务提交，才能执行。
第5步：终端B想执行，就得等终端A的for update锁释放，此时是互相等待的局面，于是死锁产生了，因为终端A还在阻塞，强大的MySQL检测到了死锁，于是在终端A，提示了错误。注意此时终端A的事务，按照最小成本回滚策略，已经被自动回滚。
第6步：终端A再commit无济于事，终端B提交事务，则正常执行。
什么是最小成本回滚策略？
在 MySQL 中，当发生死锁时，MySQL 使用一种叫做"最小成本回滚"（InnoDB 中称为"最小编号事务回滚"）的策略来选择一个事务作为牺牲者并进行回滚，最小成本回滚策略是 MySQL 的默认行为，它会自动选择牺牲者并回滚事务。
最小成本回滚策略的原理是选择最小成本的事务作为牺牲者。评估算法如下：
回滚事务所涉及的操作数量：回滚操作的数量越小，成本越低。
回滚事务所占用的系统资源：回滚事务占用的系统资源越少，成本越低。
回滚事务已执行的工作量：已执行的工作量越少，成本越低。
感谢分享 有用
发表评论