INSERT returns deadlock instead of duplicate key on secondary unique key collision
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership | Status tracked in 5.6 | |||||
5.5 |
Fix Released
|
Low
|
Seppo Jaakola | |||
5.6 |
Fix Released
|
Low
|
Seppo Jaakola | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Fix Released
|
Undecided
|
Unassigned | |||
5.6 |
Invalid
|
Undecided
|
Unassigned |
Bug Description
This happens when the primary key is AUTO_INCREMENT and DEFAULT value is inserted into it. How to reproduce:
mysql> CREATE TABLE t(p INT AUTO_INCREMENT PRIMARY KEY, u INT UNIQUE KEY);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES(1, 1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t VALUES(2, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'u'
mysql> INSERT IGNORE INTO t VALUES(2, 1);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES(DEFAULT, 1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> INSERT IGNORE INTO t VALUES(DEFAULT, 1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Not able to repeat this with latest revision:
>>mysql -S /pxc56/ datadir/ pxc.sock -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.14-25.1-debug Percona XtraDB Cluster (GPL) 5.6.14-25.1, Revision 563, wsrep_25.1.r581
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use sbtest;
ERROR 1049 (42000): Unknown database 'sbtest'
MySQL [(none)]> use test;
Database changed
MySQL [test]> drop table t;
Query OK, 0 rows affected (0.03 sec)
MySQL [test]> CREATE TABLE t(p INT AUTO_INCREMENT PRIMARY KEY, u INT UNIQUE KEY);
Query OK, 0 rows affected (0.02 sec)
MySQL [test]> INSERT INTO t VALUES(1, 1);
Query OK, 1 row affected (0.00 sec)
MySQL [test]> INSERT INTO t VALUES(2, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'u'
MySQL [test]> INSERT IGNORE INTO t VALUES(2, 1);
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> INSERT INTO t VALUES(DEFAULT, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'u'
MySQL [test]>
MySQL [test]> INSERT IGNORE INTO t VALUES(DEFAULT, 1);
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> select * from t;
+---+------+
| p | u |
+---+------+
| 1 | 1 |
+---+------+
1 row in set (0.01 sec)
MySQL [test]>
======= ======= ======= ======= ======= ======= ======= ====
Are there any other conditions required for this?