Mysql:如何在事务中获取S锁

Question

我尝试使用以下 SQL 获取 S 锁:

start transaction;
  SELECT * FROM test.t1 where id=1;

但是我发现这种方式行不通。我将其更改如下:

start transaction;
  SELECT * FROM test.t1 where id=1 lock in share mode;

我想知道为什么第一种方式没有获得 S 锁？

我已经设置了autocommit=0;

Answer 1

On tables, InnoDB normally acquires only intentional shared (LOCK_IS) or intentional exclusive (LOCK_IX) modes. It does not lock the tables in shared mode (LOCK_S) or exclusive mode (LOCK_X) unless explicitly requested via LOCK TABLES command.

如前所述here在“表级事务锁”部分(我假设您的表是 InnoDB)。

至于您关于何时授予共享锁的评论的答案，它在另一行的同一篇文章中:

One exception to this is in the prepare phase of the online alter table command, where the table is locked in shared mode