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自行找到成本最低的事务,自动回滚。所以说解决方案不是解锁,而是避免。

避免

  1. 降低事务粒度:轻量级的事务,锁定更少的资源,不容易发生死锁。
  2. 尽快提交事务:锁能更快的释放。
  3. 合理的索引设计: 合理设计数据库表的索引可以减少锁竞争,提高查询效率。
  4. 一致的访问顺序: 当应用程序涉及多个表时,保持一致的访问顺序可以避免死锁。例如,如果事务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
1start transaction;start transaction;
2select * from test where id = 1 for update
3/delete from test where id = 2
4update test set name = 'C++语言' where id = 2;/
5终端显示Deadlock found when trying to get lock; try restarting transactiondelete from test where id = 1
6commitcommit

for update 语句是作为一种锁机制使用的,它可以防止其他事务在当前事务中正在读取的行上进行修改,常用于实现悲观锁。
看表格分析:
1-3步:流畅执行。
第4步:终端A开始阻塞了,需要等终端B的事务提交,才能执行。
第5步:终端B想执行,就得等终端A的for update锁释放,此时是互相等待的局面,于是死锁产生了,因为终端A还在阻塞,强大的MySQL检测到了死锁,于是在终端A,提示了错误。注意此时终端A的事务,按照最小成本回滚策略,已经被自动回滚。
第6步:终端A再commit无济于事,终端B提交事务,则正常执行。

什么是最小成本回滚策略?

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

作者:小松聊PHP进阶原文地址:https://www.cnblogs.com/phpphp/p/17985765

%s 个评论

要回复文章请先登录注册