代码之家 › 专栏 › 技术社区 › Zimbabao

MySQL事务正在等待已授予的锁..这会导致死锁

database-deadlocks deadlock mysql

10

Zimbabao · 技术社区 · 15 年前

如果以下情况是mysql中的一个bug?.

MySQL版本:mysql.x86_64 5.0.77-4.el5_4.1

内核:Linux Box2 2.6.18-128.el5 1 SMP Wed Jan 21 10:41:14 EST 2009 x86 x86 x86 GNU/Linux

------------------------
LATEST DETECTED DEADLOCK
------------------------
100125  4:24:41
*** (1) TRANSACTION:
TRANSACTION 0 210510625, ACTIVE 155 sec, process no 28125, OS thread id 1243162944 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 162928579, query id 527252744 box22 172.16.11.105 user updating
delete from user_grid_items where user_id = 669786974 and START_X = 45 and START_Y = 65
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 61372 n bits 328 index `PRIMARY` of table `gamesutra_beta/user_grid_items` trx id 0 210510625 lock_mode X locks rec but not gap waiting
Record lock, heap no 127 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 8; hex 0000000027ec235e; asc     ' #^;; 1: len 4; hex 0000002d; asc    -;; 2: len 4; hex 00000041; asc    A;; 3: len 6; hex 00000b561243; asc    V C;; 4: len 7; hex 80000040070110; asc    @   ;; 5: len 23; hex 474949445f414e494d414c535f53515549445f50494e4b; asc GIID_ANIMALS_SQUID_PINK;; 6: len 4; hex cb59f060; asc  Y `;; 7: len 4; hex 4b59f060; asc KY `;; 8: len 4; hex 80000000; asc     ;; 9: len 1; hex 80; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 0 210505911, ACTIVE 555 sec, process no 28125, OS thread id 1184323904 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 162924258, query id 527252762 box22 172.16.11.105 user updating
delete from user_grid_items where user_id = 669786974 and START_X = 45 and START_Y = 65
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 61372 n bits 328 index `PRIMARY` of table `gamesutra_beta/user_grid_items` trx id 0 210505911 lock mode S locks rec but not gap
Record lock, heap no 127 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 8; hex 0000000027ec235e; asc     ' #^;; 1: len 4; hex 0000002d; asc    -;; 2: len 4; hex 00000041; asc    A;; 3: len 6; hex 00000b561243; asc    V C;; 4: len 7; hex 80000040070110; asc    @   ;; 5: len 23; hex 474949445f414e494d414c535f53515549445f50494e4b; asc GIID_ANIMALS_SQUID_PINK;; 6: len 4; hex cb59f060; asc  Y `;; 7: len 4; hex 4b59f060; asc KY `;; 8: len 4; hex 80000000; asc     ;; 9: len 1; hex 80; asc  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 61372 n bits 328 index `PRIMARY` of table `gamesutra_beta/user_grid_items` trx id 0 210505911 lock_mode X locks rec but not gap waiting
Record lock, heap no 127 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 8; hex 0000000027ec235e; asc     ' #^;; 1: len 4; hex 0000002d; asc    -;; 2: len 4; hex 00000041; asc    A;; 3: len 6; hex 00000b561243; asc    V C;; 4: len 7; hex 80000040070110; asc    @   ;; 5: len 23; hex 474949445f414e494d414c535f53515549445f50494e4b; asc GIID_ANIMALS_SQUID_PINK;; 6: len 4; hex cb59f060; asc  Y `;; 7: len 4; hex 4b59f060; asc KY `;; 8: len 4; hex 80000000; asc     ;; 9: len 1; hex 80; asc  ;;

*** WE ROLL BACK TRANSACTION (2)
------------

3 回复 | 直到 10 年前

1

6

Marcus Adams 10 年前

有时,show engine innodb status很难破译,因为它只显示事务中的当前语句。它不显示以前在同一事务中发生的语句,这些语句可能获取了实际持有的锁。

在您的情况下,事务2中的前一条语句获取了所讨论行的共享锁。

然后,事务1试图获取同一行上的独占锁,并愉快地等待共享锁被删除。

然后,事务2在另一条语句中试图获取同一行的独占锁。典型的僵局。两个事务都无法完成。

帮助避免这种死锁的一个解决方案是在事务2中用 SELECT FOR UPDATE 语句,在获取共享锁的语句之前。

2

1

DRapp 13 年前

我很早以前就读过一些东西,不知道你会不会因此而遇到…没有看到当前事务的代码与它所冲突的内容。

在处理您的事务时,您应该尝试让它们始终按相同的顺序执行任何锁定…对于订单/订单明细/付款系统,请按照此处列出的所有类似订单的顺序执行活动。如果有另一个进程按“order detail/order”顺序尝试其事务,则可能导致死锁。

一个事务是先锁定订单,然后处理订单详细信息。另一个事务先锁定订单详细信息,然后尝试获取订单头。

高温高压

3

-4

Akos 13 年前

使用 SHOW ENGINE INNODB STATUS 以确定最新死锁的原因。这可以帮助您优化应用程序以避免死锁。

如果事务由于死锁而失败,请随时准备重新发出该事务。死锁并不危险。再试一次。