Transactions and Locking in InnoDB

A transaction is a unit of work that is committed or rolled back as a whole (ACID).

Concept

A transaction is a unit of work that is committed or rolled back as a whole (ACID). InnoDB implements:

Atomicity with the undo log.

Durability with the redo log (write-ahead log) and the doublewrite buffer.

Isolation with MVCC (consistent snapshots from undo records) plus locks for writes and locking reads.

Consistency with constraints and correct transaction boundaries.

Key MySQL facts:

autocommit is ON by default: each statement is its own transaction unless you start one.

Default isolation level is REPEATABLE READ.

DDL statements cause an implicit commit.

MyISAM and MEMORY tables are not transactional.

Why It Matters

Payments, bookings, inventory, and test-bench reservations all need correct transactions. Understanding InnoDB locking explains deadlocks, lock-wait timeouts, and unexpected blocking caused by gap locks.

Syntax

START TRANSACTION [READ ONLY | READ WRITE | WITH CONSISTENT SNAPSHOT];   -- or BEGIN
COMMIT;
ROLLBACK;
SAVEPOINT sp;
ROLLBACK TO SAVEPOINT sp;
RELEASE SAVEPOINT sp;
SET autocommit = 0;
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
SELECT ... FOR UPDATE [NOWAIT | SKIP LOCKED];
SELECT ... FOR SHARE  [NOWAIT | SKIP LOCKED];

Example

17.1 A banking transfer Important MySQL behavior: when a statement fails inside a transaction (e.g., CHECK violation), only that statement is rolled back; the transaction remains open. Your code must ROLLBACK:

Always check affected rows too: an UPDATE matching 0 rows is not an error.

17.3 Implicit commits These commit any open transaction automatically: CREATE/ALTER/DROP/RENAME/TRUNCATE TABLE, CREATE/DROP INDEX, CREATE/ALTER/DROP USER, GRANT, REVOKE, LOCK TABLES, LOAD DATA (for some engines), START TRANSACTION itself (commits the previous one), and others. Never mix DDL into a business transaction expecting to roll it back.

17.4 MVCC and consistent reads A plain SELECT in InnoDB is a consistent (non-locking) read: it sees a snapshot and does not block or get blocked by writers.

REPEATABLE READ: the snapshot is taken at the first read in the transaction; later reads see the same data.

READ COMMITTED: each statement sees a fresh snapshot.

Locking reads (FOR UPDATE, FOR SHARE) and UPDATE/DELETE always operate on the latest committed version — a subtle difference that matters for read-modify-write logic.

17.5 Isolation levels in MySQL Phantom (plain SELECT) Fewer gap locks; common choice for high-concurrency OLTP REPEATABLE READ (default)

No for consistent reads; locking reads use next-key locks to prevent phantoms

Plain SELECTs become FOR SHARE when autocommit is off Lost updates are not prevented automatically at REPEATABLE READ for application-side read-modify-write. Use atomic updates or SELECT... FOR UPDATE.

17.6 InnoDB lock types Shared (S) record lock An index record FOR SHARE, FK checks Exclusive (X) record lock UPDATE, DELETE, FOR UPDATE The gap between index records Range locking reads at REPEATABLE READ Record + gap before it

Default for locking scans at REPEATABLE READ Insert intention lock A gap, for inserts Intention locks (IS/IX) Table-level (mode dependent) Metadata lock (MDL) Any statement; DDL needs exclusive MDL

Locks are placed on index records that are scanned, not just on rows returned. Without a suitable index, an UPDATE ... WHERE non_indexed_col = x may lock every row in the table.

17.7 Row-level locking patterns 17.8 Deadlocks and lock waits

InnoDB detects deadlocks immediately and rolls back the transaction with less work. Lock waits without a cycle end with:

Note: after error 1205 only the statement is rolled back (unless innodb_rollback_on_timeout = ON); after 1213 the whole transaction is rolled back.

Prevention: consistent lock order, short transactions, good indexes, READ COMMITTED to reduce gap locks, and retry on 1213.

17.9 Table locks and XA MySQL also supports XA transactions (two-phase commit across resources): XA START, XA END, XA PREPARE, XA COMMIT.

CREATE TABLE bank_accounts (
    account_no  VARCHAR(20)   PRIMARY KEY,
    holder      VARCHAR(100)  NOT NULL,
    balance     DECIMAL(15,2) NOT NULL,
    CONSTRAINT chk_balance CHECK (balance >= 0)
);
INSERT INTO bank_accounts VALUES ('ACC-100', 'Ananya', 50000), ('ACC-200', 'Ben', 10000);

START TRANSACTION;
UPDATE bank_accounts SET balance = balance - 20000 WHERE account_no = 'ACC-100';
UPDATE bank_accounts SET balance = balance + 20000 WHERE account_no = 'ACC-200';
COMMIT;
-- ACC-100: 30000.00, ACC-200: 30000.00

