Why Transaction Timeouts and Deadlocks Occur: Master MySQL Row, Table, Gap Locks

Why Transaction Timeouts and Deadlocks Occur

Concurrency can produce dirty reads and phantom reads, which often manifest as transaction timeouts and deadlocks.

Core Lock Categories Overview

Table lock : locks the entire table; large granularity, low concurrency, high safety.

Row lock : locks a single row; fine granularity, high concurrency, higher conflict potential.

Gap lock : locks the empty interval between rows; used to prevent phantom reads.

Key rule : larger lock granularity yields poorer concurrency but greater safety; smaller granularity yields higher concurrency but higher conflict risk.

Table Lock: Simple but Often a Pitfall

Principle : when a table lock is acquired, the whole table is exclusively held, causing any other transaction to block regardless of the row accessed.

Characteristics : minimal overhead and never deadlocks; extremely poor concurrency, severely impacting performance.

MyISAM engine uses table locks by default.

InnoDB degrades to a table lock when there is no index or the index becomes ineffective.

Pitfall : index loss is the core reason why an entire table can become blocked.

Row Lock: The Core of InnoDB High Performance

Principle : only the row being operated on is locked, allowing other rows to be read or written concurrently.

Two Types of Row Locks :

Shared lock (S lock) : multiple transactions can acquire a read lock simultaneously; reads do not conflict.

Exclusive lock (X lock) : write locks are mutually exclusive; once a transaction holds an X lock, all other transactions are blocked.

Preconditions for Row Locks : an effective index is required. Without an index, with an ineffective index, with function‑based expressions, or with fuzzy left‑most matching, row locks degrade to table locks.

Gap Lock: The Ultimate Solution for Phantom Reads

Definition : does not lock specific rows; instead it locks the blank interval between two rows, preventing other transactions from inserting or modifying data in that range, thereby eliminating phantom reads at the source.

Trigger Scenarios : under REPEATABLE READ isolation, range queries or queries that return no rows will trigger gap locks.

Advantages and Disadvantages : completely resolves phantom‑read problems; however, the lock range expands, which can cause unexpected blocking and timeouts.

Deadlock: Causes and Practical Solutions

Essence : two transactions each hold a lock needed by the other, leading to a permanent wait state.

Classic Deadlock Scenario : transaction A locks row 1 then tries to lock row 2; transaction B locks row 2 then tries to lock row 1; both wait indefinitely, resulting in a deadlock.

Production‑Ready Practices to Avoid Deadlocks :

Unified update order : all business updates must follow ascending primary‑key order.

Shorten transaction duration: avoid complex logic and nested queries inside a transaction.

Avoid looping updates on multiple rows.

Rely on timeout‑based automatic lock release to prevent long‑term blocking.

Frequently Encountered Lock Issues and Root Causes

Single‑row update blocks the whole table : caused by index loss, causing row lock to degrade to table lock.

Concurrent updates occasionally timeout : caused by row‑lock contention and overly long transactions.

Mysterious blocking or insert failures : triggered by gap locks.

Deadlock errors in fixed scenarios : result from inconsistent update order.

Quick Mnemonic for Beginners

Table lock : large granularity, poor concurrency; index loss → pitfall.

Row lock : precise, strong performance; effective index is the remedy.

Gap lock : protects empty intervals, fully eliminates phantom reads.

Deadlock : mutual waiting; unified order keeps safety.