在 MySQL 中，为什么在使用未提交读时从 select 设置变量会获取锁？

我们在 MySQL 中有一个使用 InnoDB 的表，并且我们使用未提交读的事务隔离级别。为什么设置@x如图所示获取锁？

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

mysql>

尝试从另一个提示更新此表会导致超时错误：

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

就其价值而言，这种锁定不仅限于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
...

正如您记录的错误中所讨论的，Bug #67452 使用未提交读时，从 select 设置变量会获取锁 http://bugs.mysql.com/bug.php?id=67452，这种行为可能是设计使然。它似乎属于同一类别SELECT其结果用于修改数据的语句，如所描述的这些情况：

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

当在构造中使用 SELECT 时REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB 对表中的行设置共享下一键锁s.

下一键锁的原因是为了使SELECT结果更稳定。也就是说，我们不希望匹配的行SELECT在使用它们时进行更改UPDATE或其他数据修改语句。

即使 tx_isolation 是REPEATABLE-READ，这很重要，因为 InnoDB 不支持REPEATABLE-READ for SELECT当语句作为任何类型的一部分执行时UPDATE.

回复您的评论：

无论文档如何，都会发生以下情况：

当你做简单的事时SELECT语句中，InnoDB 不会锁定任何东西，在任何事务隔离中除外SERIALIZABLE.

如果你做一个SELECT ... LOCK IN SHARE MODE or SELECT ... FOR UPDATE，当然会锁定。

但当你这样做时SELECT作为数据修改语句的一部分，例如INSERT INTO...SELECT或者在一个子查询中UPDATE或者正如您在SET @variable := (SELECT...)，它使用共享锁来确保更新过程中数据不会更改。

文档可能不完整。最好测试一下。