MySQL Bugs: #35425: Row level lock is preventing new rows being inserted in read-uncommitted mode

Bug #35425	Row level lock is preventing new rows being inserted in read-uncommitted mode
Submitted:	19 Mar 2008 7:45	Modified:	19 Mar 2008 11:11
Reporter:	Rizwan Maredia	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.1.22	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	read_uncommitted, Row level lock, tx_isolation

Description:
In READ-UNCOMMITED mode when some rows are locked for update prevents any other connection to insert rows. This is not the expected behavior and it should allow rows to be inserted into the table as table lock is not used. Interestingly, when the tx_isolation mode is READ-COMMITED insertion is allowed.

How to repeat:
We can test this script in mysql test framework

Note: Create testname-master.opt file with this options
--innodb --innodb_lock_wait_timeout=2

SET SESSION tx_isolation = 'READ-UNCOMMITTED';
CREATE TABLE t1 (a int PRIMARY KEY, b int) ENGINE=INNODB;

INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
INSERT INTO t1 VALUES(4, 4);
INSERT INTO t1 VALUES(6, 6);

SET autocommit = 0;

START TRANSACTION;
SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE;
UPDATE t1 SET b = 10 WHERE a % 2 = 0;

connect (con1,localhost,root,,);
connection con1;
SET SESSION tx_isolation = 'READ-UNCOMMITTED';
SET autocommit = 0;

START TRANSACTION;
SELECT * FROM t1;

--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO t1 VALUES(3, 3);
--error ER_LOCK_WAIT_TIMEOUT
INSERT INTO t1 VALUES(5, 5);

--echo Bug: Only even rows are being locked, error 1205 should'nt have occured

SELECT * FROM t1;
COMMIT;

connection default;
COMMIT;

Suggested fix:
The READ-UNCOMMITED isolation level should allow inserts to table whose rows are locked for update by some other connection.

Thank you for a problem report. Please, send the results of:

show variables like 'innodb_locks%';

The default value of innodb_locks_unsafe_for_binlog is off. When I enable it using option file it allows insertion.

So, I think, this is not a bug. Check http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_locks_u....