MySQL Bugs: #71055: Using IF EXISTS(SELECT * ...) acquires a lock when using read uncommitted

Bug #71055	Using IF EXISTS(SELECT * ...) acquires a lock when using read uncommitted
Submitted:	3 Dec 2013 5:18	Modified:	9 Dec 2013 3:00
Reporter:	Yoseph Phillips	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.5.12, 5.5.35, 5.6.16	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Tags:	lock, read uncommitted, regression
Triage:	Needs Triage: D3 (Medium)

Description:
When using IF EXISTS(SELECT * users WHERE userID = 1) and read uncommitted locks are getting acquired.

Using read uncommitted and trying to UPDATE users WHERE userID = 1 in another thread is waiting for the first transaction to be committed.

This type of behaviour might be expected when using read committed or other transaction isolation levels, but this seems like an error that it happens for read uncommitted.

Using SELECT * users WHERE userID = 1 by itself is not acquiring any locks, it is only when this is used in the subquery to IF EXISTS that this problem is happening.

As a workaround using innodb_locks_unsafe_for_binlog = 1, prevents this lock from getting acquired.

The doc says innodb_locks_unsafe_for_binlog is deprecated in 5.6.3. Does that mean that innodb_locks_unsafe_for_binlog should not be used from 5.6.3? Or that it can't be used any more in which case is it always enabled or disabled form 5.6.3 onwards.

With innodb_locks_unsafe_for_binlog = 0, and read uncommitted why is IF EXISTS(SELECT * users WHERE userID = 1) acquiring a lock?

How to repeat:
Create a users table with rows including one with userID = 1.
In one read-uncommitted transaction call a stored procedure containing:
 IF EXISTS(SELECT * users WHERE userID = 1) THEN
-- do something
END IF;
In another read-uncommitted transaction call UPDATE users SET userID = 2 WHERE userID = 1.

On our systems this hangs waiting for the 1st transaction to be committed or rolled back, causing massive performance issues.

Suggested fix:
Stop IF EXISTS(SELECT * users WHERE userID = 1) from acquiring a lock when using read uncommitted transactions.

Thank you for the report.

Verified as described. Problem is not repeatable with 5.1

Workaround with --innodb_locks_unsafe_for_binlog=1 does not work for me.

test case for MTR

Attachment: bug71055.test (application/octet-stream, text), 455 bytes.

option file for the test

Attachment: bug71055-master.opt (application/octet-stream, text), 108 bytes.

Just confirming what Sveta has written: the innodb_locks_unsafe_for_binlog workaround it not working for us either. (I had found that from a different post for dealing with read uncommitted bugs). Currently I have switched the tables causing the biggest performance problems from InnoDB to MyISAM, however we cannot do this for all of our tables, so this is still a big problem for us for many of our tables, so we still do not have a viable workaround.