MySQL n00b 的 MySQL 死锁之谜-6ren

MySQL n00b 的 MySQL 死锁之谜

转载作者：行者123 更新时间：2023-11-29 13:10:58

在此 MySQL 死锁中，有 2 个事务都在等待表 Foo 的PRIMARY。MemberRelation”> 被授予。但事务 2 已经持有锁。怎么又等了？

此外，我禁用了 bin 日志记录，因为尚未启用复制。而且我不关心 select 语句返回不同步的数据。

------------------------
LATEST DETECTED DEADLOCK
------------------------
140301  2:51:23

*** (1) TRANSACTION:

TRANSACTION 25B9FA3A, ACTIVE 3 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 1502 lock struct(s), heap size 195000, 3086 row lock(s)
MySQL thread id 3673, OS thread handle 0x7f639b820700, query id 2528037433 c-98-210-185-119.hsd1.ca.comcast.net 98.210.185.119 FooReadWrite Updating

UPDATE MemberRelation SET MR_Common_Interests=3,MR_ARG=2 WHERE MR_MID_1=1429 AND MR_MID_2=3370

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 313802 n bits 448 index `PRIMARY` of table `Foo`.`MemberRelation` trx id 25B9FA3A lock_mode X locks rec but not gap waiting

Record lock, heap no 137 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86de; asc         ;;
 1: len 6; hex 00002443dba5; asc   $C  ;;
 2: len 7; hex ab000200360110; asc     6  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005e9; asc    ;;
 5: len 2; hex 8009; asc   ;;
 6: len 1; hex 83; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;


*** (2) TRANSACTION:

TRANSACTION 25B9F723, ACTIVE 20 sec fetching rows

mysql tables in use 6, locked 5

3322 lock struct(s), heap size 457144, 174995 row lock(s)

MySQL thread id 1691, OS thread handle 0x7f63b0dc4700, query id 2528049898 54.245.103.196 FooReadOnly Sending data

SELECT MR_Common_Interests INTO nSF
FROM MemberRelation
WHERE (MR_MID_1 =  NAME_CONST('nMem1',9942) AND MR_MID_2 =  NAME_CONST('nMem2',1700)) OR (MR_MID_2 =  NAME_CONST('nMem1',9942) AND MR_MID_1 =  NAME_CONST('nMem2',1700))
LIMIT 0, 1

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 0 page no 313802 n bits 448 index `PRIMARY` of table `Foo`.`MemberRelation` trx id 25B9F723 lock mode S locks rec but not gap

