SELECT ... FOR UPDATE and MAX()

Question

在这个查询中使用SELECT ... FOR UPDATE会锁定表中的行吗？

表格位于InnoDB中，查询位于事务内部。

select max(id) from table1 FOR UPDATE

我有这段代码，似乎 SELECT ... FOR UPDATE 没有锁定行。

- user006779

---

你为什么认为它不会锁定它们？ - Tudor Constantin

那个结果是什么？也许你解释错了。 - Tudor Constantin

Answer 1

SELECT MAX(id) FROM table1 FOR UPDATE - 不会锁定任何行，因为它实际上并未扫描任何行。所需的信息是从表索引中检索出来的。

如果在查询前加入 DESCRIBE，则会给出以下提示：

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Select tables optimized away'

SELECT id FROM table1 WHERE id = (SELECT MAX(id) FROM table1) FOR UPDATE - 这将锁定与 MAX(id) 匹配的一行，因为您明确地检索了该行。

SELECT id FROM table1 ORDER BY id DESC LIMIT 1 FOR UPDATE - 这将锁定所有行并使用ORDER BY和LIMIT 1的组合检索MAX(id)。

这将再次通过一个DESCRIBE查询进行解释。

- dezlov

---

当id被索引时，为确保正确性，MySQL必须进行锁定（可能需要仅对该记录保持锁定）。 - jberryman

MySQL文档指出，“选择表优化”表示“在查询执行期间没有必要读取任何表”，至少对于5.5、5.6、5.7版本： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html https://dev.mysql.com/doc/refman/5.6/en/explain-output.html https://dev.mysql.com/doc/refman/5.7/en/explain-output.html - dezlov

Answer 2

也许这个可以行吗？

select id from table where id IN (select max(id) from table1) FOR UPDATE

- Kevin Burton

Answer 3

我也遇到了这个问题并测试了这种情况。最初的回答：

我也遇到了这个问题并进行了测试。

CREATE TABLE `test1` (
  `user_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `ds_id` mediumint(8) unsigned NOT NULL,
  `producer_id` mediumint(8) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`,`ds_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `test1` VALUES
(3, 4, 2),
(17, 1, 1),
(18, 1, 9);

线程 1：

begin;
select max(ds_id) from test1 where user_id=3 for update;
+------------+
| max(ds_id) |
+------------+
|          3 |
+------------+

第二个线程：

begin;
select max(ds_id) from test1 where user_id=3 for update;
... waiting ...

线程1：

insert into test1 set user_id=3, ds_id=4, producer_id=1;
commit;

第二个线程：

+------------+
| max(ds_id) |
+------------+
|          3 |
+------------+

使用分组函数时，InnoDB似乎不会锁定间隙，只会锁定现有记录。如果没有分组函数，线程2将按预期返回2行，包括全新的一行。

原始答案：Original Answer

- Victor Mironov

Answer 4

SELECT ... FOR UPDATE会阻塞其他会话执行SELECT ... LOCK IN SHARE MODE

建议使用SELECT ... LOCK IN SHARE MODE，它会对被读取的行设置共享模式锁，这样其他会话就可以读取这些行但不能修改它们。读取到的行是最新的可用数据，如果这些行属于另一个尚未提交的事务，则读取操作会阻塞直到该事务结束。

更多信息请参见文档。

- CristiC

Answer 5

就像在Oracle文档中所述，此子句受以下限制：

您不能将此子句与以下其他结构一起使用：DISTINCT运算符、CURSOR表达式、集合运算符、group_by_clause或聚合函数。

- Lakshitha Udara