InnoDB隔离级别和锁定

Question

InnoDB隔离级别和锁定

mysqltransactionsinnodbisolation-leveldatabase-locking

9

我正在阅读一本关于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中的第二个命令是插入，行为也是相同的。能否有人描述一下这个例子背后的锁定背景（锁定发生在哪里以及为什么会发生锁定）？

- clime

2

在事务级别中了解**REPEATABLE READ：对于锁定读取（使用FOR UPDATE或LOCK IN SHARE MODE的SELECT）、UPDATE和DELETE语句，锁定取决于语句是否使用具有唯一搜索条件的唯一索引**或范围类型搜索条件。... - ypercubeᵀᴹ

4个回答

1

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

要看到您期望的行为，请将表格创建更改为以下内容：

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

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

- kbenson

据我所了解，需要有一个索引，但它不需要是唯一的。 - clime

啊，当我读到它“使用一个具有唯一搜索条件的唯一索引”时，我把它认为是一个“唯一”的索引，但他们肯定是指单一、特定的唯一。 - kbenson

ypercube所写的并不（完全）相关。如果存在唯一索引和唯一搜索条件，则使用记录锁而不是next-key锁。在我的例子中，无论使用记录锁还是next-key锁都没有关系，因为第一个更新可能锁定的唯一间隙是1之前的间隙，而第二个更新引用2。重要的是有一个索引，因为更新不仅锁定目标记录，而且在搜索目标记录期间遇到的所有记录都会被锁定。没有索引=>全扫描=>所有记录都被锁定。 - clime

我认为这是相关的，只是不够准确。它以概括的方式描绘了你详细描述的内容。感谢更新！ - kbenson

0

对于搜索遇到的索引记录，使用SELECT ... FROM ... FOR UPDATE会阻止其他会话执行SELECT ... FROM ... LOCK IN SHARE MODE或在某些事务隔离级别下的读取。一致性读取将忽略在读取视图中存在的记录上设置的任何锁定。哪些特定锁可以与SELECT ... FOR UPDATE一起应用，以使其他会话无法读取被锁定的记录？

- Ravi Sahu

抱歉，这不是一个答案。请考虑发布一个单独的问题。 - Beryllium

StackOverflow的人不允许我将其作为新问题发布。 - Ravi Sahu

MySQL中没有这样的锁，请参考此答案以获取解决方法。 - elipoultorak

0

UPDATE ... WHERE ... 在搜索遇到的每个记录上设置了一个独占的 next-key 锁。然而，仅对使用唯一索引锁定行以搜索唯一行的语句需要索引记录锁。因此，在这种情况下，您期望获得索引记录锁，但是您的索引不是唯一索引，因此您的语句将被添加一个独占的 next-key 锁。

- tian lan

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Somnath Muluk · Accepted Answer

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

SELECT ... FROM是一致性读取，读取数据库的快照并且不设置锁，除非事务隔离级别设置为SERIALIZABLE。对于SERIALIZABLE级别，搜索会在遇到的索引记录上设置共享next-key锁。
SELECT ... FROM ... LOCK IN SHARE MODE将在搜索遇到的所有索引记录上设置共享next-key锁。
对于搜索遇到的索引记录，SELECT ... FROM ... FOR UPDATE会阻止其他会话执行SELECT ... FROM ... LOCK IN SHARE MODE或在某些事务隔离级别下进行读取。一致性读取将忽略在读取视图中存在的任何记录上设置的锁。
UPDATE ... WHERE ...在搜索遇到的每个记录上设置独占的next-key锁。
DELETE FROM ... WHERE ...在搜索遇到的每个记录上设置独占的next-key锁。
INSERT在插入的行上设置排他锁。这个锁是索引记录锁，而不是next-key锁（也就是说，没有间隙锁），不会阻止其他会话在插入行之前插入间隙。

InnoDB有几种记录级别的锁：

记录锁：这是对索引记录的锁定。
间隙锁：这是对索引记录之间的间隙或第一个或最后一个索引记录之前的间隙的锁定。
next-key锁：这是对索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

请参见更多：

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

避免死锁