In MySQL Why does setting a variable from a select acquire a lock when using read uncommitted?

Question

We have a table in MySQL using InnoDB, and we are using a transaction isolation level of read uncommitted. Why does setting @x as shown acquire a lock?

mysql> set @x = (select userID from users limit 1);
Query OK, 0 rows affected (0.02 sec)

mysql>

Trying to update this table from another prompt results in a timeout error:

mysql> update users set userID = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Answer 1

For what it's worth, this locking is not limited to READ-UNCOMMITTED:

mysql1> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
mysql1> BEGIN;
mysql1> SET @x := (SELECT x FROM foo LIMIT 1);

mysql2> UPDATE foo SET x = x+1;
[gets a lock wait]

mysql3> SHOW ENGINE INNODB STATUS;
...
---TRANSACTION 228746, ACTIVE 22 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 58, OS thread handle 0x7fc262a1c700, query id 8163
  192.168.56.1 root cleaning up
TABLE LOCK table `test`.`foo` trx id 228746 lock mode IS
RECORD LOCKS space id 801 page no 3 n bits 80 index `PRIMARY` 
  of table `test`.`foo` trx id 228746 lock mode S
...

As discussed in the bug you logged, Bug #67452 Setting a variable from a select acquires a lock when using read uncommitted, this behavior is probably by design. It seems to fall into the same category as SELECT statements whose results are used to modify data, like these cases described:

http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

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.

The reason for next-key locks is to make the SELECT results more stable. That is, we don't want the rows matched by the SELECT to change while they are being used for an UPDATE or other data-modifying statement.

Even when the tx_isolation is REPEATABLE-READ, this is important because InnoDB doesn't support REPEATABLE-READ for SELECT statements when they're executed as part of any type of UPDATE.

---

Re your comment:

Regardless of the documentation, here's what happens:

When you do plain SELECT statement, InnoDB does not lock anything, in any transaction isolation except SERIALIZABLE.

If you do a SELECT ... LOCK IN SHARE MODE or SELECT ... FOR UPDATE, it locks of course.

But when you do SELECT as part of a data-modifying statement like INSERT INTO...SELECT or in a subquery of an UPDATE or as you found in a SET @variable := (SELECT...), it uses a shared lock to make sure the data doesn't change while the update is in progress.

Documentation can be incomplete. Better to test.

Answer 2

Your first statement executes a SELECT on the table, therefore the transaction acquires a read lock on one row.

The second transaction tries to acquire a write lock on the same table (on all rows, since there is no WHERE clause), but cannot.

You need to issue a COMMIT (or ROLLBACK) command after the SET @x = (...), so that it releases the read lock.

The above is wrong. I keep this post just because the below comments might be of interest.