Feb 17, 2018

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.
XIXSIS
XConflictConflictConflictConflict
IXConflictCompatibleConflictCompatible
SConflictConflictCompatibleCompatible
ISConflictCompatibleCompatibleCompatible