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)

[3 Dec 2013 5:18] Yoseph Phillips
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.
[3 Dec 2013 17:26] Sveta Smirnova
Thank you for the report.

Verified as described. Problem is not repeatable with 5.1
[3 Dec 2013 17:35] Sveta Smirnova
Workaround with --innodb_locks_unsafe_for_binlog=1 does not work for me.
[3 Dec 2013 17:37] Sveta Smirnova
test case for MTR

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

[3 Dec 2013 17:37] Sveta Smirnova
option file for the test

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

[9 Dec 2013 3:00] Yoseph Phillips
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.