mysql - 我如何调试这个 mysql 死锁？-6ren

mysql - 我如何调试这个 mysql 死锁？

转载作者：行者123 更新时间：2023-12-03 21:27:29

我的 MySQL InnoDB 表出现死锁。 InnoDB 日志查明导致死锁的两个查询(这是两个完全相同的查询，两个部分完全相同的事务，几乎同时重复请求 API 导致)。但我不明白问题出在哪里 - 查询只是连续更新一些字段，为什么会死锁？

以下是查询示例:

update `some_table` set `some_field` = 123 where `some_table`.`id` = 530;

下面我粘贴了来自 InnoDB show engine innodb status; 的死锁日志。让我感到困惑的是，对于 (2) TRANSACTION 中的 HOLDS THE LOCK 行和 WAITING FOR THIS LOCK TO BE GRANTED 是相同的——就好像它既持有锁又等待它。

我是 InnoDB 中死锁处理的新手，所以可能我遗漏了一些东西。这里有什么问题？

LATEST DETECTED DEADLOCK
------------------------
2017-10-28 11:50:42 0x7f9d586d2700
*** (1) TRANSACTION:
TRANSACTION 14425003, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4662, OS thread handle 140313765725952, query id 6441114 localhost 127.0.0.1 app updating
update `some_table` set `some_field` = 123 where `some_table`.`id` = 530
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 148 page no 15 n bits 96 index PRIMARY of table `some_schema`.`some_table` trx id 14425003 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
 0: len 8; hex 8000000000000212; asc         ;;
 1: len 6; hex 000000dc1b3a; asc      :;;
 2: len 7; hex 350000013227b6; asc 5   2' ;;
 3: len 8; hex 8000000000000006; asc         ;;
 4: SQL NULL;
 5: len 4; hex 80000000; asc     ;;
 6: len 28; hex d09ed0bad181d0bad0b8d0b520d181d0b0d0b4d18b20283530333029; asc                       (5030);;
 7: len 30; hex d09fd0bed181d0b5d0bbd0bed0ba2022d09ed0bad181d0bad0b8d0b520d1; asc                "              ; (total 38 bytes);
 8: len 12; hex d0add092d0a0d098d09ad090; asc             ;;
 9: len 4; hex 312e3732; asc 1.72;;
 10: len 8; hex 8000000000000023; asc        #;;
 11: SQL NULL;
 12: len 30; hex 30820122300d06092a864886f70d01010105000382010f003082010a0282; asc 0  "0   * H             0     ; (total 294 bytes);
 13: SQL NULL;
 14: len 1; hex 9d; asc  ;;
 15: len 30; hex 353030303a333b3530303a333b313030303a33313b35303a353b3130303a; asc 5000:3;500:3;1000:31;50:5;100:; (total 32 bytes);
 16: len 4; hex 800001f4; asc     ;;
 17: len 8; hex 8000015f622b7acf; asc    _b+z ;;
 18: SQL NULL;
 19: SQL NULL;
 20: len 1; hex 80; asc  ;;
 21: len 1; hex 81; asc  ;;
 22: len 1; hex 81; asc  ;;
 23: len 1; hex 81; asc  ;;
 24: len 4; hex 77696669; asc wifi;;
 25: len 7; hex 6d656761666f6e; asc megafon;;
 26: len 8; hex 8000015f5d43771a; asc    _]Cw ;;
 27: len 30; hex 4a1a42c600000230000007d700000772000007b3000007e1000007e20000; asc J B    0       r              ; (total 48 bytes);
 28: len 8; hex 5cbdd55bbd55e93f; asc \  [ U ?;;
 29: len 4; hex 80000000; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 14425004, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4663, OS thread handle 140313770141440, query id 6441120 localhost 127.0.0.1 app updating
update `some_table` set `some_field` = 123 where `some_table`.`id` = 530
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 148 page no 15 n bits 96 index PRIMARY of table `some_schema`.`some_table` trx id 14425004 lock mode S locks rec but not gap
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
 0: len 8; hex 8000000000000212; asc         ;;
 1: len 6; hex 000000dc1b3a; asc      :;;
 2: len 7; hex 350000013227b6; asc 5   2' ;;
 3: len 8; hex 8000000000000006; asc         ;;
 4: SQL NULL;
 5: len 4; hex 80000000; asc     ;;
 6: len 28; hex d09ed0bad181d0bad0b8d0b520d181d0b0d0b4d18b20283530333029; asc                       (5030);;
 7: len 30; hex d09fd0bed181d0b5d0bbd0bed0ba2022d09ed0bad181d0bad0b8d0b520d1; asc                "              ; (total 38 bytes);
 8: len 12; hex d0add092d0a0d098d09ad090; asc             ;;
 9: len 4; hex 312e3732; asc 1.72;;
 10: len 8; hex 8000000000000023; asc        #;;
 11: SQL NULL;
 12: len 30; hex 30820122300d06092a864886f70d01010105000382010f003082010a0282; asc 0  "0   * H             0     ; (total 294 bytes);
 13: SQL NULL;
 14: len 1; hex 9d; asc  ;;
 15: len 30; hex 353030303a333b3530303a333b313030303a33313b35303a353b3130303a; asc 5000:3;500:3;1000:31;50:5;100:; (total 32 bytes);
 16: len 4; hex 800001f4; asc     ;;
 17: len 8; hex 8000015f622b7acf; asc    _b+z ;;
 18: SQL NULL;
 19: SQL NULL;
 20: len 1; hex 80; asc  ;;
 21: len 1; hex 81; asc  ;;
 22: len 1; hex 81; asc  ;;
 23: len 1; hex 81; asc  ;;
 24: len 4; hex 77696669; asc wifi;;
 25: len 7; hex 6d656761666f6e; asc megafon;;
 26: len 8; hex 8000015f5d43771a; asc    _]Cw ;;
 27: len 30; hex 4a1a42c600000230000007d700000772000007b3000007e1000007e20000; asc J B    0       r              ; (total 48 bytes);
 28: len 8; hex 5cbdd55bbd55e93f; asc \  [ U ?;;
 29: len 4; hex 80000000; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 148 page no 15 n bits 96 index PRIMARY of table `some_schema`.`some_table` trx id 14425004 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0
 0: len 8; hex 8000000000000212; asc         ;;
 1: len 6; hex 000000dc1b3a; asc      :;;
 2: len 7; hex 350000013227b6; asc 5   2' ;;
 3: len 8; hex 8000000000000006; asc         ;;
 4: SQL NULL;
 5: len 4; hex 80000000; asc     ;;
 6: len 28; hex d09ed0bad181d0bad0b8d0b520d181d0b0d0b4d18b20283530333029; asc                       (5030);;
 7: len 30; hex d09fd0bed181d0b5d0bbd0bed0ba2022d09ed0bad181d0bad0b8d0b520d1; asc                "              ; (total 38 bytes);
 8: len 12; hex d0add092d0a0d098d09ad090; asc             ;;
 9: len 4; hex 312e3732; asc 1.72;;
 10: len 8; hex 8000000000000023; asc        #;;
 11: SQL NULL;
 12: len 30; hex 30820122300d06092a864886f70d01010105000382010f003082010a0282; asc 0  "0   * H             0     ; (total 294 bytes);
 13: SQL NULL;
 14: len 1; hex 9d; asc  ;;
 15: len 30; hex 353030303a333b3530303a333b313030303a33313b35303a353b3130303a; asc 5000:3;500:3;1000:31;50:5;100:; (total 32 bytes);
 16: len 4; hex 800001f4; asc     ;;
 17: len 8; hex 8000015f622b7acf; asc    _b+z ;;
 18: SQL NULL;
 19: SQL NULL;
 20: len 1; hex 80; asc  ;;
 21: len 1; hex 81; asc  ;;
 22: len 1; hex 81; asc  ;;
 23: len 1; hex 81; asc  ;;
 24: len 4; hex 77696669; asc wifi;;
 25: len 7; hex 6d656761666f6e; asc megafon;;
 26: len 8; hex 8000015f5d43771a; asc    _]Cw ;;
 27: len 30; hex 4a1a42c600000230000007d700000772000007b3000007e1000007e20000; asc J B    0       r              ; (total 48 bytes);
 28: len 8; hex 5cbdd55bbd55e93f; asc \  [ U ?;;
 29: len 4; hex 80000000; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

最佳答案

我猜这个死锁是由主键上的共享锁引起的。
Here 是官方文档的链接，死锁是如何发生的。
然后我会调查您的问题，并进行一些调查以尝试重现此僵局。
首先，让我们分析一下你的死锁日志:

2 个与此死锁相关的事务。

Transaction (1) id = 14425003

Transaction (2) id = 14425004

Transaction (2) 在检测到死锁后最终回滚。

Transaction (2) 保持 锁定模式 S 锁定 rec 但不锁定间隙

Transaction (2) 等待 lock_mode X locks rec but not gap

Transaction (1) 等待 lock_mode X locks rec but not gap

综上所述， Transaction (2) 持有 RECORD LOCK S 想要得到 RECORD LOCK X ，同时 Transaction (1) 想要得到 RECORD LOCK X 。它们相互阻塞，然后发生死锁。
使用 select * where id = 530 lock in share mode 授予 RECORD LOCK S 非常简单，但是，我认为您可能不会在您的应用程序中使用此查询。
然后我们需要知道 RECORD LOCK S 什么时候会被添加到主索引中。以下来自 msyql offical document

If a duplicate-key error occurs, a shared lock on the duplicate indexrecord is set. This use of a shared lock can result in deadlock shouldthere be multiple sessions trying to insert the same row if anothersession already has an exclusive lock. This can occur if anothersession deletes the row. Suppose that an InnoDB table t1 has thefollowing structure:

话虽如此，必须有一些查询做插入或更新，并发生重复键错误。

以下是如何重现这种死锁:

1. Create table:

CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  unique key `p_name` (`name`)
) ENGINE=InnoDB CHARSET=utf8;

