MySQL Locks

Publish on: Classify at:
Words: 394 Read:≈ 2mins Views:

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.

Lock TypeTable S Lock (Shared)Table X Lock (Exclusive)Table IS Lock (Intention Shared)Table IX Lock (Intention Exclusive)
Table S Lock (Shared)CompatibleConflictCompatibleCompatible
Table X Lock (Exclusive)ConflictConflictConflictConflict
Table IS Lock (Intention Shared)CompatibleConflictCompatibleCompatible
Table IX Lock (Intention Exclusive)ConflictConflictCompatibleCompatible

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.