START TRANSACTION;
UPDATE bank_accounts SET balance = balance - 40000 WHERE account_no = 'ACC-100';
-- ERROR 3819: Check constraint 'chk_balance' is violated.
UPDATE bank_accounts SET balance = balance + 40000 WHERE account_no = 'ACC-200';   -- would still succeed!
ROLLBACK;   -- the application must do this on any error

START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 101;
SAVEPOINT s1;
UPDATE products SET stock = stock - 100 WHERE product_id = 101;   -- would violate business rules
ROLLBACK TO SAVEPOINT s1;
COMMIT;
SELECT stock FROM products WHERE product_id = 101;   -- 14

-- Session A                                    -- Session B
START TRANSACTION;
SELECT price FROM products WHERE product_id = 101;   -- 75000.00
                                                UPDATE products SET price = 70000 WHERE product_id = 101;  -- autocommit
SELECT price FROM products WHERE product_id = 101;   -- still 75000.00 (REPEATABLE READ snapshot)
SELECT price FROM products WHERE product_id = 101 FOR UPDATE;   -- 70000.00 (locking read sees latest!)
COMMIT;

SELECT @@transaction_isolation;                       -- REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Session A (REPEATABLE READ)
START TRANSACTION;
SELECT * FROM orders WHERE order_id BETWEEN 1003 AND 1005 FOR UPDATE;   -- next-key locks on 1003..1005 and gaps

-- Session B
INSERT INTO orders VALUES (1007, 5, '2026-04-01', 'PENDING', 100);   -- OK: outside the locked range
INSERT INTO orders VALUES (1004, 5, ...);                            -- would conflict (existing key, and locked)

-- Pessimistic lock for read-modify-write
START TRANSACTION;
SELECT stock FROM products WHERE product_id = 105 FOR UPDATE;
UPDATE products SET stock = stock - 2 WHERE product_id = 105;
COMMIT;

-- Atomic conditional update (often best)
UPDATE products SET stock = stock - 2 WHERE product_id = 105 AND stock >= 2;   -- check ROW_COUNT() = 1

-- Job queue: each worker grabs a different job without waiting
START TRANSACTION;
SELECT job_id FROM test_jobs WHERE status = 'QUEUED' ORDER BY job_id LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE test_jobs SET status = 'RUNNING', worker = 'bench-07' WHERE job_id = ?;
COMMIT;

-- Fail fast instead of waiting
SELECT * FROM seats WHERE seat_no = 'A10' FOR UPDATE NOWAIT;   -- ERROR 3572 if locked

-- T1: UPDATE bank_accounts ... 'ACC-100'   then 'ACC-200'
-- T2: UPDATE bank_accounts ... 'ACC-200'   then 'ACC-100'
-- → ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction   (innodb_lock_wait_timeout, default 50 s)

SHOW ENGINE INNODB STATUS\G                 -- section LATEST DETECTED DEADLOCK
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits\G       -- who waits for whom, with the blocking query and KILL statement
SET GLOBAL innodb_print_all_deadlocks = ON; -- log every deadlock to the error log

Expected Result

SELECT @@autocommit, @@transaction_isolation, @@innodb_lock_wait_timeout;

+--------------+-------------------------+----------------------------+
| @@autocommit | @@transaction_isolation | @@innodb_lock_wait_timeout |
+--------------+-------------------------+----------------------------+
|            1 | REPEATABLE-READ         |                         50 |
+--------------+-------------------------+----------------------------+

Common Mistakes

Assuming an error rolls back the whole transaction (it does not, except deadlocks).

Forgetting COMMIT with autocommit = 0 (locks held, changes invisible).

DDL inside transactions.

Unindexed WHERE in UPDATE/DELETE locking whole tables.

Long transactions blocking purge and growing the undo log ("history list length").

Using MyISAM tables in transactions.

Not retrying deadlocks.

Best Practices

Short transactions; no user interaction inside them.

Handle errors with explicit ROLLBACK and retry on 1213/1205.

Use atomic conditional updates or FOR UPDATE for read-modify-write.

Consider READ COMMITTED for write-heavy OLTP to reduce gap locking.

Monitor sys.innodb_lock_waits and deadlock logs.

Interview Relevance

Very high: "Default isolation level in MySQL?", "What is MVCC?", "What are gap and next-key locks?", "Deadlock vs lock wait timeout?", "What causes implicit commits?", "SKIP LOCKED use cases?".

Practice Questions

Write a transfer that fails safely when the balance is insufficient.

Reproduce a deadlock in two sessions and read it in SHOW ENGINE INNODB STATUS.

Show that REPEATABLE READ keeps a snapshot, while FOR UPDATE sees the latest value.

Build a job queue using FOR UPDATE SKIP LOCKED.