How to Diagnose and Fix MySQL Deadlocks Without Just Restarting the Service

Problem Background

MySQL deadlocks are a common pain point in production. Many operators restart the database when a deadlock error appears, which only clears the immediate blockage while the root cause remains and can re‑appear later.

Deadlock Fundamentals

What Is a Deadlock

A deadlock occurs when two or more transactions hold locks the other needs, forming a circular wait that prevents any progress.

InnoDB Lock Types

Shared lock (S) : allows a transaction to read a row.

Exclusive lock (X) : allows a transaction to update or delete a row.

Record lock : locks an index record.

Gap lock : locks the gap between index records to prevent phantom reads.

Next‑Key lock : combination of record lock and gap lock.

Intention lock : table‑level lock indicating a row‑level lock will be taken.

Lock Wait vs. Deadlock

Lock wait : transaction A waits for B to release a lock; times out after innodb_lock_wait_timeout (default 50 s). This is a one‑way wait, not a deadlock.

Deadlock : transaction A waits for B while B simultaneously waits for A. MySQL detects the cycle and rolls back one transaction (usually the one holding fewer row locks).

Investigation Tools

Step 1 – Enable Deadlock Logging

# my.cnf / my.ini
[mysqld]
innodb_print_all_deadlocks = ON
innodb_lock_wait_timeout = 50
innodb_deadlock_detect = ON

For MySQL 8.0 the settings can be changed dynamically:

SET GLOBAL innodb_print_all_deadlocks = ON;
SET GLOBAL innodb_lock_wait_timeout = 50;

Step 2 – View Deadlock Logs

grep -A 50 "TRANSACTION" /var/log/mysql/error.log | grep -A 30 "DEADLOCK"

Step 3 – Query Performance Schema (MySQL 5.7+)

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

Practical Case 1 – Row‑Lock Conflict

Symptom

Concurrent inserts into orders sometimes fail with:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Investigation Process

Extract the deadlock section from the error log.

Identify that both transactions hold an X lock on different rows of orders and wait for each other's lock, indicating they attempted to insert the same primary‑key value.

Check the table structure and recent rows to confirm duplicate primary‑key or auto‑increment collisions.

Root Cause

Two concurrent transactions insert orders for the same user_id. Because a unique index on user_id creates a Next‑Key lock under REPEATABLE‑READ, each transaction waits for the other's lock.

Remediation

Solution 1 – Application‑level distributed lock (recommended)

import redis, uuid

def create_order(user_id, product_id, amount):
    lock_key = f"order:lock:{user_id}"
    lock_val = str(uuid.uuid4())
    if not redis.set(lock_key, lock_val, nx=True, ex=5):
        raise Exception("Too many concurrent orders, please retry")
    try:
        with db_connection.cursor() as cur:
            sql = "INSERT INTO orders (user_id, product_id, amount, status) VALUES (%s, %s, %s, 'pending')"
            cur.execute(sql, (user_id, product_id, amount))
            db_connection.commit()
            return cur.lastrowid
    finally:
        if redis.get(lock_key) == lock_val:
            redis.delete(lock_key)

Solution 2 – Add a unique constraint as a safety net

ALTER TABLE orders ADD CONSTRAINT uk_user_product UNIQUE (user_id, product_id);
INSERT INTO orders (user_id, product_id, amount, status)
VALUES (1001, 2001, 99.00, 'pending')
ON DUPLICATE KEY UPDATE amount = VALUES(amount), status = VALUES(status);

Solution 3 – Lower isolation level to READ‑COMMITTED

SET SESSION transaction_isolation = 'READ-COMMITTED';
# or globally
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

Solution 4 – Align SQL execution order across transactions

-- Bad order (causes deadlock)
-- Transaction A: UPDATE orders …; UPDATE users …
-- Transaction B: UPDATE users …; UPDATE orders …

-- Good order (consistent)
UPDATE users SET balance = balance-100 WHERE id=100;
UPDATE orders SET status='paid' WHERE id=1;

Verification

# Continuously tail the error log to ensure no new deadlocks
tail -f /var/log/mysql/error.log | grep DEADLOCK

# Check for duplicate rows
SELECT user_id, product_id, COUNT(*) cnt FROM orders GROUP BY user_id, product_id HAVING cnt > 1;

Practical Case 2 – Gap‑Lock Deadlock (Stock Deduction)

Symptom

Three concurrent

UPDATE products SET stock = stock - 1 WHERE id = 1001 AND stock > 0

statements on a product with only one item cause two deadlocks.

Root Cause

Under REPEATABLE‑READ, the WHERE stock > 0 range query acquires a gap lock. Multiple transactions read the same gap and then try to acquire the X lock on the row, leading to a circular wait.

Remediation

Solution 1 – Optimistic lock (version column)

UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1001 AND stock >= 1 AND version = 1;

Solution 2 – SELECT … FOR UPDATE before the update

START TRANSACTION;
SELECT stock INTO @cur_stock FROM products WHERE id = 1001 FOR UPDATE;
IF @cur_stock >= 1 THEN
    UPDATE products SET stock = stock - 1 WHERE id = 1001;
    COMMIT;
ELSE
    ROLLBACK;
END IF;

Solution 3 – Pre‑reserve table

