MySQL & Database Design

Locks & Deadlocks

13 min Lesson 28 of 40

Locks & Deadlocks

Locks are mechanisms that prevent concurrent transactions from interfering with each other. While essential for data integrity, locks can also lead to deadlocks—situations where two or more transactions wait indefinitely for each other to release locks. Understanding locks and deadlocks is crucial for building robust, high-performance database applications.

Understanding Database Locks

When a transaction accesses data, MySQL places locks to ensure consistency. Think of locks like reservation signs on a table at a restaurant—they prevent others from using the resource until you're done.

The Purpose: Locks ensure that when one transaction modifies data, other transactions see either the old value or the new value—never an inconsistent intermediate state.

Types of Locks

MySQL InnoDB uses several types of locks at different granularity levels:

Shared Lock (S): Multiple transactions can read, none can write
Exclusive Lock (X): Only one transaction can read and write
Intention Locks: Table-level locks that indicate row-level locking
Record Locks: Locks on individual index records
Gap Locks: Locks on gaps between index records
Next-Key Locks: Combination of record + gap lock

Table Locks vs Row Locks

Understanding the difference between table and row locks is essential:

-- Table Lock (locks entire table)
LOCK TABLES products WRITE;
UPDATE products SET price = price * 1.1;
UNLOCK TABLES;

-- Row Lock (InnoDB default, locks only affected rows)
START TRANSACTION;
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';
COMMIT;

Best Practice: InnoDB uses row-level locks automatically, which is almost always preferable to table locks because it allows much higher concurrency. Avoid explicit LOCK TABLES unless absolutely necessary.

Shared Locks (Read Locks)

Shared locks allow multiple transactions to read the same data simultaneously:

-- Transaction 1
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR SHARE;
-- Places a shared lock

-- Transaction 2 (in another session)
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR SHARE;
-- This works! Multiple shared locks are compatible

-- Transaction 3 (in another session)
START TRANSACTION;
UPDATE products SET price = 99.99 WHERE product_id = 1;
-- This waits! Cannot get exclusive lock while shared locks exist

Exclusive Locks (Write Locks)

Exclusive locks prevent any other transaction from reading or writing:

-- Transaction 1
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1 FOR UPDATE;
-- Places an exclusive lock

