Concurrent INSERT ... SELECT statements into a partitioned InnoDB table with AUTO_INCREMENT end in a deadlock

Likely cause: old code, not a recent change. MySQL 5.5 and 5.6 give the same deadlock. MySQL 5.7 and later do not. There, InnoDB handles the partitions itself and does not take an AUTO-INC lock for each partition.

--source include/have_innodb.inc

--source include/have_partition.inc

--source include/have_sequence.inc

CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c INT) ENGINE=InnoDB PARTITION BY HASH (id) PARTITIONS 2;

--connect (con1,localhost,root,,)

--send INSERT INTO t1 (c) SELECT SLEEP(1) FROM seq_1_to_3

--connection default

--sleep 0.5

--error 0,ER_LOCK_DEADLOCK

INSERT INTO t1 (c) SELECT SLEEP(1) FROM seq_1_to_2;

--let $e1= $mysql_errno

--connection con1

--error 0,ER_LOCK_DEADLOCK

--reap

--let $e2= $mysql_errno

--disconnect con1

--connection default

--let $e= `SELECT $e1 + $e2`

if ($e)

{

  --die Two concurrent INSERT ... SELECT into a partitioned InnoDB table with AUTO_INCREMENT end in a deadlock

}

DROP TABLE t1;

Leads to:

mysqltest: At line 21: Two concurrent INSERT ... SELECT into a partitioned InnoDB table with AUTO_INCREMENT end in a deadlock

The INSERT on the default connection fails with ERROR 1213 (40001): Deadlock found when trying to get lock. Without partitioning, both statements succeed. The deadlock report in SHOW ENGINE INNODB STATUS shows each transaction waiting for the AUTO-INC lock of the partition that the other transaction holds. Debug builds give the same result.

The same deadlock happens with explicit values in the INSERT ... SELECT, with an UPDATE that changes the AUTO_INCREMENT column, and with a multi-row INSERT when innodb_autoinc_lock_mode=0. With 12 sessions that each run 400 INSERT ... SELECT statements of 10 rows into 4 HASH partitions, 4341 of the 4800 statements fail with ERROR 1213 on an optimized build. On a table without partitioning, none fail.

+--------+-----------------------+---------------+

| MySQL  | INSERT on default     | Rows in t1    |

+--------+-----------------------+---------------+

| 5.5.62 | ERROR 1213 (deadlock) | 1, 3, 4       |

| 5.6.51 | ERROR 1213 (deadlock) | 1, 3, 4       |

| 5.7.44 | no error              | 1, 2, 3, 4, 5 |

| 8.0.36 | no error              | 1, 2, 3, 4, 5 |

| 9.1.0  | no error              | 1, 2, 3, 4, 5 |

+--------+-----------------------+---------------+

From the code it seems that with innodb_autoinc_lock_mode=1, the default, an INSERT ... SELECT takes the AUTO-INC lock of each partition it writes to (ha_innobase::innobase_set_max_autoinc()) and holds it to the end of the statement. The rows of one statement go to different partitions, so two statements take these locks in different orders.