SELECT…ORDER BY XXX LIMIT 1 FOR UPDATE将锁定多少行？

| 我有以下结构的查询：

SELECT ..... WHERE status = \'QUEUED\' ORDER BY position ASC LIMIT 1 FOR UPDATE;

这是InnoDB表上的单表SELECT语句。字段“ 1”（INT NOT NULL）上具有索引。状态为ENUM，并且也已建立索引。 SELECT ... FOR UPDATE手册页说，它锁定了读取的所有行。我是否正确理解，在这种情况下，只有一行会被锁定？或更确切地说，它将锁定整个表？是否有可能确定使用“ 3”查询将锁定哪些行？如果是，怎么办？对空表的查询解释如下：

1;\'SIMPLE\';\'job\';\'index\';<null>;\'index_position\';[34,...];<null>;1;\'Using where\'

已邀请:

4 个回复

醒荒捆府绣

这是一个很好的问题。 InnoDB是行级锁定引擎，但是必须设置其他锁定以确保二进制日志（用于复制；时间点恢复）的安全性。要开始对其进行解释，请考虑以下（朴素的）示例：

session1> START TRANSACTION;
session1> DELETE FROM users WHERE is_deleted = 1; # 1 row matches (user_id 10), deleted.
session2> START TRANSACTION;
session2> UPDATE users SET is_deleted = 1 WHERE user_id = 5; # 1 row matches.
session2> COMMIT;
session1> COMMIT;

由于语句仅在提交后才写入二进制日志，因此将在从属会话2上首先应用该语句，并将产生不同的结果，从而导致数据损坏。因此，InnoDB要做的是设置其他锁。如果对“ 6”进行了索引，那么在session1提交之前，其他任何人都无法修改或插入到“ 7”的记录范围内。如果在ѭ6上没有索引，则InnoDB需要锁定整个表中的每一行，以确保重播的顺序相同。您可以将其视为锁定间隙，这与直接从行级锁定掌握的概念不同。对于这种“ 9”的情况，InnoDB需要确保不能在最低键值和最低可能值“特殊”之间修改任何新行。如果您做得很好，例如ORDER BY position DESC..，那么没人可以插入该范围。解决方案如下：基于语句的二进制日志很烂。我真的很期待未来，我们都可以切换到基于行的二进制日志记录（可从MySQL 5.1获得，但默认情况下未启用）。使用基于行的复制时，如果将隔离级别更改为“已提交读”，则仅需要锁定匹配的一行。如果要成为受虐狂，还可以使用基于语句的复制来打开innodb_locks_unsafe_for_binlog。 4月22日更新：要复制并粘贴我改进后的测试用例版本（它不是在“空白处”搜索）：

session1> CREATE TABLE test (id int not null primary key auto_increment, data1 int, data2 int, INDEX(data1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

session1> INSERT INTO test VALUES (NULL, 1, 2), (NULL, 2, 1), (5, 2, 2), (6, 3, 3), (3, 3, 4), (4, 4, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

session1> start transaction;
Query OK, 0 rows affected (0.00 sec)

session1> SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

session2> INSERT INTO test values (NULL, 0, 99); # blocks - 0 is in the gap between the lowest value found (1) and the \"special\" lowest value.

# At the same time, from information_schema:

localhost information_schema> select * from innodb_locks\\G
*************************** 1. row ***************************
    lock_id: 151A1C:1735:4:2
lock_trx_id: 151A1C
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `so5694658`.`test`
 lock_index: `data1`
 lock_space: 1735
  lock_page: 4
   lock_rec: 2
  lock_data: 1, 1
*************************** 2. row ***************************
    lock_id: 151A1A:1735:4:2
lock_trx_id: 151A1A
  lock_mode: X
  lock_type: RECORD
 lock_table: `so5694658`.`test`
 lock_index: `data1`
 lock_space: 1735
  lock_page: 4
   lock_rec: 2
  lock_data: 1, 1
2 rows in set (0.00 sec)

# Another example:
select * from test where id < 1 for update; # blocks

骨乏唯瓜

我已经做了测试。创建了下表：

id  data1   data2
1   1   2
2   2   1
5   2   2
6   3   3
3   3   4
4   4   3

然后我创建了与事务的第一个连接：

SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE;

结果是id = 1的行；然后，我从另一个连接创建了第二个事务，而没有先提交：

SELECT id FROM test WHERE data1=2 FOR UPDATE;

它没有阻止。并且仅当我尝试选择第一个事务选择的行时，它才阻塞。我尝试了以下将ORDER BY更改为DESC的方法，它也可以工作。结论：MySQL在使用ORDER BY和LIMIT子句时仅阻止其实际选择的行。有关间隙锁定的说明，请参见@Morgan答案。我的MySQL版本是5.0.45

凸晴

在某些版本的MySQL中有一个错误：＃67745将SELECT用于UPDATE，LIMIT和ORDER BY时，行锁过多。版本：5.5.28、5.5.30、5.7.1 我的本地mysql 5.5.25 win64上的相同错误。

脖呐

与其他数据库不同，在MySQL中，查询将锁定索引位置。这实际上意味着当前具有等于ѭ16的ѭ15或希望从另一个事务更改为ѭ16的所有行都被锁定。我发现的唯一解决方案是选择不带FOR UPDATE的行，然后使用基于ID的过滤器选择它们，并在锁定后重新检查条件。不好，但是确实可以。

要回复问题请先登录或注册

SELECT…ORDER BY XXX LIMIT 1 FOR UPDATE将锁定多少行？

4 个回复

发起人

rowlocking

mysql

transactions

问题状态

SELECT…ORDER BY XXX LIMIT 1 FOR UPDATE将锁定多少行？

与内容相关的链接

4 个回复

发起人

rowlocking

mysql

transactions

问题状态