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 位置 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_locksG
*************************** 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
相关文章