mysql - 简单删除查询上的 mysql/innodb 死锁

Question

mysql/innodb，可重复读取事务，非拦截行删除死锁。试图了解正在发生的事情。

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2019-05-08 06:16:23 7f4a5769a700
    *** (1) TRANSACTION:
    TRANSACTION 12314813, ACTIVE 1 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 13 lock struct(s), heap size 2936, 355 row lock(s), undo log entries 110
    MySQL thread id 1654182, OS thread handle 0x7f4a57658700, query id 21892885 xx.xx.xx.xx oc5z updating
    DELETE FROM deal_product_rows_tmp WHERE batch_no=5754
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 219 page no 3 n bits 168 index `PRIMARY` of table `db1`.`deal_product_rows_tmp` trx id 12314813 lock_mode X waiting
    Record lock, heap no 88 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
     0: len 4; hex 80001bb9; asc     ;;
     1: len 6; hex 000000bbe8c0; asc       ;;
     2: len 7; hex be000001b50110; asc        ;;
     3: len 4; hex 8000167b; asc    {;;
     4: len 4; hex 800fe7c4; asc     ;;
     5: len 4; hex 80002516; asc   % ;;
     6: len 4; hex 8000986e; asc    n;;
     7: len 4; hex 80000003; asc     ;;
     8: len 30; hex 415254455820d184d0bed180d0bcd18b20d0bfd180d18fd0bcd0bed183d0; asc PROD                        ; (total 81 bytes);
     9: len 8; hex 0000000000208c40; asc        @;;
     10: len 1; hex 4d; asc M;;

    *** (2) TRANSACTION:
    TRANSACTION 12314816, ACTIVE 1 sec starting index read
    mysql tables in use 1, locked 1
    14 lock struct(s), heap size 2936, 95 row lock(s), undo log entries 14
    MySQL thread id 1654175, OS thread handle 0x7f4a5769a700, query id 21892888 xx.xx.xx.xx oc5z updating
    DELETE FROM deal_product_rows_tmp WHERE batch_no=5755
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 219 page no 3 n bits 168 index `PRIMARY` of table `db1`.`deal_product_rows_tmp` trx id 12314816 lock_mode X locks rec but not gap
    Record lock, heap no 25 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

表结构：

     CREATE TABLE `deal_product_rows_tmp` (
      `batch_no` int(11) NOT NULL,
      `bitrix_id` int(11) NOT NULL,
      `deal_id` int(11) NOT NULL,
      `product_id` int(11) NOT NULL,
      `quantity` int(11) NOT NULL,
      `product_name` varchar(1000) NOT NULL,
      `price` double DEFAULT NULL,
      `status` varchar(50) NOT NULL,
      `tmp_id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`tmp_id`),
      KEY `idx_deal_row_tmp_deal` (`deal_id`),
      KEY `idx_deal_row_tmp_batchno` (`batch_no`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7500 DEFAULT CHARSET=utf8 

我是否理解正确：第一个事务试图在索引页的所有行上应用间隙锁。一个一个的记录（Next-Key Locks）。第二笔交易尝试相同，但方向不同。结果部分索引页被事务 1 锁定，部分被事务 2 锁定。我知道我可以将事务隔离级别降低到 RC，我试图了解可重复读取在 innodb 中是如何工作的。

更新：与 Read Committed IL 的情况相同。

标签： mysqlinnodbdeadlock