Bug #78406 Simple insert deadlock when selected from partitioned table being updated
Submitted: 11 Sep 2015 10:21
Reporter: Przemyslaw Malkowski Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.25 OS:Any
Assigned to: CPU Architecture:Any

[11 Sep 2015 10:21] Przemyslaw Malkowski
Description:
Under some specific conditions, you may hit a deadlock on a simple, single insert transaction when it uses select from a table that is being updated.
This is not easy to reproduce as it seems that it very much depends on locking ranges and the stage of the selected table update, when an insert comes in. 
However, I was not able to repeat the same problem with non-partitioned table - the insert always waits for granted locks and never deadlock occurs.

How to repeat:
(In this example the update is very non optimal due to the missing index)
MySQL settings are all default, it's a generic mysqlsandbox instance, autocommit=1 and I did not start transactions explicitly.

mysql [localhost] {msandbox} (db1) > show create table s1\G
*************************** 1. row ***************************
       Table: s1
Create Table: CREATE TABLE `s1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c_new` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000
/*!50100 PARTITION BY KEY (id)
PARTITIONS 32 */
1 row in set (0.01 sec)

mysql [localhost] {msandbox} (db1) > show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=966 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (db1) > explain partitions insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: p8,p9,p10,p11,p13,p17,p21
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 54
        Extra: Using where; Using index
1 row in set (0.01 sec)

mysql [localhost] {msandbox} (db1) > explain partitions update s1 set c_new="A",pad=now() where k between 25115053 and 45115053\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: s1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 4925520
        Extra: Using where
1 row in set (0.00 sec)

(session 1)
mysql [localhost] {msandbox} (db1) > update s1 set c_new="A",pad=now() where k between 25115053 and 45115053;
Query OK, 1616581 rows affected (28.66 sec)
Rows matched: 1616581  Changed: 1616581  Warnings: 0

(session 2 - started just after above update)
mysql [localhost] {msandbox} (db1) > insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (db1) > insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (db1) > insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (db1) > insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (db1) > insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (db1) > insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (db1) > insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

InnoDB engine status:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-09-11 05:48:48 7ffdbc4f8700
*** (1) TRANSACTION:
TRANSACTION 23288, ACTIVE 8 sec starting index read
mysql tables in use 8, locked 8
LOCK WAIT 10 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x7ffdbc4b7700, query id 185 localhost msandbox Sending data
insert into t2 (b,d) select id,k from s1 where id in(305775,1499265,4307632,2017878,1845720,3591637,2390616,100,2000)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 224 n bits 144 index `PRIMARY` of table `db1`.`s1` /* Partition `p8` */ trx id 23288 lock mode S waiting
Record lock, heap no 42 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 0004aa6f; asc    o;;
 1: len 4; hex 017f39ac; asc   9 ;;
 2: len 6; hex 000000005a54; asc     ZT;;
 3: len 7; hex 40000001fc0a3d; asc @     =;;
 4: len 30; hex 412020202020202020202020202020202020202020202020202020202020; asc A                             ; (total 120 bytes);
 5: len 30; hex 323031352d30392d31312030353a30393a31352020202020202020202020; asc 2015-09-11 05:09:15           ; (total 60 bytes);

*** (2) TRANSACTION:
TRANSACTION 23277, ACTIVE 13 sec fetching rows
mysql tables in use 32, locked 32
35982 lock struct(s), heap size 3503656, 2659884 row lock(s), undo log entries 849224
MySQL thread id 1, OS thread handle 0x7ffdbc4f8700, query id 178 localhost msandbox updating
update s1 set c_new="A",pad=now() where k between 25115053 and 45115053
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 17 page no 224 n bits 144 index `PRIMARY` of table `db1`.`s1` /* Partition `p8` */ trx id 23277 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
(...)

*** WE ROLL BACK TRANSACTION (1)

Suggested fix:
IMHO the deadlock conditions should be consistent whether a table is partitioned on not.