MySQL死锁

2024-01-24 20:06 由小松聊PHP进阶发表于 #数据库

简介

在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 的默认行为，它会自动选择牺牲者并回滚事务。
最小成本回滚策略的原理是选择最小成本的事务作为牺牲者。评估算法如下：
回滚事务所涉及的操作数量：回滚操作的数量越小，成本越低。
回滚事务所占用的系统资源：回滚事务占用的系统资源越少，成本越低。
回滚事务已执行的工作量：已执行的工作量越少，成本越低。