在MySQL中，为什么在使用未提交的读取时通过select设置变量会获得锁定? [英] In MySQL Why does setting a variable from a select acquire a lock when using read uncommitted?

问题描述

我们在MySQL中有一个使用InnoDB的表，并且正在使用未提交的事务隔离级别.为什么按所示设置 @x 会获得锁?

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

推荐答案

对于它的价值，该锁定不限于 READ-UNCOMMITTED :

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

如您所记录的错误中所述，错误#67452通过选择设置变量使用未提交读时锁定，这种行为可能是设计使然.它似乎与 SELECT 语句属于同一类别，该语句的结果用于修改数据，就像下面描述的这些情况一样:

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

在构造中使用SELECT时或 UPDATE t ... WHERE col IN(SELECT ... FROM s...)，InnoDB在表 s 的行上设置共享的下一键锁.

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.

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

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.

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

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.

发表您的评论

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

Regardless of the documentation, here's what happens:

当您执行普通的 SELECT 语句时，除了 SERIALIZABLE 以外，InnoDB不会在任何事务隔离中锁定任何内容.

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

如果您执行 SELECT ...共享模式锁定或 SELECT ... FOR UPDATE ，它当然会锁定.

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

但是，当您将 SELECT 作为数据修改语句(如 INSERT INTO ... SELECT )的一部分或在 UPDATE 或您在 SET @variable:=(SELECT ...)中发现的，它使用共享锁来确保在更新过程中数据不会更改.

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.

这篇关于在MySQL中，为什么在使用未提交的读取时通过select设置变量会获得锁定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！