爱玩MySQL之InnoDB Locking

原文： 14.7.1 InnoDB Locking

总结

表级锁

LOCK TABLES … WRITE
SELECT … LOCK IN SHARE MODE sets an IS lock,
and SELECT … FOR UPDATE sets an IX lock.
An AUTO-INC lock is a special table-level lock.

生活中的间隙锁

A/B/C三人在银行排队，这时D也来办业务，为了防止D插队，A/B/C三人把两两间的间隙都锁住，这样D只能排到最后，这就是生活中的间隙锁。
A/B/C对应MySQL中的一行行记录。

MySQL中的间隙锁

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止price=3的记录通过update变成price=5）
（3）间隙锁，对于不存在的行的修改和删除是不阻塞的，只阻塞插入操作

innodb自动使用间隙锁的条件：
（1）必须在RR级别下
（2）检索条件必须有非唯一索引
- 没有索引的话，mysql锁全表，其他事务不能任何增删改操作
- 如果索引是唯一索引，则降级为行锁，对于where price=3这样的查询条件，只锁这一条记录，不影响其他记录。

Shared and Exclusive Locks

A shared (S) lock permits the transaction that holds the lock to read a row.
An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

Intention Locks

InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks. For example, a statement such as LOCK TABLES … WRITE takes an exclusive lock (an X lock) on the specified table. To make locking at multiple granularity levels practical, InnoDB uses intention locks. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. There are two types of intention locks:

An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.

An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

InnoDB支持多粒度锁，允许行锁和表锁共存。例如，一个语句，如LOCK TABLES…WRITE接受指定表上的一个排它锁(X锁)。为了实现多粒度级别的锁，InnoDB使用了意图锁。意图锁是表级别的锁，它指示事务稍后需要表中的一行使用哪种类型的锁(共享或排他)。有两种类型的意图锁:
意图共享锁(IS)表示事务意图在表中的各个行上设置共享锁。
意图排它锁(IX)表示事务打算在表中的各个行上设置一个排它锁。

The intention locking protocol is as follows:
Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
事务在获取表中某一行的共享锁之前，必须首先获取表上的IS锁或更强的锁。
Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
事务在获取表中某一行上的排它锁之前，必须首先获取表上的IX锁。

Record Locks

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.
记录锁总是锁定索引记录，即使表没有定义索引。对于这种情况，InnoDB会创建一个隐藏的聚集索引，并使用这个索引进行记录锁定。

Gap Locks

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.
间隙锁是索引记录之间的间隙上的锁，或者第一个索引记录之前或最后一个索引记录之后的间隙上的锁。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 For UPDATE;防止其他事务将值15插入到列t.c1中，不管列中是否已经有这样的值，因为范围中所有现有值之间的间隙都被锁定。

A gap might span a single index value, multiple index values, or even be empty.
间隙可以跨越单个索引值、多个索引值，甚至为空。

SELECT * FROM child WHERE id = 100;

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.
如果id没有被索引或具有非唯一索引，则该语句将锁定前面的间隙。
如果id没用索引，则锁表

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.
这里还值得注意的是，冲突锁可以由不同的事务在间隙上持有。例如，事务A可以持有一个间隙上的共享间隙锁(间隙s锁)，而事务B持有同一个间隙上的排他间隙锁(间隙x锁)。允许存在冲突的间隙锁的原因是，如果从索引中清除了一个记录，则必须合并由不同事务持有的记录上的间隙锁。

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.
InnoDB中的间隙锁是“纯粹抑制的”，这意味着它们的唯一目的是防止其他事务插入到间隙中。间隙锁可以共存。一个事务使用间隙锁并不会阻止另一个事务使用同一间隙上的间隙锁。共享和排他间隙锁之间没有区别。它们彼此之间不冲突，并且执行相同的功能。

Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows.
默认情况下，InnoDB运行在REPEATABLE READ事务隔离级别。在这种情况下，InnoDB使用next-key锁来进行搜索和索引扫描，这可以防止幻像行。

Insert Intention Locks

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.
插入意图锁是一种由insert操作在行插入之前设置的间隙锁类型。这个锁表明了插入的意图，如果插入到同一个索引间隙中的多个事务没有插入到间隙中的相同位置，那么它们就不需要互相等待。假设有值为4和7的索引记录。尝试分别插入值为5和6的独立事务，在获得插入行的排他锁之前，每个事务使用插入意图锁锁定4和7之间的间隙，但不会相互阻塞，因为行不冲突。

AUTO-INC Locks

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
AUTO-INC锁是一种特殊的表级锁，由插入到带有AUTO_INCREMENT列的表中的事务获取。在最简单的情况下，如果一个事务向表中插入值，那么任何其他事务都必须等待自己向表中插入值，以便由第一个事务插入的行接收到连续的主键值。

The innodb_autoinc_lock_mode variable controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.
innodb_autoinc_lock_mode变量控制用于自动增量锁定的算法。它允许您选择如何在可预测的自动增量值序列和插入操作的最大并发性之间进行权衡。

The index is not required to be a PRIMARY KEY or UNIQUE, but to avoid duplicate values in the AUTO_INCREMENT column, those index types are recommended.
索引不需要是PRIMARY KEY或UNIQUE，但是为了避免AUTO_INCREMENT列中的重复值，推荐使用这些索引类型。

• “Lost” auto-increment values and sequence gaps
  In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”.
  在所有的锁模式(0,1和2)中，如果一个生成了自动增量值的事务回滚，那么这些自动增量值就会“丢失”。
• Specifying NULL or 0 for the AUTO_INCREMENT column
  In all lock modes (0, 1, and 2), if a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value was not specified and generates a new value for it.
  在所有的锁模式下(0、1、2)，如果用户在INSERT语句中指定AUTO_INCREMENT列为NULL或0,InnoDB会将该行视为未指定的值，并为其生成一个新值。

Predicate Locks for Spatial Indexes

暂不整理。

Phantom Rows

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking.
幻行更确切，只是用next-key来解决幻行的插入。如果禁用了gap或隔离级别为RC，则会出现幻行问题。

REPEATABLE READ

Consistent reads within the same transaction read the snapshot established by the first read.
This 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.
同一事务内的一致读取读取第一次读取所建立的快照。
这意味着，如果在同一个事务中发出几个普通(非锁定)SELECT语句，这些SELECT语句彼此之间也保持一致。

Consistent reads

Consistent reads within the same transaction read the snapshot established by the first read
同一事务内的一致读取读取第一次读取所建立的快照

locking reads

- 对于唯一索引的唯一查询，多次相同查询，结果不会变。
- 对于其他查询，通过间隙锁和next-key锁阻塞其他操作，来实现相同查询的结果相同。

https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html