MySQL InnoDB Locking: Deadlocks, Gap Locks, and How to Avoid Them

Home » MySQL » MySQL InnoDB Locking: Deadlocks, Gap Locks, and How to Avoid Them
October 20, 2025 MySQL
MySQL InnoDB Locking: Deadlocks, Gap Locks, and How to Avoid Them

InnoDB’s row-level locking is what makes WordPress’s MySQL backend safe for concurrent writes, but it also introduces deadlocks and long-running lock waits that show up as Lock wait timeout exceeded errors in busy WooCommerce stores. Understanding the difference between record locks, gap locks, and next-key locks — and knowing when each is acquired — is the key to writing queries that avoid contention.

Problem: A WooCommerce site under high order volume experiences intermittent database deadlocks — MySQL's slow query log shows LOCK WAIT timeout exceeded errors, and it is unclear which queries are conflicting.

Solution: Enable InnoDB deadlock logging with innodb_print_all_deadlocks = ON. Inspect SHOW ENGINE INNODB STATUS to see the latest deadlock detail. Common WordPress/WooCommerce causes: concurrent UPDATE wp_options on autoloaded transients, and row-level locks held across multiple table updates in the same transaction. Fix by reducing transaction scope and using SELECT ... FOR SHARE instead of FOR UPDATE where appropriate.


The examples below show how to diagnose a deadlock from the error log, reproduce a classic gap-lock deadlock, fix it by switching to a less-strict isolation level or redesigning the query, and use SHOW ENGINE INNODB STATUS to identify the blocking transaction.


-- 1. Show the most recent deadlock (always check this first)
SHOW ENGINE INNODB STATUS\G
-- Look for: LATEST DETECTED DEADLOCK section
-- It shows the two transactions, the locks held, and the lock waited for.

-- 2. Show currently waiting locks (MySQL 8.0+)
SELECT
    r.trx_id           AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query        AS waiting_query,
    b.trx_id           AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query        AS blocking_query
FROM information_schema.innodb_lock_waits  w
JOIN information_schema.innodb_trx         b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx         r ON r.trx_id = w.requesting_trx_id;

-- 3. Classic gap-lock scenario on wp_options
-- Session A:
START TRANSACTION;
SELECT option_value FROM wp_options WHERE option_name = 'my_lock_key' FOR UPDATE;
-- This acquires a next-key lock on the gap around 'my_lock_key'

-- Session B (runs concurrently):
START TRANSACTION;
INSERT INTO wp_options (option_name, option_value, autoload)
VALUES ('my_lock_key_2', 'value', 'no');
-- Session B waits — blocked by Session A's gap lock

-- 4. Fix option 1: Use READ COMMITTED isolation (no gap locks)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 5. Fix option 2: Use INSERT ... ON DUPLICATE KEY UPDATE instead of SELECT FOR UPDATE
INSERT INTO wp_options (option_name, option_value, autoload)
VALUES ('my_lock_key', 'new_value', 'no')
ON DUPLICATE KEY UPDATE option_value = VALUES(option_value);

-- 6. Enable deadlock logging (add to my.cnf)
-- innodb_print_all_deadlocks = ON    -- logs every deadlock to error log
-- innodb_lock_wait_timeout = 10      -- fail fast instead of waiting 50s (default)


NOTE: WordPress's update_option() uses a SELECT + INSERT/UPDATE two-step that is vulnerable to gap-lock deadlocks under high concurrency — if you need atomic option updates in concurrent requests (e.g., incrementing a counter), use a raw INSERT ... ON DUPLICATE KEY UPDATE option_value = option_value + 1 query instead.

Related posts