Victor's room

Overview & Root Cause Summary: The error ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction occurs in MySQL and MariaDB when two or more concurrent transactions form a cyclical lock dependency where each transaction holds an exclusive or shared lock on a row/table that the other transaction needs to proceed. Unlike lock wait timeouts (Error 1205), InnoDB’s deadlock detector identifies this circular dependency immediately, selects the transaction that performed the fewest modifications as a “victim”, forcibly rolls it back with Error 1213, and allows the competing transaction to finish.

Understanding the Root Causes

• Inconsistent Access Sequence Across Concurrent Transactions: Two application threads updating or locking the exact same rows in differing orders (e.g., Thread 1 locks Row A then Row B; Thread 2 locks Row B then Row A).
• Gap Locks in REPEATABLE READ Isolation: Under MySQL’s default REPEATABLE READ isolation level, InnoDB utilizes Next-Key locks (combining record locks and gap locks) on ranges of unindexed or foreign key data, causing overlapping gap locks that escalate into deadlocks.
• Missing Foreign Key Indexes: Cascading updates or deletes on parent tables lock ranges of child tables. Without dedicated foreign key indexes, InnoDB scans and locks wide index gaps.
• Concurrent Bulk UPSERTs (INSERT ... ON DUPLICATE KEY UPDATE): Concurrent workers inserting overlapping sets of unique keys acquiring conflicting shared (S) and exclusive (X) locks on duplicates.

Step 1: Quick Fix (Analyze the Latest Deadlock Graph via InnoDB Status)

Extract the complete deadlock transaction graph from the MySQL engine to identify the exact queries, locks, and thread IDs involved in the collision.

# 1. Connect to MySQL as an administrator:
mysql -u root -p

# 2. View the engine status output:
SHOW ENGINE INNODB STATUS\G

# 3. Locate the "------------------------\nLATEST DETECTED DEADLOCK" section in the output.
# Analyze the following key sections:
# - (1) TRANSACTION: Shows the first query and the lock it was waiting for.
# - (2) TRANSACTION: Shows the second query holding that lock and waiting for another.
# - WE ROLL BACK TRANSACTION (1) or (2): Identifies which transaction was rolled back.

# 4. Enable permanent deadlock logging in MySQL error log (in my.cnf or dynamically):
SET GLOBAL innodb_print_all_deadlocks = ON;

Step 2: Enforce Deterministic Lock Ordering & Index Foreign Keys

Prevent circular lock dependency graphs by ensuring all application write transactions acquire locks in identical, sorted key order.

-- ANTI-PATTERN (Triggers Deadlock):
-- Session 1 updates user 10, then user 20
-- Session 2 updates user 20, then user 10

-- BEST PRACTICE: Sort primary keys in ascending order before updating:
-- In your application ORM or query:
BEGIN;
SELECT * FROM users 
WHERE id IN (10, 20) 
ORDER BY id ASC 
FOR UPDATE;

UPDATE users SET balance = balance - 50 WHERE id = 10;
UPDATE users SET balance = balance + 50 WHERE id = 20;
COMMIT;

-- Ensure all foreign key columns are explicitly indexed:
CREATE INDEX idx_orders_user_id ON orders(user_id);

Step 3: Switch to READ COMMITTED Isolation & Implement Client Retry Logic

Eliminate unnecessary gap locks by adopting the industry-standard READ COMMITTED isolation level and wrapping database transactions in automatic retry loops.

# --- MySQL Configuration: Switch to READ COMMITTED to eliminate gap locks ---
# In /etc/mysql/mysql.conf.d/mysqld.cnf under [mysqld]:
# transaction-isolation = READ-COMMITTED
# binlog_format = ROW

# --- Application Layer (Python / SQLAlchemy Retry Example) ---
import time
from sqlalchemy.exc import OperationalError

def execute_transaction_with_retry(session_maker, max_retries=3):
    for attempt in range(max_retries):
        session = session_maker()
        try:
            # Execute business transaction
            session.commit()
            return
        except OperationalError as err:
            session.rollback()
            # MySQL Error 1213 corresponds to deadlock
            if err.orig.args[0] == 1213 and attempt < max_retries - 1:
                time.sleep(0.05 * (2 ** attempt)) # Exponential backoff
                continue
            raise
        finally:
            session.close()

Verification & Testing Steps

Monitor deadlock occurrence metrics in real time and verify that concurrent transaction simulation runs without unhandled exceptions.

-- 1. Check cumulative deadlock counter in MySQL:
SHOW STATUS LIKE 'Innodb_deadlocks';

-- 2. Inspect active row lock wait activity:
SHOW STATUS LIKE 'Innodb_row_lock_waits';

-- 3. Review MySQL error log for recorded deadlock details:
-- sudo tail -n 50 /var/log/mysql/error.log

Summary Comparison Table

Remediation Strategy	Root Cause Addressed	Implementation Layer	Primary Benefit
Deterministic Ordering	Circular row lock dependency	Application ORM / Queries	Eliminates deadlocks mathematically
READ COMMITTED Isolation	Phantom / Gap locking contention	MySQL Engine Configuration	Removes gap locks entirely for unique reads
Foreign Key Indexing	Full-table lock escalations	Database Schema DDL	Restricts locks strictly to affected rows
Automated Client Retry	Inevitable concurrent collisions	Application Code Wrapper	Makes transient deadlocks invisible to users