Database Lock Granularity

1. What Are Granularity Locks

• Database locks can be divided by granularity into row locks, page locks, and table locks.

2. Row Locks

2.1. What They Are

Row locks lock data at row granularity.

2.2. Classification

• Further divided by read/write

  • Shared lock: read lock, S (Share) lock. When reading a record, first obtain this lock. Multiple read operations can proceed at the same time, but they block writes.
  • Exclusive lock: write lock, X (Exclusive) lock. When modifying a record, first obtain this lock. Until the current write operation is complete, it blocks other write and read operations.

• Compatibility

  Compatibility	S Lock	X Lock
  S Lock	Compatible	Incompatible
  X Lock	Incompatible	Incompatible

• Usage

  • Shared lock: SELECT ... LOCK IN SHARE MODE
  • Exclusive lock: SELECT ... FOR UPDATE

3. Page Locks

3.1. What They Are

• Page locks lock at page granularity.

4. Table Locks

4.1. What They Are

• Table locks lock a data table.

4.2. Classification

• Further divided by read/write

  • If a transaction places a shared lock on a table, then:
    • Other transactions can continue to obtain an S lock on the table.
    • Other transactions can continue to obtain an S lock on some records in the table.
    • Other transactions cannot continue to obtain an X lock on the table.
    • Other transactions cannot continue to obtain an X lock on some records in the table.
  • If a transaction places an exclusive lock on a table (meaning that the transaction wants exclusive access to the table), then:
    • Other transactions cannot continue to obtain an S lock on the table.
    • Other transactions cannot continue to obtain an S lock on some records in the table.
    • Other transactions cannot continue to obtain an X lock on the table.
    • Other transactions cannot continue to obtain an X lock on some records in the table.

• There is a problem: if transaction A has placed an S lock on a record and transaction B wants to place a table-level X lock, it would first need to determine whether all records have S or X locks. Traversing them would be too inefficient. Therefore, when transaction A adds a record-level S lock, it needs to add an IS lock at the table level, used only to speed up this determination for other transactions.

  • Intention Shared Lock, abbreviated IS lock. When a transaction is preparing to add an S lock to a record, it first needs to add an IS lock at the table level.
  • Intention Exclusive Lock, abbreviated IX lock. When a transaction is preparing to add an X lock to a record, it first needs to add an IX lock at the table level.

• Compatibility

Compatibility	X	IX	S	IS
X	Incompatible	Incompatible	Incompatible	Incompatible
IX	Incompatible	Compatible	Incompatible	Compatible
S	Incompatible	Incompatible	Compatible	Compatible
IS	Incompatible	Compatible	Compatible	Compatible

5. Metadata Locks

5.1. What They Are

A table’s structure belongs to the table’s metadata. A metadata lock is a lock placed on the table structure. It is also used to solve concurrency problems. For example, transaction A selects data and prepares to modify it, then transaction B alters the table structure and commits. Transaction A then updates, but because the table structure has changed, an error occurs.

5.2. Classification

A write lock is added when executing DDL, and a read lock is added when executing DML.

6. Row Locks vs Page Locks vs Table Locks

	Row Lock	Page Lock	Table Lock
Granularity	Small	Medium	Large
Lock Conflicts	High	Medium	Low
Concurrency	High	Medium	Low
Deadlocks	High	Medium	Low

7. Reference

• Understanding MDL Metadata Locks in Depth - SegmentFault