mysql - LOCK IN SHARE MODE 是否与 COUNT 一起使用

Question

是

SELECT COUNT(*) FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

锁定行的方法与

相同

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Answer 1

锁定共享模式的文档指定:

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

这将基于事务读取的行。这两个查询通常会读取相同的行。

但是，如果 parent(name) 上有索引，则查询不等价。 count(*) 查询只需使用索引即可解决此问题，而 select * 则需要读取数据页。我假设 MySQL 会在仅索引查询期间锁定底层行，但我不是 100% 确定。