INSERT returns deadlock instead of duplicate key on secondary unique key collision

[Alex Yurchenko]

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

[Raghavendra D Prabhu (raghavendra-prabhu)]

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?

[Alex Yurchenko (ayurchen)]

Looks like Percona server is not affected by this. Just reproduced it again with codership-mysql/5.6 branch.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Yep, looks like it isn't.

[Seppo Jaakola (seppo-jaakola)]

wsrep-5.6 build seems to run as expected for me:

Server version: 5.6.15-debug-log Source distribution, wsrep_25.2.r4032

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE t(p INT AUTO_INCREMENT PRIMARY KEY, u INT UNIQUE KEY);
Query OK, 0 rows affected (0.02 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 1062 (23000): Duplicate entry '1' for key 'u'
mysql> INSERT IGNORE INTO t VALUES(DEFAULT, 1);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+---+------+
| p | u |
+---+------+
| 1 | 1 |
+---+------+
1 row in set (0.00 sec)

[Seppo Jaakola (seppo-jaakola)]

okay, can catch the problem now. This issue happens only when 'wsrep_drupal_282555_workaround' is defined

[Seppo Jaakola (seppo-jaakola)]

Pushed a fix, where under wsrep_drupal_282555_workaround, a DUPKEY failure will be tried once, if wsrep_retry_autocommit is also set.

Note that you will set both wsrep_drupal_282555_workaround and wsrep_retry_autocommit (>= 1), to enable DUPKEY cases to retry. And even then, retrying will happen exactly once.

The fix is pushed in revision: http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4033

[Seppo Jaakola (seppo-jaakola)]

Fix for wsrep-5.5 was merged in revision: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3941

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1529