Mastering MySQL Row Locks: Two‑Phase Locking, Deadlocks & Performance Tips

MySQL Row Locks

Row locks are implemented by each storage engine; not all engines support them (e.g., MyISAM lacks row locks, forcing table‑level locking and limiting concurrency). InnoDB supports row locks, which is a key reason for its replacement of MyISAM.

A row lock protects a specific record: if transaction A updates a row, transaction B must wait until A commits before it can modify the same row.

Two‑Phase Locking (2PL)

During a transaction, locks are acquired as needed and held until the transaction commits. This means transaction B’s UPDATE will be blocked until transaction A releases its locks at commit time. This behavior is the essence of the two‑phase locking protocol.

When a transaction needs to lock multiple rows, place the statements that are most likely to cause conflicts toward the end of the transaction to minimize lock holding time and improve concurrency.

Case Study – Online Movie Ticket Purchase

Three operations are required in a single transaction:

Deduct the ticket price from the customer’s account balance.

Add the ticket price to the cinema’s account balance.

Insert a transaction log record.

When two customers purchase tickets simultaneously, the conflict occurs on step 2 because both transactions update the same cinema account row. By ordering the statements as INSERT log, UPDATE customer, UPDATE cinema (i.e., placing the cinema update last), the cinema row lock is held for the shortest possible time, reducing lock wait and increasing throughput.

Deadlock and Deadlock Detection

A deadlock happens when two transactions wait for each other’s row locks, creating a circular wait. In the example, transaction A waits for row id=2 while transaction B waits for row id=1, forming a deadlock.

Deadlock Resolution Strategies

Wait until the lock timeout expires. The timeout is controlled by the innodb_lock_wait_timeout parameter (default 50 seconds).

Enable active deadlock detection by setting innodb_deadlock_detect=ON. InnoDB will periodically scan waiting transactions to find cycles and roll back one transaction to break the deadlock.

Strategy 1 can cause long pauses (up to 50 seconds) for online services, while setting a very short timeout may abort normal lock waits that are not deadlocks. Therefore, the default and recommended approach is strategy 2 with active detection enabled.

Active detection incurs overhead: each blocked transaction must be examined, leading to O(n) complexity per detection cycle. With many concurrent threads updating the same row (e.g., 1,000 threads), CPU usage spikes while the system still processes few transactions per second.

Mitigating Hot‑Row Contention

Temporarily disable deadlock detection if the application can guarantee no deadlocks, accepting the risk of increased timeouts.

Control concurrency so that only a limited number of threads update the same row simultaneously (e.g., max 10).

Implement server‑side queuing or middleware to serialize updates to hot rows.

Redesign the schema: split a single high‑contention row into multiple logical rows (e.g., store a cinema’s balance across ten rows). Updates then target a randomly chosen row, reducing the probability of conflict by roughly 1/10 and lowering deadlock detection cost.

These redesigns must consider business logic, such as handling refunds where rows may become zero and require special treatment.

Summary

When a transaction locks multiple rows, schedule the statements that are most likely to cause conflicts later in the transaction to minimize lock holding time. Adjusting statement order alone does not eliminate deadlocks; therefore, enable InnoDB’s deadlock detection and apply strategies such as concurrency control, row sharding, or server‑side queuing to reduce the impact of hot‑row updates.

References

《MySQL 实战 45 讲》