inno db 隔离级别和锁定

Question

我正在阅读一本关于 innodb 事务的手册，但仍然有很多不清楚的东西。例如，我不太理解以下行为：

-- client 1                             -- client 2
mysql> create table simple (col int) 
       engine=innodb; 

mysql> insert into simple values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into simple values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select @@tx_isolation;                                                              
+-----------------+                                                                         
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |                                                                         
+-----------------+

mysql> begin;                                    
Query OK, 0 rows affected (0.01 sec)            
                                        mysql> begin;
                                        Query OK, 0 rows affected (0.00 sec)

mysql> update simple set col=10 where col=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

                                         mysql> update simple set col=42 where col=2;
                                         -- blocks

现在，最后一个更新命令（在客户端 2 中）等待。我希望该命令能够执行，因为我假设只有第 1 行被锁定。即使客户端 2 中的第二个命令是 insert，行为也是相同的。谁能描述一下这个例子背后的锁定背景（锁的位置和原因）？

Answer 1

InnoDB 设置特定类型的锁如下。

• SELECT ... FROM 是一致读，读取数据库的快照并且不设置锁，除非事务隔离级别设置为 SERIALIZABLE。对于 SERIALIZABLE 级别，搜索在遇到的索引记录上设置共享的下一个键锁。

• SELECT ... FROM ... LOCK IN SHARE MODE 对搜索遇到的所有索引记录设置共享下一个键锁。

• 对于搜索遇到的索引记录，SELECT ... FROM ... FOR UPDATE 会阻止其他会话执行 SELECT ... FROM ... LOCK IN SHARE MODE 或在某些事务隔离级别中进行读取。一致读取将忽略读取视图中存在的记录上设置的任何锁定。

• UPDATE ... WHERE ... 在搜索遇到的每个记录上设置独占的下一键锁定。

• DELETE FROM ... WHERE ... 在搜索遇到的每个记录上设置独占的下一键锁定。

• INSERT 在插入的行上设置排他锁。该锁是索引记录锁，而不是下一个键锁（即没有间隙锁），并且不会阻止其他会话插入到插入行之前的间隙中。

InnoDB 有几种类型的记录级锁：

• 记录锁：这是对索引记录的锁。

• 间隙锁：这是对索引记录之间的间隙的锁定，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁定。

• 下一个键锁：这是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。

查看更多：

使用下一键锁定避免幻象问题

避免死锁

Answer 2

ypercube 说得对。具体来说，如果条件中没有使用唯一索引，它将锁定不止受影响的单行。

要查看您期望的行为，请将表创建更改为：

create table simple (col int unique) ENGINE=InnoDB;

col 字段上的唯一索引将允许它仅锁定受影响的行。

Answer 3

“对于搜索遇到的索引记录，SELECT ... FROM ... FOR UPDATE 会阻止其他会话执行 SELECT ... FROM ... LOCK IN SHARE MODE 或在某些事务隔离级别中读取。一致性读取将忽略任何锁定设置在读取视图中存在的记录上”

可以通过 select for update 应用哪些特定锁定，以便其他会话无法读取锁定的记录？

Answer 4

UPDATE ... WHERE ... 对搜索遇到的每个记录设置独占的下一键锁定。但是，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁。
所以在这种情况下，你期望一个索引记录锁，但是，你的索引不是唯一索引，所以你的语句将被添加一个独占的next-key锁。