Bug #67452 Setting a variable from a select acquires a lock when using read uncommitted
Submitted: 2 Nov 2012 6:18 Modified: 1 Mar 2023 8:42
Reporter: Yoseph Phillips Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.5.24+ OS:Any (Ubuntu and Windows XP)
Assigned to: CPU Architecture:Any
Tags: lock, read uncommitted

[2 Nov 2012 6:18] Yoseph Phillips
When using read uncommitted 'set @x = (select userID from users limit 1);' is acquiring a lock. This is causing deadlocks all over the place.

I would expect this not to acquire any locks as dirty reads are permitted, and I can not find any documentation saying that this would acquire a lock.

I haves tested this on Linux: MySQL 5.5.24-0ubuntu0.12.04,
and on Windows XP: MySQL 5.6+

without the set @x no lock is acquired, as expected.
I have also asked about this here: http://stackoverflow.com/questions/13151837

How to repeat:
Create an InnoDB table called users with a userID column and at least one row.

In one transaction set session transaction isolation level to read uncommitted.
(Note on 5.5.24-0ubuntu0.12.04 global is not changing the isolation level, hence why I used session)
Then call: set @x = (select userID from users limit 1);

In another transaction call: set session transaction isolation level to read uncommitted.
Then call: update users set userID = 1;

Actual: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Expected: Query OK, 0 rows affected (0.00 sec)
[3 Nov 2012 20:09] Valeriy Kravchuk
Manual, http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html, says about somewhat similar case:

"When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s."

and isolation level is not mentioned in this context, so looks like this happens for READ UNCOMMITTED as well. SET @var=(SELECT ... FROM s ...) probably is considered as a form of update. 

I also wonder what is the value of innodb_locks_unsafe_for_binlog in your case...

See http://bugs.mysql.com/bug.php?id=46947 also.
[4 Nov 2012 0:40] Yoseph Phillips
http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html, says 'The transaction isolation level also can affect which locks are set; see Section 13.3.6, “SET TRANSACTION Syntax”. '

http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html says 'READ UNCOMMITTED

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a “dirty read.” Otherwise, this isolation level works like READ COMMITTED. '

All the values using 5.5.24-0ubuntu0.12.04 are the defaults: 
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
| Variable_name                  | Value |
| innodb_locks_unsafe_for_binlog | OFF   |
1 row in set (0.00 sec)

It looks like http://bugs.mysql.com/bug.php?id=46947 was already closed as fixed in earlier versions, this bug is in 5.5.24 and 5.6.
[4 Nov 2012 16:59] Davi Arnaut
A SET statement is a SQL command in itself, which InnoDB does not recognize as equivalent to a SELECT command for locking purposes -- probably because no one thought about handling or optimizing for this corner case. Use 'SELECT ... INTO var' instead (e.g. select userID into @x from users limit 1).
[6 Nov 2012 4:53] Yoseph Phillips
The problem with the 'into' workaround is: 'If the query returns no rows, a warning with error code 1329 occurs (No data)' (SET does not return a warning for this). 
I have switched some places to use Cursors instead.
Our stored procedures have been written by lots of people, and it would take a long time to go through changing each of them in this way. (Where this problem is often causing deadlocks, I am taking the time to rewrite just those stored procedures).
[9 Nov 2012 14:54] MySQL Verification Team
A decision on when and how to fix this bug will be made later, during next year.
[8 Jan 2014 8:45] Erlend Dahl
Setting to "verified" since we will discontinue the use of "to be fixed later".
[25 Aug 2022 5:00] MySQL Verification Team
1. After discussion with dev team we found that setting the variable in the
   set @x = (select userID from users limit 1);
   is not a sql standard .
2. The set query used in this fashion is not considered a select query,
   therefore we take a shared lock on the row.
3. The correct way to set the variable to be considered as a select query is
    "select userID into @x from users limit 1." which works on all isolation
4. So it was decided that not to support the set query as mentioned in the
bug report .

5. It is probably prudent to raise a new bug for the problem with the workaround. ie not to raise a warning in case of the select query returns with no result.