CREATE TABLE stock_reserve (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    status ENUM('pending','confirmed','cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product_status (product_id, status),
    UNIQUE KEY uk_reserve (product_id, id)
);

INSERT INTO stock_reserve (product_id, quantity) VALUES (1001, 1);
UPDATE stock_reserve sr JOIN products p
SET sr.status='confirmed', p.stock = p.stock - sr.quantity
WHERE sr.id = ? AND sr.product_id = p.id AND sr.status='pending';

Verification

# Verify deadlock frequency drops
SELECT TIME, COUNT(*) deadlock_count FROM mysql.general_log
WHERE command_type='Query' AND argument LIKE '%Deadlock%'
GROUP BY TIME ORDER BY TIME DESC LIMIT 20;

Practical Case 3 – Unique‑Index Conflict

Symptom

Concurrent INSERTs into users with the same username and email cause deadlocks.

Root Cause

Both transactions request an exclusive lock on the same unique index entry; the second transaction waits, forming a deadlock that MySQL resolves by rolling back one of them.

Remediation

Solution 1 – Application‑level existence check

def register_user(username, email, password):
    existing = db.query("SELECT id FROM users WHERE username=%s OR email=%s", (username, email)).fetchone()
    if existing:
        raise ValueError("Username or email already registered")
    db.execute("INSERT INTO users (username, email, password_hash) VALUES (%s, %s, %s)",
               (username, email, hash_password(password)))
    db.commit()

Solution 2 – INSERT IGNORE or ON DUPLICATE KEY

INSERT IGNORE INTO users (username, email, password_hash) VALUES ('john_doe','[email protected]','hashed_pw');
-- or
INSERT INTO users (username, email, password_hash) VALUES (...)
ON DUPLICATE KEY UPDATE email=VALUES(email), password_hash=VALUES(password_hash);

Solution 3 – REPLACE (DELETE + INSERT)

REPLACE INTO users (username, email, password_hash) VALUES ('john_doe','[email protected]','hashed_pw');

Solution 4 – Distributed lock around the insert

def register_user_with_lock(username, email, password):
    lock_key = f"user:register:{username}"
    lock_val = str(uuid.uuid4())
    if not redis.set(lock_key, lock_val, nx=True, ex=5):
        raise Exception("Registration in progress, please retry later")
    try:
        if db.query("SELECT 1 FROM users WHERE username=%s", (username,)).fetchone():
            raise ValueError("Username already exists")
        db.execute("INSERT INTO users (username, email, password_hash) VALUES (%s,%s,%s)",
                   (username, email, hash_password(password)))
        db.commit()
    finally:
        if redis.get(lock_key) == lock_val:
            redis.delete(lock_key)

Core Commands Cheat Sheet

# List all transactions and held locks
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_weight,
       trx_mysql_thread_id, trx_query, trx_rows_locked, trx_rows_modified
FROM information_schema.INNODB_TRX;

# InnoDB lock information (5.7)
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

# InnoDB lock information (8.0)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

# Find blocking SQL
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id,
       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 w.blocking_trx_id = b.trx_id
JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id;

# Deadlock statistics
SHOW GLOBAL STATUS LIKE 'Innodb_deadlock%';

Prevention Measures

SQL‑Level

Access rows in a fixed order (e.g., always lock by primary key ascending) to avoid circular waits.

Keep transactions short; avoid long‑running network I/O or heavy computation inside a transaction.

Use proper indexes; missing indexes cause full‑table scans and larger lock ranges.

Prefer primary‑key or unique‑key lookups; range scans acquire Next‑Key locks.

Configuration Level

[mysqld]
innodb_lock_wait_timeout = 10   # keep timeout short
innodb_deadlock_detect = ON    # keep detection enabled
innodb_print_all_deadlocks = ON
performance-schema-instrument = 'lock%=ON'
transaction-isolation = REPEATABLE-READ   # or READ-COMMITTED for high concurrency
innodb_table_locks = ON

Architecture Level

Use a message queue to serialize high‑contention operations such as stock deduction.

Apply distributed locks (Redis, ZooKeeper) before critical DB writes.

Separate reads to replica servers (read‑write splitting) to reduce lock contention on the primary.

Shard hot tables by user or order ID to spread lock hotspots across multiple databases.

Common Misconceptions

Deadlocks are not merely a MySQL problem; they stem from business logic and transaction design.

Increasing innodb_lock_wait_timeout does not reduce deadlocks; it only delays the error.

Switching from REPEATABLE‑READ to READ‑COMMITTED reduces Next‑Key lock range but does not eliminate all deadlocks.

Large transactions increase lock holding time and are more prone to deadlocks; break them into smaller units.

Unique constraints are a safety net, not a replacement for proper concurrency control.

Conclusion

Effective MySQL deadlock handling hinges on three pillars:

Read the deadlock log. With innodb_print_all_deadlocks=ON, the log shows the locks each transaction holds ( HOLDS THE LOCK(S)) and the locks it waits for ( WAITING FOR THIS LOCK TO BE GRANTED), allowing you to pinpoint row‑lock, gap‑lock, or unique‑index conflicts.

Understand InnoDB lock mechanics. Next‑Key locks under REPEATABLE‑READ lock index ranges, which explains why range queries are more deadlock‑prone than point queries.

Optimize both SQL and application flow. Adjust queries, add appropriate indexes, use distributed locks or message queues, and consider isolation‑level changes. The goal is not to eradicate deadlocks completely—an inevitable side‑effect of high concurrency—but to keep their frequency low and ensure the application can gracefully retry or handle them.

Never rely on a database restart as a fix; it merely masks the underlying issue. Instead, follow the systematic analysis and remediation steps outlined above to achieve stable, high‑throughput MySQL operations.