Locks and Deadlocks in MySQL

What Is a Lock in InnoDB?

A lock coordinates access to a database resource while a transaction is active. InnoDB usually locks index records and ranges rather than an abstract application row. The exact footprint depends on the statement, indexes, search condition and isolation level.

Locks protect correctness, while short transactions and good indexes preserve concurrency.

Reproducible Lock Laboratory

DROP TABLE IF EXISTS lock_demo;
CREATE TABLE lock_demo (
  resource_id INT PRIMARY KEY,
  resource_name VARCHAR(40) NOT NULL,
  value_no INT NOT NULL
) ENGINE = InnoDB;

INSERT INTO lock_demo VALUES
(1, 'Resource A', 10),
(2, 'Resource B', 20),
(3, 'Resource C', 30);
COMMIT;

Open Session A and Session B. If a statement waits, leave it running and execute the next labelled statement in the other session. Reset values after experiments.

Shared, Exclusive and Range Lock Types

For a unique search on a unique index, InnoDB can lock only the matching record. For range searches it may lock scanned ranges. If no useful index exists, an UPDATE or locking read can scan and lock far more records than expected.

Two-Session Blocking Example

-- Session A
START TRANSACTION;
SELECT resource_id, value_no
FROM lock_demo
WHERE resource_id = 1
FOR UPDATE;
-- Resource 1 is locked for conflicting operations.

-- Session B
START TRANSACTION;
UPDATE lock_demo
SET value_no = value_no + 1
WHERE resource_id = 1;
-- Waits while Session A holds its lock.

-- Session A
UPDATE lock_demo SET value_no = 15 WHERE resource_id = 1;
COMMIT;

-- Session B now continues from the committed row.
COMMIT;

A plain consistent SELECT in Session B may still read a snapshot version; do not confuse that with permission to perform a conflicting update. Use FOR UPDATE NOWAIT when immediate error is preferable to waiting.

SELECT * FROM lock_demo
WHERE resource_id = 1
FOR UPDATE NOWAIT;
-- Error 3572 if the required row lock is unavailable.

Create a Real Deadlock in Two Sessions

A deadlock is a cycle: each transaction holds a lock and waits for another held by the other transaction.

-- Step 1, Session A
START TRANSACTION;
UPDATE lock_demo SET value_no = value_no + 1
WHERE resource_id = 1;

-- Step 2, Session B
START TRANSACTION;
UPDATE lock_demo SET value_no = value_no + 1
WHERE resource_id = 2;

-- Step 3, Session A: waits for Session B.
UPDATE lock_demo SET value_no = value_no + 1
WHERE resource_id = 2;

-- Step 4, Session B: requests Session A's row.
UPDATE lock_demo SET value_no = value_no + 1
WHERE resource_id = 1;

InnoDB detects the cycle and rolls back one victim transaction. Which transaction becomes the victim is an engine decision; do not build logic that assumes Session B always loses. Once the victim is rolled back, the surviving wait can proceed and should be explicitly committed or rolled back.

Correctly Handle Deadlock Error 1213

attempt = 0
while attempt is below retry_limit
    begin a fresh transaction
    try
        acquire rows in a deterministic order
        execute all validated statements
        commit
        return success
    catch deadlock error 1213
        transaction is the victim and has been rolled back
        wait a small randomized backoff
        retry the complete logical transaction
    catch any other error
        rollback if still active
        report or classify the failure

• Retry the entire transaction because its earlier statements were rolled back.
• Use a small maximum attempt count; endless retry hides design problems.
• Re-read data in the fresh transaction instead of reusing stale decisions.
• Make externally visible operations idempotent so a client retry cannot duplicate them.
• Distinguish deadlock from lock wait timeout. Under the default InnoDB setting, a timeout normally rolls back the waiting statement rather than universally ending the full transaction.

Diagnose Locks, Waits and Deadlocks

SHOW ENGINE INNODB STATUS\G

Inspect the LATEST DETECTED DEADLOCK section for transactions, statements and index records involved. The exact output is diagnostic text, not a stable application API.

SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA,
       OBJECT_NAME, INDEX_NAME, LOCK_TYPE,
       LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks;

SELECT *
FROM performance_schema.data_lock_waits;

• Capture the SQL pattern and transaction order, not only a single process ID.
• Confirm that predicates use intended indexes with EXPLAIN.
• Look for long transactions, idle sessions and broad range scans.
• innodb_print_all_deadlocks can log every deadlock while diagnosing frequent incidents; disable extra logging when finished.
• Monitoring current waits is time-sensitive because committed or rolled-back locks disappear.

Minimize Deadlocks Without Hiding Them

1. Access tables and rows in the same deterministic order everywhere.
2. Keep transactions small and commit promptly after related work.
3. Never wait for user input or external network calls while holding locks.
4. Create appropriate indexes for FOR UPDATE, UPDATE and DELETE predicates.
5. Lock only rows that belong to the business unit.
6. Use atomic conditional updates when a read-before-write step is unnecessary.
7. Evaluate READ COMMITTED when its visibility semantics meet the application and it reduces unwanted range locking.
8. Expect occasional deadlocks on a busy correct system and keep bounded retry logic.
9. Do not increase lock-wait timeout as a substitute for fixing a cyclic lock order.

Study concurrency control and indexes with this lesson because lock footprint is tied to access paths.

Official References

• MySQL 8.4: InnoDB Locking
• MySQL 8.4: InnoDB Locking Reads
• MySQL 8.4: Deadlocks in InnoDB

Lock compatibility, deadlock detection and retry guidance were checked against the official MySQL 8.4 manual.