SELECT ... ORDER BY XXX LIMIT 1 FOR UPDATE将锁定多少行? [英] How many rows will be locked by SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE?

问题描述

我有一个具有以下结构的查询:

I have a query with the following structure:

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

这是InnoDB表上的单表SELECT语句.字段position(INT NOT NULL)上面有一个索引.状态为ENUM，并且也已建立索引.

It's a single-table SELECT statement on InnoDB table. Field position (INT NOT NULL) has an index on it. status is ENUM and is also indexed.

SELECT ... FOR UPDATE手册页说，它锁定了它读取的所有行.我是否正确理解，在这种情况下，只有一行会被锁定?或更确切地说，它将锁定整个表?

SELECT ... FOR UPDATE manual page says, that it locks all rows it reads. Do I understand correctly, that in this case only one row will be locked? Or rather it will lock the whole table?

是否可以确定哪些行将被EXPLAIN查询锁定?如果是，怎么办?对空表的查询解释如下:

Is that possible to determine which rows will be locked with EXPLAIN query? If yes - how? Explain for a query on the empty table shows the following:

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

推荐答案

这是一个很好的问题. InnoDB是行级锁定引擎，但必须设置其他锁定以确保二进制日志(用于复制；时间点恢复)的安全性.要开始解释它，请考虑下面的示例(原始):

This is a great question. InnoDB is a row level locking engine, but it has to set additional locks to ensure safety with the binary log (used for replication; point in time recovery). To start explaining it, consider the following (naive) example:

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上将首先应用，并且会产生不同的结果，导致数据损坏.

Because statements are only written to the binary log once committed, on the slave session#2 would apply first, and would produce a different result, leading to data corruption.

因此，InnoDB的作用是设置附加锁.如果为is_deleted建立索引，则在session1提交之前，没有其他人可以修改或将其插入到is_deleted=1记录范围内.如果is_deleted上没有索引，则InnoDB需要锁定整个表中的每一行，以确保重播的顺序相同.您可以将其视为锁定间隙，与直接行级锁定要理解的不同概念.

So what InnoDB does, is sets additional locks. If is_deleted is indexed, then before session1 commits nobody else will be able to modify or insert into the range of records where is_deleted=1. If there are no indexes on is_deleted, then InnoDB needs to lock every row in the entire table to make sure the replay is in the same order. You can think of this as locking the gap, which is different concept to grasp from row-level locking directly.

在使用ORDER BY position ASC的情况下，InnoDB需要确保在最低键值和特殊"最低值之间不能修改任何新行.如果您执行了类似ORDER BY position DESC ..这样的操作，那么没有人可以插入该范围.

In your case with that ORDER BY position ASC, InnoDB needs to make sure that no new rows could be modified between the lowest key value and a "special" lowest possible value. If you did something like ORDER BY position DESC.. well, then nobody could insert into this range.

所以解决方案来了:

• 基于语句的二进制日志记录很烂.我真的很期待未来，我们都可以切换到基于行的二进制文件日志记录(可从MySQL 5.1开始，但默认情况下未启用).

• Statement based binary logging sucks. I really look forward to a future where we all switch to row based binary logging (available from MySQL 5.1, but not on by default).

对于基于行的复制，如果将隔离级别更改为已提交读"，则仅需要锁定匹配的一行.

With Row-based replication, if you change the isolation level to read-committed, then only the one row that matches needs to be locked.

如果您想成为受虐狂，也可以打开 innodb_locks_unsafe_for_binlog 和基于语句的复制.

If you want to be a masochist, you can also turn on innodb_locks_unsafe_for_binlog with statement-based replication.

4月22日更新:要复制并粘贴我的测试用例的改进版本(不是在空白处搜索):

Update 22 April: To copy + paste my improved version of your testcase (it was not searching 'in the gap'):

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

这篇关于SELECT ... ORDER BY XXX LIMIT 1 FOR UPDATE将锁定多少行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！