Mastering MySQL Transactions: From ACID Principles to Lock Mechanisms for High‑Concurrency Data Safety

ACID Principles and Implementation

Atomicity : a transaction either fully commits or fully rolls back.

Consistency : the database moves from one consistent state to another.

Isolation : concurrent transactions are isolated from each other.

Durability : once committed, changes survive crashes.

Atomicity – Undo Log

-- Atomicity example
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- If any statement fails, all changes are rolled back
COMMIT;

InnoDB records the original row versions in an Undo Log, uses MVCC to provide snapshot reads, and reverts changes during a rollback.

Consistency – Constraints

-- Consistency constraints example
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10,2) CHECK (balance >= 0),
    user_id INT UNIQUE
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    account_id INT,
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

Isolation – MVCC and Locks

-- MVCC example: each transaction sees a snapshot
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts; -- sees data as of transaction start
COMMIT;

Durability – Redo Log

-- Durability guarantee
START TRANSACTION;
INSERT INTO logs (message) VALUES ('important operation');
COMMIT; -- after commit, data persists even after a crash

Redo Log uses a write‑ahead log (WAL): changes are first written to the redo log, then to the data files, and replayed during crash recovery.

Isolation Levels and Concurrency Problems

Four Isolation Levels

SELECT @@transaction_isolation; -- view current level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- set session level
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- set global level

Isolation Level Comparison

Concurrency Issues

Dirty Read

-- Transaction A (uncommitted update)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1; -- not committed

-- Transaction B reads dirty data
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- returns 500 (dirty)
ROLLBACK; -- A rolls back, B's read becomes invalid

Non‑repeatable Read

-- Transaction A reads first
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- returns 100

-- Transaction B updates and commits
START TRANSACTION;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Transaction A reads again
SELECT balance FROM accounts WHERE id = 1; -- returns 200, values differ

Phantom Read

-- Transaction A selects rows with balance > 100
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 100; -- returns 2 rows

-- Transaction B inserts a new qualifying row and commits
START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (3, 150);
COMMIT;

-- Transaction A repeats the query
SELECT * FROM accounts WHERE balance > 100; -- still returns 2 rows (phantom prevented)

Lock Mechanisms Deep Dive

Lock Types Overview

Record Lock

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- exclusive lock on row 1
-- Another session trying to modify row 1 will block

Gap Lock

START TRANSACTION;
SELECT * FROM accounts WHERE id BETWEEN 1 AND 10 FOR UPDATE; -- locks the gap 1‑10
-- Inserting id=5 blocks, inserting id=15 succeeds

Next‑Key Lock (Record + Gap)

START TRANSACTION;
-- Assuming rows with ids 1,3,5,7 exist
SELECT * FROM accounts WHERE id = 5 FOR UPDATE; -- locks row 5 and gaps (3,5) & (5,7)

Lock Compatibility Matrix

Lock Monitoring and Diagnosis

SELECT * FROM performance_schema.data_locks;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- Detailed wait info
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query, b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread, b.trx_query 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;

Deadlock Detection and Prevention

Deadlock Example

-- Session 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- holds lock on id=1

-- Session 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2; -- holds lock on id=2

-- Session 1 tries to lock id=2 and waits
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- blocked

-- Session 2 tries to lock id=1 and waits
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- blocked → deadlock

Detecting and Handling Deadlocks

SHOW ENGINE INNODB STATUS\G   -- view LATEST DETECTED DEADLOCK section
SHOW VARIABLES LIKE 'innodb_deadlock_detect';   -- is detection enabled?
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';   -- lock wait timeout
SET SESSION innodb_lock_wait_timeout = 50;   -- adjust timeout

Prevention Strategies

Fixed Resource Order

-- Bad: different order
-- Txn1: A then B
-- Txn2: B then A

-- Good: same order (e.g., order by id)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Proper Indexes

CREATE INDEX idx_account_id ON accounts(id);   -- avoids table‑level locks
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- row‑level lock
SELECT * FROM accounts WHERE name = 'John' FOR UPDATE;   -- without index may cause table lock

Short Transactions

-- Bad: long transaction with many steps
START TRANSACTION;
-- complex logic ...
UPDATE accounts SET ...;
COMMIT;   -- holds locks too long

-- Good: commit as soon as possible
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Retry on Deadlock (Python)

# Python example for deadlock retry
import pymysql, time

def execute_with_retry(conn, sql, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                return True
        except pymysql.err.OperationalError as e:
            if 'Deadlock' in str(e) and attempt < max_retries - 1:
                time.sleep(0.1 * (2 ** attempt))  # exponential backoff
                continue
            else:
                raise
    return False

Best Practices

Transaction Design Principles

Keep transactions short to reduce lock hold time.

Access resources in a fixed order to avoid circular waits.

Optimize indexes so queries use row‑level locks instead of table locks.

Choose the lowest isolation level that satisfies business correctness.

Handle errors by implementing deadlock retry logic.

Performance Optimization Tips

-- Batch operations reduce transaction count
START TRANSACTION;
INSERT INTO logs (message) VALUES ('msg1'), ('msg2'), ('msg3');
COMMIT;

-- Use READ COMMITTED to lower lock contention
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Limit long‑running transactions
SET SESSION max_execution_time = 5000;   -- 5 seconds timeout

-- Periodically analyze lock waits
ANALYZE TABLE accounts;

Monitoring Script

SELECT r.trx_id, r.trx_state, r.trx_started, r.trx_wait_started,
       TIMEDIFF(NOW(), r.trx_started) AS trx_duration,
       r.trx_mysql_thread_id, r.trx_query,
       l.lock_table, l.lock_index, l.lock_type, l.lock_mode, l.lock_status
FROM information_schema.innodb_trx r
LEFT JOIN information_schema.innodb_locks l ON r.trx_id = l.lock_trx_id
WHERE r.trx_state = 'LOCK WAIT'
   OR TIMEDIFF(NOW(), r.trx_started) > '00:05:00'   -- running >5 min
ORDER BY r.trx_started;