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

Bug #1255147 reported by Alex Yurchenko on 2013-11-26
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Status tracked in 5.6
5.5
Low
Seppo Jaakola
5.6
Low
Seppo Jaakola
Percona XtraDB Cluster
Status tracked in 5.6
5.5
Undecided
Unassigned
5.6
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?

Alex Yurchenko (ayurchen) wrote :

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

Yep, looks like it isn't.

Seppo Jaakola (seppo-jaakola) wrote :

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) wrote :

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

Seppo Jaakola (seppo-jaakola) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers