Global Locks

Typically used for full backups. Blocks writes and allows only reads, so usually run during off-peak.

1

flush tables with read lock

Table-Level Locks

Table Locks

1. Shared read lock: multiple threads can read the table concurrently, but cannot write. MyISAM uses a shared read lock for reads; InnoDB prefers row locks and MVCC for reads.
2. Exclusive write lock: only one thread can acquire it; others cannot read or write.

1

lock tables t_student read;

DDL such as ALTER/DROP/TRUNCATE triggers table locks.

Intention Locks

Intention locks are table-level locks to avoid full-table scans when checking for conflicts. InnoDB does NOT set intention locks for MVCC-consistent reads of a single record.

Intention locks do not conflict with row locks, nor with each other.

IS (Intention Shared)

1

SELECT ... LOCK IN SHARE MODE

When reading a row in this mode, a row-level shared lock is set, and the table gets an IS lock.

1. Another thread attempting a row-level X (write) lock checks via the index; if the row isn’t locked, it can lock; if it is, it must wait.
2. Another thread attempting a table-level X lock sees the table already has an IS lock and cannot proceed.
3. Another thread applying S (row/table) locks doesn’t conflict.

IX (Intention Exclusive)

1

select ... for update;

This sets an IX on the table and a row-level X lock on the target row(s).

1. Another thread attempting a row-level X lock checks the index; if the row isn’t X-locked, it can lock; otherwise it waits.
2. Another thread attempting a table-level X lock sees IX and cannot proceed.
3. Another thread attempting a row-level S lock conflicts if it’s the same row; otherwise OK.
4. Another thread attempting a table-level S lock is allowed.

Row Locks

Row locks are per-record.

Record Lock

Row-level S/X locks.

Gap Lock

Locks the gaps between index records (ranges can be open/closed on either side). Only in RR isolation for InnoDB, used to prevent phantom inserts.

May lead to deadlocks.

Next-Key Lock

A combination of record+gap locks; locks a half-open interval (left-open, right-closed). Prevents phantoms while also locking specific rows.

Interview Tips

1. UPDATE without WHERE: InnoDB acquires table IX plus X on all rows to ensure atomicity/consistency; MyISAM takes a table-level X lock that blocks all reads/writes.
2. UPDATE with WHERE: InnoDB locks matching rows with X and adds gap locks in RR to prevent phantoms. If the WHERE uses an index, it locks via the index; without an index, it may escalate to a table lock. MyISAM still takes a table-level X lock regardless.