Record lock, heap no 137 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86de; asc         ;;
 1: len 6; hex 00002443dba5; asc   $C  ;;
 2: len 7; hex ab000200360110; asc     6  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005e9; asc    ;;
 5: len 2; hex 8009; asc   ;;
 6: len 1; hex 83; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 142 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86e3; asc         ;;
 1: len 6; hex 00002443dbaf; asc   $C  ;;
 2: len 7; hex b50000720c0110; asc    r   ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005f5; asc    ;;
 5: len 2; hex 8004; asc   ;;
 6: len 1; hex 86; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 143 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86e4; asc         ;;
 1: len 6; hex 00002443dbb1; asc   $C  ;;
 2: len 7; hex b7000200340110; asc     4  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005f6; asc    ;;
 5: len 2; hex 8009; asc   ;;
 6: len 1; hex 81; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 144 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86e5; asc         ;;
 1: len 6; hex 00002443dbb3; asc   $C  ;;
 2: len 7; hex b90003c0210110; asc     !  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005f7; asc    ;;
 5: len 2; hex 8007; asc   ;;
 6: len 1; hex 80; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 145 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86e6; asc         ;;
 1: len 6; hex 00002443dbb5; asc   $C  ;;
 2: len 7; hex bb0002800e0110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005f8; asc    ;;
 5: len 2; hex 8002; asc   ;;
 6: len 1; hex 80; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 146 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86e7; asc         ;;
 1: len 6; hex 00002443dbb7; asc   $C  ;;
 2: len 7; hex bd00003a400110; asc    :@  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005f9; asc    ;;
 5: len 2; hex 8004; asc   ;;
 6: len 1; hex 83; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 147 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86e8; asc         ;;
 1: len 6; hex 00002443dbb9; asc   $C  ;;
 2: len 7; hex bf0002002a0110; asc     *  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005fa; asc    ;;
 5: len 2; hex 8002; asc   ;;
 6: len 1; hex 82; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 148 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86e9; asc         ;;
 1: len 6; hex 00002443dbbb; asc   $C  ;;
 2: len 7; hex c10000c0070110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005fb; asc    ;;
 5: len 2; hex 8003; asc   ;;
 6: len 1; hex 81; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 149 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86ea; asc         ;;
 1: len 6; hex 00002443dbbd; asc   $C  ;;
 2: len 7; hex c30003c0110110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005fc; asc    ;;
 5: len 2; hex 8002; asc   ;;
 6: len 1; hex 80; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 150 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86eb; asc         ;;
 1: len 6; hex 00002443dbbf; asc   $C  ;;
 2: len 7; hex c5000200030110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005fd; asc    ;;
 5: len 2; hex 8001; asc   ;;
 6: len 1; hex 83; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 151 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86ec; asc         ;;
 1: len 6; hex 00002443dbc1; asc   $C  ;;
 2: len 7; hex c7000072010110; asc    r   ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005fe; asc    ;;
 5: len 2; hex 8004; asc   ;;
 6: len 1; hex 82; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 152 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86ed; asc         ;;
 1: len 6; hex 00002443dbc3; asc   $C  ;;
 2: len 7; hex c9000180030110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8005ff; asc    ;;
 5: len 2; hex 8006; asc   ;;
 6: len 1; hex 81; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 153 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86ee; asc         ;;
 1: len 6; hex 00002443dbc5; asc   $C  ;;
 2: len 7; hex cb000080060110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800600; asc    ;;
 5: len 2; hex 8005; asc   ;;
 6: len 1; hex 83; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 154 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86ef; asc         ;;
 1: len 6; hex 00002443dbc7; asc   $C  ;;
 2: len 7; hex cd0003800c0110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800601; asc    ;;
 5: len 2; hex 8004; asc   ;;
 6: len 1; hex 81; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 155 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86f0; asc         ;;
 1: len 6; hex 00002443dbc9; asc   $C  ;;
 2: len 7; hex cf0000b9c30110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800602; asc    ;;
 5: len 2; hex 8006; asc   ;;
 6: len 1; hex 82; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 156 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86f1; asc         ;;
 1: len 6; hex 00002443dbcb; asc   $C  ;;
 2: len 7; hex d1000200160110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800603; asc    ;;
 5: len 2; hex 8002; asc   ;;
 6: len 1; hex 80; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 157 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86f2; asc         ;;
 1: len 6; hex 00002443dbcd; asc   $C  ;;
 2: len 7; hex d30000c6010110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800604; asc    ;;
 5: len 2; hex 8002; asc   ;;
 6: len 1; hex 87; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 158 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86f3; asc         ;;
 1: len 6; hex 00002443dbcf; asc   $C  ;;
 2: len 7; hex d5000340170110; asc    @   ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800605; asc    ;;
 5: len 2; hex 8003; asc   ;;
 6: len 1; hex 87; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 159 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86f4; asc         ;;
 1: len 6; hex 00002443dbd1; asc   $C  ;;
 2: len 7; hex d7000300060110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800606; asc    ;;
 5: len 2; hex 8002; asc   ;;
 6: len 1; hex 80; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 160 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86f5; asc         ;;
 1: len 6; hex 00002443dbd3; asc   $C  ;;
 2: len 7; hex d9000200260110; asc     &  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800607; asc    ;;
 5: len 2; hex 8002; asc   ;;
 6: len 1; hex 80; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 161 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86f6; asc         ;;
 1: len 6; hex 00002443dbd5; asc   $C  ;;
 2: len 7; hex db000200190110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800608; asc    ;;
 5: len 2; hex 8002; asc   ;;
 6: len 1; hex 87; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 168 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86fd; asc         ;;
 1: len 6; hex 00002443dbe3; asc   $C  ;;
 2: len 7; hex e90000b8540110; asc     T  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800614; asc    ;;
 5: len 2; hex 8001; asc   ;;
 6: len 1; hex 81; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 169 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86fe; asc         ;;
 1: len 6; hex 00002443dbe5; asc   $C  ;;
 2: len 7; hex eb0003c00b0110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800615; asc    ;;
 5: len 2; hex 8000; asc   ;;
 6: len 1; hex 85; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 170 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f86ff; asc         ;;
 1: len 6; hex 00002443dbe7; asc   $C  ;;
 2: len 7; hex ed0000b8490110; asc     I  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800616; asc    ;;
 5: len 2; hex 8003; asc   ;;
 6: len 1; hex 82; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 175 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f8704; asc         ;;
 1: len 6; hex 00002443dbf1; asc   $C  ;;
 2: len 7; hex f7000072080110; asc    r   ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 80061f; asc    ;;
 5: len 2; hex 8000; asc   ;;
 6: len 1; hex 80; asc  ;;
 7: len 4; hex 530a05ea; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 271 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f8764; asc        d;;
 1: len 6; hex 00002443dcb1; asc   $C  ;;
 2: len 7; hex b90003c0210110; asc     !  ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800683; asc    ;;
 5: len 2; hex 8005; asc   ;;
 6: len 1; hex 81; asc  ;;
 7: len 4; hex 530a05eb; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 276 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f8769; asc        i;;
 1: len 6; hex 00002443dcbb; asc   $C  ;;
 2: len 7; hex c30003c0110110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 80068c; asc    ;;
 5: len 2; hex 8001; asc   ;;
 6: len 1; hex 81; asc  ;;
 7: len 4; hex 530a05eb; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 283 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f8770; asc        p;;
 1: len 6; hex 00002443dcc9; asc   $C  ;;
 2: len 7; hex d1000200160110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 800699; asc    ;;
 5: len 2; hex 8001; asc   ;;
 6: len 1; hex 82; asc  ;;
 7: len 4; hex 530a05eb; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

