inno db 隔离级别和锁定 [英] inno db isolation levels and locking
问题描述
我正在阅读有关 innodb 事务的手册，但仍然有很多不清楚的地方.例如，我不太了解以下行为:
I am reading a manual about innodb transactions but still, there is lots of unclear stuff to me. For instance, I don't quite understand to the following behaviour:
-- 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，行为也是相同的.谁能描述一下这个例子背后的锁定背景(锁定的位置和原因)?
Now, the last update command (in the client 2) waits. I would expect the command to execute because I would suppose only the row 1 is locked. The behaviour is the same even if the second command in the client 2 is insert. Could anyone describe the locking background behind this example (where and why the locks)?
推荐答案
InnoDB 设置特定类型的锁如下.
InnoDB sets specific types of locks as follows.
SELECT ... FROM 是一致读取，读取数据库的快照并且不设置锁，除非事务隔离级别设置为 SERIALIZABLE.对于 SERIALIZABLE 级别，搜索在它遇到的索引记录上设置共享 next-key 锁.
SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters.
SELECT ... FROM ... LOCK IN SHARE MODE 在搜索遇到的所有索引记录上设置共享 next-key 锁.
SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters.
对于搜索遇到的索引记录，SELECT ... FROM ... FOR UPDATE 阻止其他会话执行 SELECT ... FROM ... LOCK IN SHARE MODE 或读取某些事务隔离级别.一致读取将忽略对读取视图中存在的记录设置的任何锁定.
For index records the search encounters, SELECT ... FROM ... FOR UPDATE blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads will ignore any locks set on the records that exist in the read view.
UPDATE ... WHERE ... 为搜索遇到的每条记录设置一个独占的 next-key 锁.
UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
DELETE FROM ... WHERE ... 为搜索遇到的每条记录设置一个独占的 next-key 锁.
DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
INSERT 在插入的行上设置排他锁.这个锁是索引记录锁，不是next-key锁(即没有间隙锁)，并且不会阻止其他会话在插入行之前插入间隙.
INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.
InnoDB 有几种类型的记录级锁:
InnoDB has several types of record-level locks:
记录锁:这是对索引记录的锁.
Record lock: This is a lock on an index record.
间隙锁:这是对索引记录之间间隙的锁，或者是对第一条索引记录之前或最后一条索引记录之后的间隙的锁.
Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
Next-key lock:这是对索引记录的记录锁和对索引记录之前的间隙的间隙锁的组合.
Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.
查看更多:
这篇关于inno db 隔离级别和锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
