Quantum Ink

Writing about the future as it unfolds.

Diagram illustrating InnoDB locking and transaction isolation concepts in MySQL.

Understanding InnoDB Locking and Transaction Isolation in MySQL

Locking and transaction isolation in InnoDB are central to correctness and performance. Misunderstand them, and you get deadlocks, timeouts, or mysterious slowdowns. Understand them, and you can design queries and schemas that scale safely.

This article walks through how InnoDB locks work, how isolation levels affect behaviour, and practical patterns to avoid common pitfalls.

1. Core concepts: transactions, isolation and MVCC

InnoDB provides ACID transactions using MVCC (multi-version concurrency control). Readers usually do not block writers and vice versa, but some statements take locks that can conflict.

Key concepts:

  • Transaction: group of statements that commit or roll back together.
  • Isolation level: defines which changes from other transactions you can see.
  • MVCC: readers see a consistent snapshot; old row versions are stored in the undo log.
  • Locks: ensure conflicting writes are serialised safely.

Common isolation levels in MySQL/InnoDB:

  • READ UNCOMMITTED: rarely used; can see uncommitted changes (dirty reads).
  • READ COMMITTED: each statement sees committed data at its start.
  • REPEATABLE READ (default): entire transaction sees a consistent snapshot.
  • SERIALIZABLE: highest isolation; more locking and contention.

2. Types of InnoDB locks

InnoDB uses several lock types. The most important for day-to-day work:

2.1 Row-level locks

  • Record locks: locks on individual index records.
  • Gap locks: locks on gaps between index records (no actual row).
  • Next-key locks: record lock + gap lock on the gap before the record.

Row locks are acquired on index entries, not directly on table rows. This is why good indexing is critical for predictable locking.

2.2 Lock modes

  • S-lock (shared): for reads that need to block writers (e.g. SELECT … FOR UPDATE).
  • X-lock (exclusive): for writes (INSERT, UPDATE, DELETE).
  • Intention locks (IS, IX): table-level hints that row-level locks will be taken.

Shared vs exclusive conflict; intention locks allow row locks to co-exist efficiently.

3. Isolation levels and locking behaviour

You can check the current isolation level:

SHOW VARIABLES LIKE 'transaction_isolation';

Set it at session level (safe for experiments):

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3.1 REPEATABLE READ (InnoDB default)

Characteristics:

  • Each transaction sees a snapshot from the time of its first read.
  • SELECTs without FOR UPDATE/LOCK IN SHARE MODE normally do not take row locks.
  • To prevent phantoms for locking reads, InnoDB uses next-key locks.

Example pattern:

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 10 FOR UPDATE;
-- row (and gap) locked for concurrent writers

-- Session B
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 10;
-- blocks until Session A commits or rolls back

3.2 READ COMMITTED

In READ COMMITTED:

  • Each statement sees the latest committed data.
  • Locking reads still take row locks but use fewer gap locks.
  • Can reduce contention at the cost of allowing some anomalies (e.g. non-repeatable reads).

READ COMMITTED is often chosen for OLTP workloads to reduce deadlocks caused by gap locks, but you must design around its semantics.

4. Step-by-step: observing locks and deadlocks

4.1 Setup example table

CREATE TABLE accounts (
  id        BIGINT PRIMARY KEY,
  owner     VARCHAR(100) NOT NULL,
  balance   DECIMAL(18,2) NOT NULL,
  status    VARCHAR(20) NOT NULL,
  KEY idx_status (status)
) ENGINE=InnoDB;

INSERT INTO accounts (id, owner, balance, status) VALUES
  (1, 'Alice', 1000.00, 'active'),
  (2, 'Bob',   500.00,  'active'),
  (3, 'Cara',  200.00,  'pending');

4.2 Demonstrate a simple blocking update

-- Session A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- row id=1 locked with X-lock

-- Session B
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- blocks until Session A commits or rolls back

While Session B is blocked, you can inspect locks from another session:

SHOW ENGINE INNODB STATUS\G

Look for the TRANSACTIONS section to see which transactions are waiting and which locks they hold.

4.3 Deadlock example (two sessions, reversed order)

Deadlocks occur when transactions hold locks the other needs, in a cycle. InnoDB detects deadlocks and rolls back one transaction.

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

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

-- Back to Session A
UPDATE accounts SET balance = balance + 20 WHERE id = 2;
-- waits for Session B's lock on id=2

-- Back to Session B
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
-- deadlock: waits for Session A's lock on id=1
-- InnoDB detects this and rolls back one session

