Unlock MySQL Performance: 8 Lock Types, Deadlock Solutions, and Optimization Tips

Lock Fundamentals: The Backbone of Concurrency Control

When multiple transactions operate on the same data, issues such as dirty reads, non‑repeatable reads, and phantom reads can occur. Locks ensure transaction isolation (the "I" in ACID) by preventing these problems.

Lock Classification Overview

1. By Granularity

Table lock

Page lock

Row lock

2. By Mode

Common lock types include:

Shared lock (S) : allows concurrent reads (e.g., SELECT ... LOCK IN SHARE MODE)

Exclusive lock (X) : blocks other reads and writes (e.g., UPDATE/DELETE/INSERT)

Intention shared lock (IS) : table‑level marker before acquiring row‑level shared locks

Intention exclusive lock (IX) : table‑level marker before acquiring row‑level exclusive locks

Row‑Level Locks: The Core of High Concurrency

1. Record Lock

Locks the index record:

-- Transaction A
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- X‑lock on id=1

-- Transaction B (blocked)
UPDATE users SET name = 'Tom' WHERE id = 1;

2. Gap Lock

Locks a range of index values to prevent phantom reads. Example with ids 1,5,10:

BEGIN;
SELECT * FROM users WHERE id BETWEEN 5 AND 10 FOR UPDATE; -- blocks inserts in [5,10]
INSERT INTO users(id) VALUES(6); -- blocked
INSERT INTO users(id) VALUES(11); -- succeeds

3. Next‑Key Lock

Combines record and gap locks under Repeatable Read isolation:

BEGIN;
SELECT * FROM users WHERE id > 5 FOR UPDATE; -- locks matching rows and the gap
UPDATE users SET name='A' WHERE id = 10; -- blocked by record lock
INSERT INTO users(id) VALUES(6); -- blocked by gap lock

Table‑Level Locks: Protecting Whole‑Table Operations

1. Explicit Table Lock

LOCK TABLES users WRITE; -- acquire write lock
-- perform updates
UNLOCK TABLES;

2. Implicit Lock (DDL)

ALTER TABLE users ADD COLUMN age INT; -- automatically acquires an exclusive table lock

3. Metadata Lock (MDL)

-- Session A
BEGIN;
SELECT * FROM users; -- acquires MDL read lock

-- Session B (blocked)
ALTER TABLE users ADD COLUMN email VARCHAR(255);

Deadlocks: The Ultimate High‑Concurrency Challenge

1. Classic Deadlock Scenario

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B (reverse order)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

2. Detection and Resolution

Automatic detection via InnoDB status:

SHOW ENGINE INNODB STATUS; -- check LATEST DETECTED DEADLOCK

Manual handling example (Spring retry):

@Retryable(maxAttempts = 3, backoff = @Backoff(delay = 100))
@Transactional
public void transferMoney(Long from, Long to, BigDecimal amount) {
    // transfer logic
}

Lock Monitoring and Practical Optimization

1. Analyzing Lock Waits

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

2. Index Optimization to Avoid Table Locks

-- Problematic query (no index, causes table lock)
UPDATE users SET status = 1 WHERE name LIKE 'A%';

-- Create index to enable row‑level locking
ALTER TABLE users ADD INDEX idx_name(name);
UPDATE users SET status = 1 WHERE name LIKE 'A%';

3. Lock Timeout Configuration

# my.cnf
[mysqld]
innodb_lock_wait_timeout = 50  # default 50 seconds

Isolation Level Impact on Locks

Different isolation levels affect lock behavior:

Read Uncommitted: possible dirty reads, no locks

Read Committed: prevents dirty reads, uses statement‑level snapshots

Repeatable Read (default): prevents dirty and non‑repeatable reads, uses next‑key locks for phantom protection

Serializable: highest isolation, often results in full‑table locks

InnoDB resolves phantom reads at the Repeatable Read level using Next‑Key Locks.

Best Practices for Lock Management

1. Optimization Mnemonic

Fast : keep transactions short

Small : minimize lock granularity

Avoid : large transactions, full‑table scans, long waits

2. Choosing the Right Lock for the Scenario

Precise single‑row update → row‑level X lock (primary key)

Range update → Next‑Key Lock (Repeatable Read)

Full‑table update → batch commits during low‑traffic periods

Schema change → PT‑Online‑Schema‑Change tool

Conclusion

Locks protect data consistency but can reduce concurrency.

Granularity matters: row > page > table.

Select appropriate isolation level (RR is recommended for most cases).

Indexes are the key: ~80% of lock issues are solved by proper indexing.

Monitoring is essential: use SHOW ENGINE INNODB STATUS to inspect lock information.

"The best locking strategy is no locking at all." – Michael Stonebraker