LOCK IN SHARE MODE锁定整个表 [英] LOCK IN SHARE MODE locks entire table

问题描述

文档:

SELECT ...共享模式锁定在任何已读取的行上设置共享模式锁定.其他会话可以读取这些行，但是在提交事务之前无法对其进行修改.如果这些行中的任何一条被尚未提交的另一个事务更改，则查询将等待该事务结束，然后使用最新值.

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.

但是，一些实验表明，它比读取的行锁定更多.

However, some experimentation suggests that it locks more than the rows that are read.

CREATE TABLE example (a int);
START TRANSACTION;
SELECT a FROM example WHERE a = 0 LOCK IN SHARE MODE;

然后在另一个连接上

INSERT INTO example VALUES (1);

以后的连接会在锁上阻塞.

The later connection blocks on the lock.

LOCK IN SHARE MODE似乎比读取的任何行"都锁定更多.

It would seems that LOCK IN SHARE MODE locks more than "any rows that are read".

LOCK IN SHARE MODE完全锁定了什么 ?

推荐答案

确保在a列上有索引.否则，为了评估WHERE a = 0，它必须读取表中的每一行，然后在读取时在每一行上设置一个锁.

Make sure you have an index on the a column. Otherwise, in order to evaluate WHERE a = 0, it has to read every row in the table, and it will then set a lock on each row as it reads it.

ALTER TABLE example ADD INDEX (a);

这篇关于LOCK IN SHARE MODE锁定整个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！