MySQL InnoDB Lock Modes for impatients

InnoDB Lock Modes

InnoDB implements standard row-level locking where there are two types of locks, shared (S) locks and exclusive (X) 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

Additionally, InnoDB supports multiple granularity locking which permits coexistence of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used.

 There are two types of intention locks used in InnoDB (assume that transaction T has requested a lock of the indicated type on table t):

• Intention shared (IS): Transaction T intends to set S locks on individual rows in table t.
• Intention exclusive (IX): Transaction T intends to set X locks on those rows.

The intention locking protocol is as follows:

• Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t.
• Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

These rules can be conveniently summarized by means of the following lock type compatibility 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