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.
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.