死锁原因求教

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test values(2,'a');
insert into test values(1000,'b');
insert into test values(10000,'c');

session1:
begin;
delete from test where id = 10;
  
                                    session2:
                                    begin;
                                    delete from test where id = 10;

session1:
insert into test values(50,'aaa');

session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
                    trx_id: 5957
                 trx_state: RUNNING
               trx_started: 2016-12-27 05:16:51
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 3
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 5956
                 trx_state: LOCK WAIT
               trx_started: 2016-12-27 05:16:43
     trx_requested_lock_id: 5956:36:3:3
          trx_wait_started: 2016-12-27 05:16:55
                trx_weight: 3
       trx_mysql_thread_id: 2
                 trx_query: insert into test values(50,'aaa')
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

*************************** 1. row ***************************
    lock_id: 5956:36:3:3
lock_trx_id: 5956
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `kfpanda`.`test`
 lock_index: PRIMARY
 lock_space: 36
  lock_page: 3
   lock_rec: 3
  lock_data: 1000
*************************** 2. row ***************************
    lock_id: 5957:36:3:3
lock_trx_id: 5957
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `kfpanda`.`test`
 lock_index: PRIMARY
 lock_space: 36
  lock_page: 3
   lock_rec: 3
  lock_data: 1000
2 rows in set, 1 warning (0.00 sec)

*************************** 1. row ***************************
requesting_trx_id: 5956
requested_lock_id: 5956:36:3:3
  blocking_trx_id: 5957
 blocking_lock_id: 5957:36:3:3
1 row in set, 1 warning (0.00 sec)


                                    session2:
                                    insert into test values(100,'bbb');
                                    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

                                    
session1:
Query OK, 1 row affected (17.66 sec)        
                                    

问题:在delete时有持有锁吗? 为啥会产生死锁?
已邀请:

youge - http://www.yougemysqldba.com/

赞同来自: hanchangyue lei

首先,你session 1 的delete一个不存在的记录会产生一个gap lock 在 (2,1000)之间
其次,你session 2的delete也会产生同样的gap lock
但是两个gap lock是相容的
最后你insert,需要x lock与之前的gap lock不兼容,所以会死锁。
 
我这边也可以提问MySQL相关问题
http://www.yougemysqldba.com/
 

kfpanda

赞同来自: A390_Fander

Can someone answer this question?

kfpanda

赞同来自:

anybody here?

wubx - 专注MySQL及架构设计

赞同来自:

这个锁和Delete一个不存在记录没关系。
请查询gap lock 这个锁是怎么会事。

hanchangyue - 8090后SA

赞同来自:

---TRANSACTION 27795, ACTIVE 3 sec
1 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 38, OS thread handle 139867293804288, query id 11791 localhost root cleaning up
TABLE LOCK table `wubx`.`t1` trx id 27795 lock mode IX
---TRANSACTION 27794, ACTIVE 90 sec
1 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 36, OS thread handle 139867294205696, query id 11782 localhost root cleaning up
TABLE LOCK table `wubx`.`t1` trx id 27794 lock mode IX
删除不存在的行,有持有锁的,意向X锁

要回复问题请先登录注册