This section describes lock types used by InnoDB.
本章节讲解InnoDB引擎中的锁类型

Shared and Exclusive Locks 共享锁和排他锁

InnoDB实现了两类标准的行级锁，分别为shared (S) 锁和 exclusive (X)锁；
shared (S) 锁：获取到该行共享锁的事务可以读取该行.
exclusive (X)锁：获取到该行排它锁的事务可以更新或删除该行.

如果事务T1在r行上获取了啊一个共享锁，那么事务T2请求获取r行的锁的处理情况如下：

• 如果T2请求的是一个共享锁，那么立刻给予。

• 如果T2请求的是一个排它锁，那么不能立刻给予；

如果事务T2在r行上持有一个排它锁，那么事务T2无论请求什么锁都不能被立即给予。T2必须等待T1释放T1所持有的排它锁。

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.

For example, SELECT … FOR SHARE sets an IS lock, and SELECT … FOR UPDATE sets an IX lock.

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.

Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

Table-level lock type compatibility is summarized in the following matrix.

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible
A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.

Intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

Transaction data for an intention lock appears similar to the following in SHOW ENGINE INNODB STATUS and InnoDB monitor output:

TABLE LOCK table test.t trx id 10080 lock mode IX

Record Locks

Gap Locks

Next-Key Locks

Insert Intention Locks

AUTO-INC Locks

Predicate Locks for Spatial Indexes