MySQL Bugs: #67452: Setting a variable from a select acquires a lock when using read uncommitted

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:	None
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

Description:
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)

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.

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.

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).

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).

A decision on when and how to fix this bug will be made later, during next year.

Setting to "verified" since we will discontinue the use of "to be fixed later".

https://bugs.mysql.com/bug.php?id=108273

1. After discussion with dev team we found that setting the variable in the
fashion
   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
    levels.
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.