Typical deadlock diagram:

┌─────────────┐        ┌─────────────┐
│ Session A   │        │ Session B   │
│ holds: id=1│        │ holds: id=2│
│ waits: id=2│ <──────│ waits: id=1│
└─────────────┘        └─────────────┘

5. Gap and next-key locks: why range queries can block inserts

In REPEATABLE READ, InnoDB uses next-key locks for locking reads (SELECT … FOR UPDATE / LOCK IN SHARE MODE) on indexed ranges.

Example:

-- Assume PRIMARY KEY on id

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts
  WHERE id BETWEEN 1 AND 5
  FOR UPDATE;
-- locks rows 1..5 and the gaps around them

Now, a concurrent insert into this range can be blocked:

-- Session B
INSERT INTO accounts (id, owner, balance, status)
VALUES (4, 'Dan', 300.00, 'active');
-- may block due to gap / next-key lock from Session A

Diagram of locked index range:

Index on id:

  gap  row1  gap  row2  gap  row3  gap  row4  gap
  ─┬────┬────┬────┬────┬────┬────┬────┬────┬──
   0    1    2    3    4    5    6    7

Next-key lock on [1,5] means
rows 1..5 and surrounding gaps are protected.

This behaviour prevents phantom rows from appearing in the locked range but can surprise you if you expect inserts to be independent.

6. Best practices to reduce lock contention

6.1 Keep transactions small and short-lived

  • Do not keep transactions open across user think time or long application calls.
  • Move non-DB work (HTTP calls, heavy computations) outside transactions.
  • Commit as soon as you do not need the locks or snapshot.

6.2 Access rows in a consistent order

To reduce deadlocks, ensure all code paths lock rows in the same order.

Instead of:

-- sometimes (1 then 2), sometimes (2 then 1)
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

Use a consistent ordering:

SET @id1 = LEAST(@from_id, @to_id);
SET @id2 = GREATEST(@from_id, @to_id);

SELECT * FROM accounts WHERE id IN (@id1, @id2) FOR UPDATE;
-- then perform updates in that order

6.3 Always use appropriate indexes

Without a useful index, InnoDB may lock more rows than necessary because it must scan and lock via a less selective index or the clustered index.

  • Add indexes to support WHERE clauses used in updates and locking reads.
  • Avoid functions on columns in predicates that prevent index usage.
  • Check execution plans with EXPLAIN to confirm index usage.

6.4 Choose isolation levels deliberately

  • For many OLTP workloads, REPEATABLE READ is a good default.
  • If you experience many deadlocks due to gap locks, consider READ COMMITTED, but verify application logic for anomalies.
  • Use SERIALIZABLE only when you truly need serial semantics and can tolerate lower concurrency.

6.5 Use locking reads only when necessary

SELECT ... FOR UPDATE and LOCK IN SHARE MODE are powerful but can cause heavy contention.

  • Use them for critical invariants (e.g. account balances, unique business constraints).
  • Avoid using them in read-only queries that do not need to block writers.
  • Prefer optimistic approaches (read, then update with a version column) when suitable.

7. Monitoring and troubleshooting locks

7.1 Inspecting current locks

On RHEL/Rocky Linux, you can connect to MySQL and use:

mysql -u root -p -h 127.0.0.1

Inside MySQL:

SHOW ENGINE INNODB STATUS\G

SELECT * FROM information_schema.innodb_trx\G;
SELECT * FROM information_schema.innodb_locks\G;
SELECT * FROM information_schema.innodb_lock_waits\G;

These views show active transactions, held locks, and wait relationships. You can script periodic snapshots to diagnose recurring contention.

7.2 Handling deadlocks in applications

Deadlocks are normal under high concurrency. Applications must:

  • Detect deadlock errors (e.g. SQLSTATE 40001 or MySQL error 1213).
  • Roll back the current transaction.
  • Retry the operation a limited number of times with backoff.

Do not disable deadlock detection or increase lock timeouts to huge values; that only hides problems and increases latency.

8. Summary

InnoDB locking and isolation define how your data behaves under concurrency. Understanding row, gap and next-key locks, and how isolation levels change behaviour, lets you design queries and schemas that avoid unnecessary blocking and deadlocks. Keep transactions short, access rows in a consistent order, index your predicates, and choose isolation levels deliberately. With these practices, you can achieve both correctness and high throughput.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

Smart reads for curious minds

We don’t spam! Read more in our privacy policy

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *