Global Lock

Generally used when doing full database backup. It blocks write operations and only allows read requests. Therefore, it’s usually chosen during business low-peak periods for full database backup.

1

flush tables with read lock

Table-Level Locks

Table Lock

1. Table shared read lock. Multiple threads can acquire this lock simultaneously. Threads with this lock can read the table but cannot write to it. MyISAM acquires table shared read locks when processing read requests. InnoDB defaults to row locks and handles read requests through MVCC.
2. Table exclusive write lock. Only one thread can acquire this lock and perform write operations on the table. No other threads can read this table.

1

lock tables t_student read;

Table locks are triggered by: ALTER/DROP/TRUNCATE TABLE

Intention Locks

Intention locks are table-level locks, and their main purpose is to avoid full table scans. (🌟 operations demonstrate this) In InnoDB, reading a record uses the lock-free MVCC mechanism, which doesn’t add intention locks to the table.

Intention locks don’t conflict with row-level locks, and intention locks don’t conflict with each other.

Intention Shared Lock (IS)

1

SELECT... LOCK IN SHARE MODE

When we read a row using this operation, it adds a row-level read lock to that row, and the database also adds a table-level intention shared lock to the table.

1. When another thread tries to add a write lock to a specific row, it checks the index to see if the row has a read lock. If not, the write lock can be acquired; if the row already has a read lock, the write lock cannot be added.
2. When another thread tries to add a write lock to the entire table, it discovers the table already has an intention shared lock, so the write operation cannot be completed. 🌟
3. When another thread tries to add a read lock to a specific row/entire table, there’s no conflict.

Intention Exclusive Lock (IX)

1

select ... for update;

This operation adds an intention exclusive lock to the table and adds a write lock to the specific row.

1. Another thread attempting a row-level exclusive lock checks the index. It can lock a different row, but must wait if the target row is already exclusively locked.
2. Another thread attempting a table-level exclusive lock conflicts with the intention exclusive lock and must wait.
3. Another thread attempting a row-level shared lock conflicts only when it targets the same row.
4. Another thread attempting a table-level shared lock is allowed.

Row Locks

Row locks apply to indexed records.

Record Lock

A shared or exclusive lock on a specific index record.

Gap Lock

Locks a gap between index records. InnoDB uses gap locks under Repeatable Read to prevent inserts that would create phantom rows.

Gap locks can increase the risk of deadlocks.

Next-Key Lock

Combines a record lock with a gap lock, usually covering a left-open, right-closed interval. It prevents phantom inserts while locking the matching record.

Interview Tips

1. UPDATE without WHERE: InnoDB takes a table intention exclusive lock plus exclusive locks on all affected rows. MyISAM takes a table-level exclusive lock.
2. UPDATE with WHERE: InnoDB locks matching rows and may add gap locks under Repeatable Read. Without a usable index, it may scan and lock many rows.