Description:
When there is a table with a UNIQUE index and if we try to insert a value that fails to respect the uniqueness, a lock is raised in the unique key index and never released until commit or rollback. The same does not happen if we respect the UNIQUE constraint.
An example that is WORKING:
CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c2` varchar(20) DEFAULT NULL,
`autoTs` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `c2_idx` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=21102 DEFAULT CHARSET=utf8;
# session 1
master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test');
Query OK, 1 row affected (0.00 sec)
master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test1');
Query OK, 1 row affected (0.00 sec)
master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test2');
Query OK, 1 row affected (0.00 sec)
# session 2
master [localhost:48008] {msandbox} (test) > insert into t1 (id,c2) value (null, 'test5');
Query OK, 1 row affected (0.00 sec)
master [localhost:48008] {msandbox} (test) > insert into t1 (id,c2) value (null, 'test6');
Query OK, 1 row affected (0.00 sec)
It works. Now the problematic case.
How to repeat:
CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c2` varchar(20) DEFAULT NULL,
`autoTs` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `c2_idx` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=21102 DEFAULT CHARSET=utf8;
# session 1
master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test8');
Query OK, 1 row affected (0.00 sec)
master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test8');
ERROR 1062 (23000): Duplicate entry 'test8' for key 'c2_idx'
# session 2 - hangs
master [localhost:48008] {msandbox} (test) > insert into t1 (id,c2) value (null, 'test6');
If we look at SEIS output:
TABLE LOCK table `test`.`t1` trx id 1339 lock mode IX
RECORD LOCKS space id 23 page no 4 n bits 72 index c2_idx of table `test`.`t1` trx id 1339 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 5; hex 7465737438; asc test8;;
1: len 4; hex 00000001; asc ;;
RECORD LOCKS space id 23 page no 4 n bits 72 index c2_idx of table `test`.`t1` trx id 1339 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 5; hex 7465737438; asc test8;;
1: len 4; hex 00000001; asc ;;
Suggested fix:
The lock should be held only during the time the key is being checked, not for the entire transaction.
I tested with different isolations levels and the situation is the same (REPEATABLE READ and READ COMMITTED)
Description: When there is a table with a UNIQUE index and if we try to insert a value that fails to respect the uniqueness, a lock is raised in the unique key index and never released until commit or rollback. The same does not happen if we respect the UNIQUE constraint. An example that is WORKING: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(20) DEFAULT NULL, `autoTs` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `c2_idx` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=21102 DEFAULT CHARSET=utf8; # session 1 master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test'); Query OK, 1 row affected (0.00 sec) master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test1'); Query OK, 1 row affected (0.00 sec) master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test2'); Query OK, 1 row affected (0.00 sec) # session 2 master [localhost:48008] {msandbox} (test) > insert into t1 (id,c2) value (null, 'test5'); Query OK, 1 row affected (0.00 sec) master [localhost:48008] {msandbox} (test) > insert into t1 (id,c2) value (null, 'test6'); Query OK, 1 row affected (0.00 sec) It works. Now the problematic case. How to repeat: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(20) DEFAULT NULL, `autoTs` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `c2_idx` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=21102 DEFAULT CHARSET=utf8; # session 1 master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test8'); Query OK, 1 row affected (0.00 sec) master [localhost:48008] {msandbox} (test) > insert into t1 (c2) value ('test8'); ERROR 1062 (23000): Duplicate entry 'test8' for key 'c2_idx' # session 2 - hangs master [localhost:48008] {msandbox} (test) > insert into t1 (id,c2) value (null, 'test6'); If we look at SEIS output: TABLE LOCK table `test`.`t1` trx id 1339 lock mode IX RECORD LOCKS space id 23 page no 4 n bits 72 index c2_idx of table `test`.`t1` trx id 1339 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 5; hex 7465737438; asc test8;; 1: len 4; hex 00000001; asc ;; RECORD LOCKS space id 23 page no 4 n bits 72 index c2_idx of table `test`.`t1` trx id 1339 lock mode S Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 5; hex 7465737438; asc test8;; 1: len 4; hex 00000001; asc ;; Suggested fix: The lock should be held only during the time the key is being checked, not for the entire transaction. I tested with different isolations levels and the situation is the same (REPEATABLE READ and READ COMMITTED)