2. Prepare data:

insert into t (name) value ('A'), ('C'), ('D');

3. Reproduce Steps

+-------------------------------------------+--------------------------------------+
|                 Session A                 |              Session B               |
+-------------------------------------------+--------------------------------------+
| begin;                                    |                                      |
|                                           | begin;                               |
| insert into t (id, name) values (4, 'E'); |                                      |
| update t set name = 'C' where id = 4;     |                                      |
|                                           | update t set name = 'C' where id = 2;|
|                                           | BLOCKED                              |
| update t set name = 'C' where id = 2;     |                                      |
| DEADLOCK OCCUR                            |                                      |
+-------------------------------------------+--------------------------------------+

4. The final deadlock log is as following:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-07-23 07:19:39 0x7f8cc819e700
*** (1) TRANSACTION:
TRANSACTION 2774, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 12, OS thread handle 140242628749056, query id 9719 192.168.48.1 root updating
/* ApplicationName=PyCharm 2019.1.1 */ update t set name = 'C' where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 2774 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000000ad0; asc       ;;
 2: len 7; hex a90000011d0120; asc        ;;
 3: len 1; hex 43; asc C;;

*** (2) TRANSACTION:
TRANSACTION 2773, ACTIVE 424 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140242629289728, query id 9729 192.168.48.1 root updating
/* ApplicationName=PyCharm 2019.1.1 */ update t set name = 'C' where id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 64 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 2773 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000000ad0; asc       ;;
 2: len 7; hex a90000011d0120; asc        ;;
 3: len 1; hex 43; asc C;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 2773 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000000ad0; asc       ;;
 2: len 7; hex a90000011d0120; asc        ;;
 3: len 1; hex 43; asc C;;

*** WE ROLL BACK TRANSACTION (1)

5. How to prevent

避免或减少重复值插入或更新

不要使用唯一索引并让应用程序确保值是唯一的

减少并发查询。

关于mysql - 我如何调试这个 mysql 死锁？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46987993/

文章推荐： jquery - jQuery $.Deferred (jQuery 1.x/2.x) 固有的问题

文章推荐： titan - Gremlin 查询以获取边等于 2 的顶点

qt - 死 QML 元素接收信号？
我有类似下面的代码: ... id: myComponent signal updateState() property variant modelList: [] Repeater { mo
c - 死 C 变量
我正在处理一些我无法展示的私有(private)代码，但我已经制作了一些示例代码来描述我的问题: 主.c: #include #include #include #include typede
php - 导出();死();返回假；
这个问题在这里已经有了答案: 关闭10 年前。 Possible Duplicate: what are the differences in die() and exit() in PHP? 我想
perl - Perl 模块是否应该引发异常(死/呱呱)？
在编写 Perl 模块时，在模块内部使用 croak/die 是一个好习惯吗？毕竟，如果调用者不使用 eval block ，模块可能会使调用它的程序崩溃。在这些情况下，最佳做法是什么？最佳答案
java - 死 Java 线程和 native 操作系统线程
我有一些搜索线程正在存储结果。我知道当线程启动时，JVM native 代码会代理在操作系统上创建新 native 线程的请求。这需要 JVM 之外的一些内存。当线程终止并且我保留对它的引用并将其用作
perl - 为什么调用 foo() 时，wantarray 会在标量上下文中返回 ||死？
我刚刚花了很多时间调试一个我追溯到 wantarray() 的问题。 .我已将其提炼为这个测试用例。 (忽略 $! 在这种情况下不会有任何有用信息的事实)。我想知道为什么wantarray在第二个示例
php - echo ('exit' ); 之间有什么区别？死;并死于 ('exit')；？
我看到一些代码是这样做的: if(something){ echo 'exit from program'; die; } ...more code 和其他只使用 die 的人: if
php - 如果任何 $_POST 等于另一个 $_POST 死 ('error' );?
我正在尝试将此表格用于: 如果任何 $_POST 变量等于任何其他 $_POST 变量抛出错误。如果只有几个，那不是问题，但我有大约 20 个左右所以如果我想这样做，我将不得不像这样但这
Hadoop:可用数据节点:0(共 0 个，死 0 个)
每次我运行: hadoop dfsadmin -report 我得到以下输出: Configured Capacity: 0 (0 KB) Present Capacity: 0 (0 KB) DFS

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 我如何调试这个 mysql 死锁？