gpt4 book ai didi

mysql - SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE 将锁定多少行?

转载 作者:IT老高 更新时间:2023-10-28 23:42:36 26 4
gpt4 key购买 nike

我有一个结构如下的查询:

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

这是对 InnoDB 表的单表 SELECT 语句。字段 position (INT NOT NULL) 上有一个索引。 status 是 ENUM 并且也被索引。

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

是否可以通过 EXPLAIN 查询确定哪些行将被锁定?如果是 - 如何?对空表的查询说明如下:

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

最佳答案

这是一个很好的问题。 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;

因为语句只在提交后才写入二进制日志,所以从属 session #2 将首先应用,并会产生不同的结果,导致数据损坏

所以 InnoDB 所做的就是设置额外的锁。如果 is_deleted 已编入索引,则在 session1 提交之前,其他人将无法修改 或插入 is_deleted=1 的记录范围。如果 is_deleted 上没有索引,那么 InnoDB 需要锁定整个表中的每一行,以确保重播的顺序相同。您可以将其视为锁定间隙这与直接掌握行级锁定的概念不同

在您使用 ORDER BY position ASC 的情况下,InnoDB 需要确保在最低键值和“特殊”最低可能值之间不能修改新行。如果你做了类似 ORDER BY position DESC.. 的事情,那么没有人可以插入这个范围。

那么解决方案来了:

  • 基于语句的二进制日志记录很糟糕。我真的很期待我们都切换到 row based binary logging 的 future (可从 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

关于mysql - SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE 将锁定多少行?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5694658/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com