-- Transaction 2 (in another session)
START TRANSACTION;
SELECT * FROM products WHERE product_id = 1;
-- In REPEATABLE READ or lower: returns old data (doesn't wait)
-- In SERIALIZABLE: waits for lock release

-- Transaction 3 (in another session)
START TRANSACTION;
UPDATE products SET price = 99.99 WHERE product_id = 1;
-- This waits! Must wait for Transaction 1 to commit/rollback

Lock Wait Timeout: By default, MySQL waits 50 seconds for a lock. After that, it throws an error: "Lock wait timeout exceeded". You can configure this with innodb_lock_wait_timeout.

Viewing Locked Transactions

MySQL provides tools to monitor locks and blocking transactions:

-- View current locks
SELECT * FROM performance_schema.data_locks;

-- View lock waits
SELECT * FROM performance_schema.data_lock_waits;

-- See which transactions are running
SELECT * FROM information_schema.INNODB_TRX;

-- Kill a blocking transaction (use with caution!)
KILL transaction_id;

What is a Deadlock?

A deadlock occurs when two or more transactions wait for each other to release locks, creating a circular dependency that can never resolve:

-- Classic Deadlock Scenario

-- Transaction 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Holds lock on account 1

-- Transaction 2 (simultaneously in another session)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
-- Holds lock on account 2

-- Transaction 1 continues
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Waits for Transaction 2's lock on account 2

-- Transaction 2 continues
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
-- Waits for Transaction 1's lock on account 1

-- DEADLOCK! Each transaction waits for the other

MySQL's Response: When MySQL detects a deadlock, it automatically chooses one transaction as the "victim" and rolls it back, allowing the other to proceed. The rolled-back transaction receives error 1213: "Deadlock found when trying to get lock".

Detecting Deadlocks

You can view detailed deadlock information:

-- View the most recent deadlock
SHOW ENGINE INNODB STATUS;

-- Look for the "LATEST DETECTED DEADLOCK" section
-- It shows:
-- - The transactions involved
-- - What locks they were waiting for
-- - Which transaction was rolled back

Real-World Deadlock Example

Here's a practical deadlock scenario in an inventory system:

-- User A: Transfer item from warehouse 1 to warehouse 2
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 10 WHERE warehouse_id = 1 AND product_id = 100;
-- Locked: warehouse 1, product 100

-- User B: Transfer item from warehouse 2 to warehouse 1 (simultaneously)
START TRANSACTION;
UPDATE inventory SET quantity = quantity - 5 WHERE warehouse_id = 2 AND product_id = 100;
-- Locked: warehouse 2, product 100

-- User A continues
UPDATE inventory SET quantity = quantity + 10 WHERE warehouse_id = 2 AND product_id = 100;
-- WAITS for User B's lock on warehouse 2

-- User B continues
UPDATE inventory SET quantity = quantity + 5 WHERE warehouse_id = 1 AND product_id = 100;
-- DEADLOCK! MySQL rolls back one transaction

Preventing Deadlocks

Follow these strategies to minimize deadlock occurrence:

1. Access Resources in Same Order:
Always lock tables/rows in the same order across transactions

2. Keep Transactions Short:
Less time holding locks = less chance of deadlock

3. Use Lower Isolation Levels:
READ COMMITTED reduces locking compared to REPEATABLE READ

4. Use Indexes:
Reduce rows scanned/locked by using proper indexes

5. Avoid User Interaction in Transactions:
Don't wait for user input while holding locks

Strategy 1: Consistent Lock Ordering

Always access resources in a predictable order:

-- BAD: Different transactions access accounts in different order
-- Transaction A: locks account 1, then account 2
-- Transaction B: locks account 2, then account 1
-- Result: Possible deadlock

-- GOOD: Always access accounts in ascending ID order
START TRANSACTION;

SET @from_account = 101, @to_account = 202;
SET @first = LEAST(@from_account, @to_account);
SET @second = GREATEST(@from_account, @to_account);

-- Lock in consistent order
SELECT balance FROM accounts WHERE account_id = @first FOR UPDATE;
SELECT balance FROM accounts WHERE account_id = @second FOR UPDATE;

-- Now safely update
UPDATE accounts SET balance = balance - 100 WHERE account_id = @from_account;
UPDATE accounts SET balance = balance + 100 WHERE account_id = @to_account;

COMMIT;

Strategy 2: Use SELECT ... FOR UPDATE

Explicitly lock rows at the start of your transaction:

-- GOOD: Lock all needed rows upfront
START TRANSACTION;

-- Lock all rows we'll need
SELECT * FROM products WHERE product_id IN (1, 5, 10) FOR UPDATE;
SELECT * FROM inventory WHERE product_id IN (1, 5, 10) FOR UPDATE;

-- Now perform updates without risk of deadlock
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;

COMMIT;

Pro Tip: Locking all necessary rows at the beginning (rather than acquiring locks gradually) dramatically reduces deadlock probability.

Handling Deadlocks in Application Code

Since deadlocks are inevitable in high-concurrency systems, implement retry logic:

-- PHP Example
$maxRetries = 3;
$attempt = 0;

while ($attempt < $maxRetries) {
    try {
        // Start transaction
        $pdo->beginTransaction();

        // Perform operations
        $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
        $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

        // Commit
        $pdo->commit();
        break; // Success!

    } catch (PDOException $e) {
        $pdo->rollBack();

        // Check if it's a deadlock error (1213)
        if ($e->getCode() == 40001 || strpos($e->getMessage(), 'Deadlock') !== false) {
            $attempt++;
            usleep(100000); // Wait 100ms before retry

            if ($attempt >= $maxRetries) {
                throw new Exception("Transaction failed after $maxRetries attempts");
            }
        } else {
            throw $e; // Different error, don't retry
        }
    }
}

Lock Wait Timeout Configuration

Configure how long transactions wait for locks:

-- View current timeout (default: 50 seconds)
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Set timeout for current session (in seconds)
SET SESSION innodb_lock_wait_timeout = 10;

-- Set globally (requires privileges)
SET GLOBAL innodb_lock_wait_timeout = 30;

Gap Locks and Next-Key Locks

InnoDB uses special locks to prevent phantom reads:

-- Example: Locking a range
START TRANSACTION;

-- This locks not just existing rows, but also gaps
SELECT * FROM products
WHERE price BETWEEN 100 AND 500
FOR UPDATE;

-- Other transactions cannot insert products with price 100-500
-- INSERT INTO products (price) VALUES (250); -- Would wait!

COMMIT;

Understanding Gap Locks: In REPEATABLE READ isolation level, InnoDB locks not just the records, but also the "gaps" between them to prevent other transactions from inserting new rows that would match your query.

Practice Exercise:

Scenario: Implement deadlock-safe money transfer between bank accounts.

Requirements:

Transfer money from account A to account B
Use consistent lock ordering to prevent deadlocks
Verify sufficient balance before transfer
Handle the case where B < A or A < B

Solution:

START TRANSACTION;

-- Always lock accounts in ascending ID order
SET @from_id = 250, @to_id = 100, @amount = 500.00;

-- Determine lock order
IF @from_id < @to_id THEN
    SELECT balance INTO @from_balance FROM accounts WHERE id = @from_id FOR UPDATE;
    SELECT balance INTO @to_balance FROM accounts WHERE id = @to_id FOR UPDATE;
ELSE
    SELECT balance INTO @to_balance FROM accounts WHERE id = @to_id FOR UPDATE;
    SELECT balance INTO @from_balance FROM accounts WHERE id = @from_id FOR UPDATE;
END IF;

-- Verify sufficient balance
IF @from_balance >= @amount THEN
    UPDATE accounts SET balance = balance - @amount WHERE id = @from_id;
    UPDATE accounts SET balance = balance + @amount WHERE id = @to_id;

    COMMIT;
    SELECT 'Transfer successful' AS message;
ELSE
    ROLLBACK;
    SELECT 'Insufficient balance' AS message;
END IF;

Monitoring Lock Performance

Use these queries to monitor locking issues:

-- Find long-running transactions
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id
FROM information_schema.INNODB_TRX
WHERE trx_started < NOW() - INTERVAL 30 SECOND;

-- Find transactions waiting for locks
SELECT
    waiting_trx_id,
    waiting_pid,
    blocking_trx_id,
    blocking_pid
FROM sys.innodb_lock_waits;

-- View lock wait statistics
SELECT * FROM sys.schema_table_lock_waits;

Summary

In this lesson, you learned:

Locks prevent concurrent transactions from interfering with each other
Shared locks allow reads; exclusive locks prevent all access
InnoDB uses row-level locks for better concurrency
Deadlocks occur when transactions wait circularly for each other
MySQL automatically detects and resolves deadlocks by rolling back one transaction
Prevent deadlocks by accessing resources in consistent order and keeping transactions short
Implement retry logic in application code to handle inevitable deadlocks

Next Up: In the next lesson, we'll explore constraints and data integrity mechanisms that enforce business rules at the database level!