Bug #65581 INSERT statement can be delayed by SELECT ... LOCK.

• Submitted: 11 Jun 2012 10:07
• Modified: 11 Jun 2012 10:16
• Reporter: Libing Song
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.5
• OS: Any
• CPU Architecture: Any

[11 Jun 2012 10:07] Libing Song

Description:
INSERT statement can be blocked by a SELECT ... LOCK IN SHARE MODE statement.
cause SELECT .. LOCK statement can lock the gap where INSERT statement want to insert.
and I found, at the same time, the waiting INSERT statement cannot block following SELECT ... LOCK statement.

So the INSERT statement will be delayed for a long time if there are a lot 
SELECT ... LOCK statements following it.

How to repeat:
--source include/have_innodb.inc
--connect(slock_con1, localhost, root,,)
--connect(slock_con2, localhost, root,,)

CREATE TABLE t1(c1 INT KEY);
INSERT INTO t1 VALUES(1), (3), (5), (7);

--echo [ slock_con2 connection]
BEGIN;
SELECT * FROM t1 WHERE c1 < 5 LOCK IN SHARE MODE;

--echo [ default connection]
--connection default
BEGIN;
--echo # INSERT is blocked by GAP lock between 1 and 3
send INSERT INTO t1 VALUES(2);	

--echo [ slock_con1 connection ]
--connection slock_con1
BEGIN;
--echo # INSERT INTENTION doesn't block a GAP lock
SELECT * FROM t1 WHERE c1 < 5 LOCK IN SHARE MODE;

--echo [ slock_con2 connection ]
--connection slock_con2
COMMIT;
BEGIN;
--echo # INSERT INTENTION doesn't block a GAP lock
SELECT * FROM t1 WHERE c1 < 5 LOCK IN SHARE MODE;

--echo [ slock_con1 connection ]
--connection slock_con1
COMMIT;

--echo [ default connection]
--connection default
--reap

[11 Jun 2012 10:16] Valeriy Kravchuk

Verified with 5.5.26 on Mac OS X:

...
main.bug65581                            [ fail ]
        Test ended at 2012-06-11 13:11:43

CURRENT_TEST: main.bug65581
mysqltest: At line 37: query 'reap' failed: 1205: Lock wait timeout exceeded; try restarting transaction

The result from queries just before the failure was:
< snip >
# INSERT is blocked by GAP lock between 1 and 3
INSERT INTO t1 VALUES(2);
[ slock_con1 connection ]
BEGIN;
# INSERT INTENTION doesn't block a GAP lock
SELECT * FROM t1 WHERE c1 < 5 LOCK IN SHARE MODE;
c1
1
3
[ slock_con2 connection ]
COMMIT;
BEGIN;
# INSERT INTENTION doesn't block a GAP lock
SELECT * FROM t1 WHERE c1 < 5 LOCK IN SHARE MODE;
c1
1
3
[ slock_con1 connection ]
COMMIT;
[ default connection]

More results from queries before failure can be found in /Users/openxs/dbs/5.5/mysql-test/var/log/bug65581.log
...