Record lock, heap no 294 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 80000000018f877b; asc        {;;
 1: len 6; hex 00002443dcdf; asc   $C  ;;
 2: len 7; hex e7000400020110; asc        ;;
 3: len 3; hex 800595; asc    ;;
 4: len 3; hex 8006a4; asc    ;;
 5: len 2; hex 8009; asc   ;;
 6: len 1; hex 87; asc  ;;
 7: len 4; hex 530a05eb; asc S   ;;
 8: SQL NULL;
 9: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 186757 n bits 448 index `PRIMARY` of table `Foo`.`MemberRelation` trx id 25B9F723 lock mode S locks rec but not gap waiting

Record lock, heap no 228 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 8000000000217569; asc      !ui;;
 1: len 6; hex 00001aa28e09; asc       ;;
 2: len 7; hex 830002001932c6; asc      2 ;;
 3: len 3; hex 8006a4; asc    ;;
 4: len 3; hex 800d2a; asc   *;;
 5: len 2; hex 8001; asc   ;;
 6: len 1; hex 82; asc  ;;
 7: len 4; hex 52a912d4; asc R   ;;
 8: SQL NULL;
 9: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)

最佳答案

如果您不关心不同步的数据，最简单的解决方案可能是将事务隔离级别更改为READ UNCOMMITTED，这将允许脏读。

你可以这样做:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

如果您正在处理货币交易或性质严重的事情，这不是一个安全的解决方案。

以下是导致死锁的可能原因:

User A starts transaction
User A SELECT {Row 1 ... Row N}
User B starts transaction
User B SELECT {Row 2 ... Row N} //does not contain the locked Row 1 - so this is allowed
User A UPDATE {Row 2 ...} //Can't do this because User B locked Row 2
User B UPDATE {Row 1 ...} //Can't do this because User A locked Row 1

//Both users wait for the other to finish transaction

关于MySQL n00b 的 MySQL 死锁之谜，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22110309/

文章推荐： php - PDOStatement执行SQLSTATE参数个数无效

文章推荐： php - 修复我的 php 脚本未知错误

文章推荐： mysql - 在 SQL 中返回列表和计数

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 n00b 的 MySQL 死锁之谜