Master MySQL Locks: Isolation Levels, Types, and Deadlock Solutions

1. Understanding MySQL Locks

When a database has concurrent transactions, data inconsistency may arise, so a mechanism is needed to guarantee the order of access; locking provides this mechanism.

It is like a hotel room: without a lock, many people could try to occupy the same room; a lock ensures only the holder can use it while others wait.

2. Relationship Between Isolation Levels and Locks

At Read Uncommitted level, reads do not acquire shared locks, avoiding conflicts with exclusive locks on modified data.

At Read Committed level, read operations acquire shared locks, which are released after the statement finishes.

At Repeatable Read level, read operations acquire shared locks that are held until the transaction commits.

SERIALIZABLE is the most restrictive level; it locks the entire key range and holds locks until the transaction completes.

3. Lock Granularity in MySQL and InnoDB Lock Algorithms

Relational databases can have locks of different granularity: row‑level (InnoDB), table‑level (MyISAM), and page‑level (BDB).

MyISAM and InnoDB lock usage

MyISAM uses table‑level locking.

InnoDB supports row‑level and table‑level locking, defaulting to row‑level.

Comparison of row, table, and page locks

Row lock : the finest granularity, locks only the rows involved, reducing conflicts but incurring higher overhead; includes shared and exclusive locks.

Characteristics: high overhead, slower locking, possible deadlocks, minimal lock conflict probability, highest concurrency.

Table lock : locks the whole table, simple to implement, low resource consumption, supported by most MySQL engines; includes shared read lock and exclusive write lock.

Characteristics: low overhead, fast locking, no deadlocks, high conflict probability, lowest concurrency.

Page lock : intermediate granularity, locks a group of adjacent records (a page), balancing speed and conflict.

Characteristics: moderate overhead and lock time, possible deadlocks, concurrency between row and table locks.

4. Lock Types in MySQL

Locks are classified as shared (read) locks and exclusive (write) locks.

Shared lock : also called read lock; multiple shared locks can coexist on the same data.

Exclusive lock : also called write lock; only one exclusive lock can exist, and it conflicts with any other lock.

The lock granularity depends on the storage engine; InnoDB implements row, page, and table locks.

Lock overhead decreases from row to page to table, while concurrency also decreases.

5. How InnoDB Implements Row Locks

InnoDB uses indexes to lock rows.

Example: SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE; The FOR UPDATE clause locks rows based on the indexed condition; if the column is not indexed, InnoDB falls back to a table lock.

6. InnoDB Lock Algorithms

1. Record lock: locks a single row record.

2. Gap lock: locks a range between records, not the records themselves.

3. Next‑key lock: locks a range that includes both the gap and the record (record+gap).

7. Related Points

InnoDB uses next‑key lock for row queries.

Next‑key locking solves the phantom read problem.

When the indexed column is unique, next‑key lock degrades to a record lock.

Gap locks prevent multiple transactions from inserting into the same range, avoiding phantom reads.

Gap locks can be disabled explicitly by setting the transaction isolation level to READ COMMITTED or by setting innodb_locks_unsafe_for_binlog=1.

8. What Is a Deadlock and How to Resolve It?

A deadlock occurs when two or more transactions hold resources each other needs, forming a cycle.

Common deadlock mitigation methods

1. Ensure all programs access multiple tables in the same order.

2. Acquire all needed resources in a single transaction.

3. For high‑contention workloads, consider using a coarser lock granularity such as table locks.

Alternatively, use distributed transaction locks or optimistic locking.

9. Optimistic vs. Pessimistic Locks in Databases

Concurrency control aims to preserve isolation and consistency when multiple transactions access the same data.

Pessimistic lock : assumes conflicts will occur; locks data during the transaction until commit.

Implementation: use the database's lock mechanisms.

Optimistic lock : assumes conflicts are rare; checks for conflicts only at commit, often using a version number or CAS.

Use cases: optimistic locking suits read‑heavy scenarios with few writes; pessimistic locking fits write‑heavy scenarios where conflicts are frequent.