Bug #102755 Error 1062 locks unique index and do not release until commit or rollback
Submitted: 27 Feb 17:19 Modified: 6 Apr 12:34
Reporter: Vinicius Malvestio Grippa Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[27 Feb 17:19] Vinicius Malvestio Grippa
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)
[27 Feb 20:03] Vinicius Malvestio Grippa
I forgot to mention that in session 1, I'm setting autocommit=0.

# session 1
set autocommit=0
begin;
insert into t1 (c2) value ('test');
insert into t1 (c2) value ('test');
[3 Mar 16:03] MySQL Verification Team
Hi Mr. Grippa,

Thank you for your bug report.

We have managed to repeat it.

We also find that, although this is not a serious nor critical bug, fixing this behaviour would lead to a smaller number of locks held.

Verified as reported.
[5 Apr 19:37] chen zongzhi
This issue is duplicate of https://bugs.mysql.com/bug.php?id=68021

I agree that this issue should be performance issue, not a bug.

I have add a fix to this problem in issue #68021.

The key is change the unique check lock to record_not_gap lock and change the gap|insert_intention lock to next-key|insert_intention.
[6 Apr 12:34] MySQL Verification Team
Thank you, Mr. Zongzhi.