mysql - 当表被读/写锁定时，如何读取一行？

Question

我在 MySQL 5.6.13 上运行这些查询。我使用可重复读取隔离级别。该表如下所示:

在 session A 终端中，我发出了以下声明

UPDATE manufacurer
SET lead_time = 2
WHERE mname = 'Hayleys';

在 session B 终端中，我尝试将 ACL Cables 的值 lead_time 更新为 2。但是由于之前来自 session A 的 UPDATE 命令尚未提交(并且 session A 对制造商表具有独占锁)，此更新等待。这个我能理解。

但是当我尝试如下在 session B 上执行 SELECT 语句时，

SELECT * FROM manufacturer
WHERE mcode = 'ACL';

它正确地查询了制造商表并给出了行。这怎么会发生？因为 session A 仍然持有 manufacturer 表的独占锁，据我所知，当表上持有独占锁时，在提交前一个事务之前，其他事务不能读取或写入它。

Answer 1

在此页面上找到以下信息

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

交易特征范围

You can set transaction characteristics globally, for the current session, or for the next transaction:

With the GLOBAL keyword, the statement applies globally for all subsequent sessions. Existing sessions are unaffected.

With the SESSION keyword, the statement applies to all subsequent transactions performed within the current session.

Without any SESSION or GLOBAL keyword, the statement applies to the next (not started) transaction performed within the current session.

有没有考虑到这一点？

可重复读

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

在这篇文章中它的描述非常好。

http://www.mysqlperformanceblog.com/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

It is important to remember that InnoDB actually locks index entries, not rows. During the execution of a statement InnoDB must lock every entry in the index that it traverses to find the rows it is modifying. It must do this to prevent deadlocks and maintain the isolation level.

表格是否有良好的索引？您可以运行 SHOW ENGINE innodb STATUS 来确认锁已被持有吗？