在mysql中重现这样的死锁 [英] reproduce such deadlock in mysql

问题描述

我从SHOW ENGINE INNODB STATUS获得信息

  ***（1）TRANSACTION：
 TRANSACTION 0 2799914 ，活动1秒，进程号4106，操作系统线程号139808903796480插入
 mysql表正在使用1，锁定1 
锁定等待10锁结构，堆大小1216,7行锁，撤销日志条目3 
 MySQL线程ID 4284，查询ID 2889649本地主机127.0.0.1测试更新
 INSERT INTO航运..... 
 ***（1）等待这个锁GRANTED：
 RECORD LOCKS空格ID 0页码436366 n位88索引表`testdatabase`.```的主键`trx id 0 2799914锁定模式S锁定记录，但不间隙等待
记录锁定，堆没有14物理记录：n_fields 213;紧凑的格式;信息位0 
 ..........; 
 $ b ***（2）TRANSACTION：
 TRANSACTION 0 2799913，ACTIVE 1秒，进程号4106，OS线程号139808905824000开始索引读取，线程在InnoDB中声明500 
 mysql表使用1，锁定1 
 5锁结构，堆大小1216,5行锁，撤消日志条目4 
 MySQL线程ID 4290，查询ID 2889711本地主机127.0.0.1测试更新
 UPDATE命令
 ........ 
 ***（2）提示锁定（$）
记录锁定space id 0 page no 436366 n bits 88 index`PRIMARY` of table`testdatabase`.``` trx id 0 2799913 lock_mode X锁定rec，但不锁定
记录锁定，堆号14物理记录：n_fields 213;紧凑的格式;信息位0 
 .......... 
 
 ***（2）等待这个锁定：
记录锁定space id 0 page no 153737 n位88索引表`testdatabase`.``` PRIMARY` trx id 0 2799913 lock_mode X锁定rec，但没有间隔等待
记录锁定，堆号10物理记录：n_fields 213;紧凑的格式;信息位0 
 ...... 
 
 ***我们回滚事务（2）
 ------------ 
交易
  

船只中有一个FK指的是订单主键。 b

我认为T2已经拥有了x锁，为什么它仍然需要等待一个x锁。

有人可以帮助我重现这样的死锁在MySQL？

谢谢。

解决方案

但好像你插入到子表中的行，然后在父表中进行更新。

如果这是真的，你已经在MySQL的这个问题： http://bugs.mysql.com/bug.php?id=48652

如果在表上定义了FOREIGN KEY约束，则需要检查约束条件的任何插入，更新或删除共享记录级别锁定在它检查约束的记录上。在约束失败的情况下，InnoDB也会设置这些锁。

在第一个表的单个记录中，您有：

1. 来自交易1集合的S锁，
   
2. 来自交易2集合的S锁，
   
3. X锁
   
4. 来自交易2的X锁请求，被来自交易1的S锁阻止

可能的解决方案是首先更新父表，然后将行插入到子表中。假设我们需要在子行插入时增加一些计数器，那么查询就是：

pre $ UPDATE< parent row> SET count = count + 1;
INSERT< child row>; / *如果INSERT失败，则回滚trx * /

如果您只想更新父行在插入子行之后，可以使用 FOR UPDATE 语句在父行上设置锁：

  SELECT<父行> FOR UPDATE; 
 INSERT< child row>; / *如果INSERT失败，则回滚trx * / 
 UPDATE< parent row> SET count = count + 1; 
  

I got information from SHOW ENGINE INNODB STATUS

    *** (1) TRANSACTION: 
TRANSACTION 0 2799914, ACTIVE 1 sec, process no 4106, OS thread id 139808903796480 inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1216, 7 row lock(s), undo log entries 3
MySQL thread id 4284, query id 2889649 localhost 127.0.0.1 test update
INSERT INTO shipping .....
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 436366 n bits 88 index `PRIMARY` of table `testdatabase`.`order` trx id 0 2799914 lock mode S locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 213; compact format; info bits 0
..........;

*** (2) TRANSACTION:
TRANSACTION 0 2799913, ACTIVE 1 sec, process no 4106, OS thread id 139808905824000 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216, 5 row lock(s), undo log entries 4
MySQL thread id 4290, query id 2889711 localhost 127.0.0.1 test Updating
UPDATE order
........
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 436366 n bits 88 index `PRIMARY` of table `testdatabase`.`order` trx id 0 2799913 lock_mode X locks rec but not gap
Record lock, heap no 14 PHYSICAL RECORD: n_fields 213; compact format; info bits 0
..........

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 153737 n bits 88 index `PRIMARY` of table `testdatabase`.`order` trx id 0 2799913 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 213; compact format; info bits 0
......

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

there is a FK in ship referring to order primary key.

I think T2 has hold x-lock, why it still need wait for a x-lock.

Can some one help me to reproduce such deadlock in mysql?

Thanks.

解决方案

I don't know your queries, but seems like you're inserting row into child table, and then doing an update of row in parent table.

If that's true, you've hit this issue in MySQL: http://bugs.mysql.com/bug.php?id=48652

If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.

On a single record of the first table you have:

1. S lock from transaction 1 set,
2. S lock from transaction 2 set,
3. X lock from transaction 1 requested, blocked by S lock from transaction 2,
4. X lock from transaction 2 requested, blocked by S lock from transaction 1

Possible solution is to first update parent table, then insert row into child table. Suppose we need to increment some counter upon child row insertion, then queries would be:

UPDATE <parent row> SET count = count + 1;
INSERT <child row>; /* if the INSERT fails, roll back the trx */

If you want to update parent row only after inserting child row, you can use FOR UPDATE statement to set a lock on parent row:

SELECT <parent row> FOR UPDATE;
INSERT <child row>; /* if the INSERT fails, roll back the trx */
UPDATE <parent row> SET count = count + 1;

这篇关于在mysql中重现这样的死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！