Understanding MySQL Locks: Types, Mechanisms, and Deadlock Resolution

Explanation

In MySQL 8.1.0 with the InnoDB storage engine, the article introduces lock concepts using a sample table t and initial data.

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id` int NOT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_b` (`b`) USING BTREE,
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into t values
(0,0,0,0),(5,5,5,5),(10,10,10,10),(15,15,15,15),(20,20,20,20),(25,25,25,25);

MySQL Locks

Locks are categorized by scope: global, table, and row. The focus is on row‑level locks, which are most relevant during development.

Read‑Write Locks

MySQL distinguishes shared (read) and exclusive (write) locks. Different transactions can hold compatible read locks, but any write lock is mutually exclusive with other reads or writes.

Global Locks

A global lock blocks the entire instance. Use FLUSH TABLES WITH READ LOCK to acquire and UNLOCK TABLES (or connection close) to release.

Table Locks

Table‑level locks are obtained with LOCK TABLES … READ/WRITE and released with UNLOCK TABLES or when the session ends.

Metadata Locks (MDL)

MDL is implicit; a read MDL is taken when a table is accessed, and a write MDL when its structure is altered, ensuring read/write correctness.

Intent Locks

Before acquiring row locks, InnoDB automatically sets intent read (IR) or intent write (IX) locks on the table, allowing O(1) checks for existing row locks.

Row Locks

InnoDB supports record locks (REC_NOT_GAP_LOCK), gap locks (GAP_LOCK), and next‑key locks (NEXT‑KEY_LOCK). Record locks protect specific index rows, gap locks protect intervals to prevent inserts, and next‑key locks combine both.

Deadlock

What Is a Deadlock?

A deadlock occurs when two transactions each hold a lock the other needs, causing both to wait indefinitely.

MySQL Deadlock Handling

MySQL can either wait until innodb_lock_wait_timeout expires (default 50 s) or enable automatic deadlock detection with innodb_deadlock_detect=ON, which rolls back one of the conflicting transactions.

Detecting Deadlocks in Production

Search logs for the keyword deadlock, use

SELECT count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="lock_deadlocks"

to count occurrences, or run SHOW ENGINE INNODB STATUS to view the latest deadlock report. Enabling innodb_print_all_deadlocks records all deadlock logs.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-11-08 21:08:08 0x16c56f000
*** (1) TRANSACTION:
TRANSACTION 3466, ACTIVE 341 sec starting index read
... (truncated log) ...
*** WE ROLL BACK TRANSACTION (2)

The log shows two transactions each holding a record write lock on different primary‑key values (0 and 5) and waiting for the other's lock, leading to a deadlock that MySQL resolves by rolling back one transaction.

Practical Recommendations

Delay acquiring locks that are likely to conflict until later in the transaction.

Prefer updates by primary key to avoid extra index look‑ups and broader lock scopes.

Sort batch updates to reduce lock contention.

Maintain a consistent order when updating multiple tables within a transaction.

Be aware that unique‑key insert conflicts acquire a shared (S) lock on the conflicting index record.

Summary

Database locks are essential for managing concurrency, but misuse can cause performance problems and deadlocks. Understanding MySQL's lock hierarchy, compatibility rules, and deadlock detection mechanisms helps developers design safer, more efficient transactions.