MySQL事务死锁

Question

MySQL事务死锁

mysqldeadlock

5

我不时会遇到由于竞争条件引起的MySQL死锁错误。我已经用以下方法复制了该错误。

事务1:

开始事务

将fixtrades表中id为10的行插入到fixtradeshistory表中

事务2:

开始事务

将fixtrades表中id为10的行插入到fixtradeshistory表中

事务1:

更新id为10的fixtrades表，将其状态设置为“bla”，将订购ID设置为“bla”

事务2 DEADLOCK:

更新id为10的fixtrades表，将其状态设置为“bla”，将订购ID设置为“bla”

有什么想法为什么会发生这种死锁吗？

------------------------
LATEST DETECTED DEADLOCK
------------------------
110317 14:52:08
(1) TRANSACTION:
TRANSACTION 0 57841252, ACTIVE 16 sec, process no 2976, OS thread id 3030973328 starting index read`
mysql tables in use 1, locked 1
LOCK WAIT 15 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 326855, query id 2689051 localhost salert Updating
update fixtrades set fixtradesstatustypesid='orderplaced', fixgatewayorderid='BANZAI>EXEC:1288679244240:520703' where id=10
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 232059 n bits 136 index PRIMARY` of table `salert/fixtrades` trx id 0 57841252 lock_mode X locks rec but not gap waiting
Record lock, heap no 66 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;; 1: len 6; hex 00000371a2cd; asc    q  ;; 2: len 7; hex 000004f8400770; asc     @ p;; 3: len 4; hex 80000004; asc     ;; 4: len 4; hex 80000004; asc     ;; 5: len 4; hex 80000364; asc    d;; 6: len 4; hex 800040aa; asc   @ ;; 7: SQL NULL; 8: len 13; hex 6f726465726163636570746564; asc orderaccepted;; 9: len 30; hex 42414e5a41493e455845433a313238383637393234343234303a35323037; asc BANZAI>EXEC:1288679244240:5207;...(truncated); 10: SQL NULL; 11: len 8; hex 73656c6c6c6f6e67; asc selllong;; 12: len 5; hex 564f442e4c; asc VOD.L;; 13: len 3; hex 313030; asc 100;; 14: SQL NULL; 15: SQL NULL; 16: len 4; hex 4d80e0cc; asc M   ;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: len 6; hex 6d61726b6574; asc market;; 21: SQL NULL; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 0 57841255, ACTIVE 7 sec, process no 2976, OS thread id 3030371216 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 326860, query id 2689066 localhost salert Updating
update fixtrades set fixtradesstatustypesid='orderplaced', fixgatewayorderid='BANZAI>EXEC:1288679244240:520703' where id=10
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 232059 n bits 136 index `PRIMARY` of table `salert/fixtrades` trx id 0 57841255 lock mode S locks rec but not gap
Record lock, heap no 66 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;; 1: len 6; hex 00000371a2cd; asc    q  ;; 2: len 7; hex 000004f8400770; asc     @ p;; 3: len 4; hex 80000004; asc     ;; 4: len 4; hex 80000004; asc     ;; 5: len 4; hex 80000364; asc    d;; 6: len 4; hex 800040aa; asc   @ ;; 7: SQL NULL; 8: len 13; hex 6f726465726163636570746564; asc orderaccepted;; 9: len 30; hex 42414e5a41493e455845433a313238383637393234343234303a35323037; asc BANZAI>EXEC:1288679244240:5207;...(truncated); 10: SQL NULL; 11: len 8; hex 73656c6c6c6f6e67; asc selllong;; 12: len 5; hex 564f442e4c; asc VOD.L;; 13: len 3; hex 313030; asc 100;; 14: SQL NULL; 15: SQL NULL; 16: len 4; hex 4d80e0cc; asc M   ;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: len 6; hex 6d61726b6574; asc market;; 21: SQL NULL; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 232059 n bits 136 index `PRIMARY` of table `salert/fixtrades` trx id 0 57841255 lock_mode X locks rec but not gap waiting
Record lock, heap no 66 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;; 1: len 6; hex 00000371a2cd; asc    q  ;; 2: len 7; hex 000004f8400770; asc     @ p;; 3: len 4; hex 80000004; asc     ;; 4: len 4; hex 80000004; asc     ;; 5: len 4; hex 80000364; asc    d;; 6: len 4; hex 800040aa; asc   @ ;; 7: SQL NULL; 8: len 13; hex 6f726465726163636570746564; asc orderaccepted;; 9: len 30; hex 42414e5a41493e455845433a313238383637393234343234303a35323037; asc BANZAI>EXEC:1288679244240:5207;...(truncated); 10: SQL NULL; 11: len 8; hex 73656c6c6c6f6e67; asc selllong;; 12: len 5; hex 564f442e4c; asc VOD.L;; 13: len 3; hex 313030; asc 100;; 14: SQL NULL; 15: SQL NULL; 16: len 4; hex 4d80e0cc; asc M   ;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: len 6; hex 6d61726b6574; asc market;; 21: SQL NULL; 22: SQL NULL; 23: SQL NULL; 24: SQL NULL; 25: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)

- cottaway

你能否也贴出 "show inodb status" 的内容。 - Zimbabao

3个回答

1

因为事务2在读取时获取了fixtrades id=10的S(共享)锁，所以事务1试图获取一个X(排他)锁来修改记录是不可能成功的。

我认为你可以通过将插入更改为以下内容来解决这个问题：

insert into fixtradeshistory (select null, fixtrades.* from fixtrades where id=10 FOR UPDATE);

- ggiroux

1

您也可以通过在插入操作之前执行UPDATE操作来解决问题。这会导致事务1在第一次运行时获得独占锁，而不是共享锁。事务2仍将被卡住等待事务1完成。但是，事务1将能够完成，因为它已经拥有所需的独占锁。

请参见http://vimeo.com/12941188以获取更完整的解释。

- phylae

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Charles · Accepted Answer

有什么想法，为什么会出现死锁呢？ InnoDB 有许多锁定模式。我们这里看到了行级锁，但它却失败了。

事务 #1 正在等待一个独占锁：

RECORD LOCKS ... page no 232059 ... lock_mode X locks rec but not gap waiting

事务#2已经在同一行上获得了共享锁：

RECORD LOCKS ... page no 232059 ... lock mode S locks rec but not gap

当#2拥有共享锁时，它还想要独占锁：

RECORD LOCKS ... page no 232059 ... lock_mode X locks rec but not gap waiting

#1和#2都需要相同的“独占”锁，但在#2“释放共享锁”之前，两个锁都无法被授予。但是#2无法释放共享锁，直到它被升级为独占锁，但这永远不会发生。这会导致死锁，InnoDB将终止其中一个事务并执行回滚。文档页面中描述了类似的情况。有两种方法可以处理这个问题。第一种方式是最差的方式，它使用表锁而不是事务。这将防止多个写入者（或可选读取器）同时修改表。这可能会影响性能，并打开更糟糕的死锁情况。此外，您不能混合表锁和事务。第二种更好的方法是修改应用程序以优雅地处理死锁。这意味着确保回滚发生并再次尝试或向用户呈现适当的错误。