Quantum Ink

Writing about the future as it unfolds.

Graph comparing MySQL InnoDB Cluster performance metrics across different configurations.

Benchmarking InnoDB Cluster Performance

,

Understanding how InnoDB implements MVCC (multi-version concurrency control) and locking is essential if you care about correctness and throughput in MySQL. This article walks through the core concepts, shows what really happens during common queries, and gives practical tuning and troubleshooting tips.

1. Core building blocks: undo log and transaction IDs

InnoDB implements MVCC using two main pieces of metadata on each row:

  • A transaction ID that created the current version.
  • A pointer to an undo log record for the previous version of the row (if any).

Conceptually, a row with multiple versions looks like this:

┌─────────────────────────────────────────────┐
│  Latest row version (in clustered index)   │
│  trx_id = 120                              │
│  undo_ptr ──────────────────────────────┐  │
└─────────────────────────────────────────┬┘  │
                                          │   │
                          ┌───────────────▼───▼───────────────┐
                          │   Undo log record (older image)   │
                          │   trx_id = 95                     │
                          │   undo_ptr ────────────────┐      │
                          └─────────────────────────────┬─────┘
                                                        │
                                       ┌────────────────▼──────────────┐
                                       │   Undo log record (even       │
                                       │   older image), trx_id = 60   │
                                       └───────────────────────────────┘

Readers and writers use these versions differently:

  • Readers choose which version is visible using a consistent snapshot.
  • Writers modify the latest version and create new undo records.

2. How snapshots work (REPEATABLE READ focus)

By default, InnoDB uses REPEATABLE READ isolation. When a transaction starts its first consistent read (e.g. SELECT ... without FOR UPDATE), InnoDB records:

  • The transaction's own trx_id.
  • A list (or range) of active transaction IDs at that moment.

When reading a row, InnoDB checks:

  1. Is the row's trx_id my own? If yes, I see it.
  2. Is the row's trx_id committed and not in the active list at snapshot creation time? If yes, I see it.
  3. Otherwise, follow undo_ptr to an older version and repeat.

This lets readers avoid blocking writers in most cases.

Example: two concurrent transactions

-- Session A
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;  -- snapshot created

-- Session B
START TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE id = 1;
COMMIT;

-- Session A again
SELECT balance FROM account WHERE id = 1;  -- sees old balance (repeatable)

Session A keeps seeing the same snapshot until it commits, even though Session B has committed an update.

3. Lock types: record, gap, and next-key

MVCC does not remove the need for locks; it just reduces conflicts for plain reads. Writers still use locks to protect data integrity and prevent anomalies.

InnoDB uses three main lock types on index records:

  • Record locks: lock a single index entry.
  • Gap locks: lock the gap between index entries (or before the first/after the last).
  • Next-key locks: a record lock + the gap before it.

Under REPEATABLE READ, InnoDB typically uses next-key locks for range operations to avoid phantom rows.

Visualising gaps

Assume an index on age with values 20, 30, 40:

┌───────┬──────────────┐
│ Gap   │ Covered ages │
├───────┼──────────────┤
│ G0    │ (-∞, 20)     │
│ R20   │ 20           │
│ G1    │ (20, 30)     │
│ R30   │ 30           │
│ G2    │ (30, 40)     │
│ R40   │ 40           │
│ G3    │ (40, +∞)     │
└───────┴──────────────┘

A next-key lock on 30 under REPEATABLE READ covers G1 + R30, i.e. (20, 30]. That prevents new rows with age between 21 and 30 from being inserted concurrently.

4. When SELECTs take locks

By default, a plain SELECT uses MVCC and does not acquire row locks:

START TRANSACTION;
SELECT * FROM orders WHERE id = 10;  -- consistent read, no row locks

However, InnoDB will acquire locks if you ask for them explicitly:

  • SELECT ... FOR UPDATE – exclusive locks, blocking other writers and FOR UPDATE readers.
  • SELECT ... LOCK IN SHARE MODE – shared locks, allowing other shared locks but blocking writers.

Example:

-- Session A
START TRANSACTION;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;

-- Session B
START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE id = 10;  -- waits for A

Use these locking reads carefully; they are essential for some patterns (e.g. work queues, unique checks), but they reduce concurrency.

5. INSERT, UPDATE, DELETE: what they actually lock

INSERT

  • Locks the new index entry in the relevant indexes (record lock).
  • May take gap/next-key locks to enforce unique constraints.
INSERT INTO user(email) VALUES('[email protected]');

If email is unique, InnoDB must ensure no concurrent insert of the same value succeeds, so it locks appropriately around that key.

UPDATE

  • Locks the matched rows (record or next-key locks depending on the search condition and isolation level).
  • If the indexed column changes, InnoDB effectively deletes + reinserts in the index, affecting gaps.
UPDATE account SET balance = balance - 100
WHERE id = 1;

Best practice: always use indexed predicates in your WHERE clause to avoid large scans and broad locking.

DELETE

  • Locks the rows to be deleted.
  • Can take next-key locks to protect index ranges during the operation.

Large deletes can hold locks for a long time. Consider batching:

SET autocommit = 0;

DELETE FROM events
WHERE created_at < NOW() - INTERVAL 30 DAY
LIMIT 1000;
COMMIT;

-- repeat in a loop until 0 rows affected

6. Isolation levels and their impact

MySQL supports several isolation levels; the two most relevant here are REPEATABLE READ and READ COMMITTED.

REPEATABLE READ (default)

  • Consistent snapshot per transaction.
  • Prevents non-repeatable reads and phantoms using next-key locks.
  • More gap/next-key locking; can reduce concurrency for range queries.

READ COMMITTED

  • Each statement sees only committed data at its start time.
  • Fewer gap locks; only necessary ones for foreign keys and uniqueness.
  • Range queries can see phantoms; repeat selects may see different data.

To change globally (requires restart or careful coordination):

[mysqld]
transaction-isolation = READ-COMMITTED

To change per session:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Best practice: stay with REPEATABLE READ unless you understand and accept the visibility changes. For high-contention range workloads, READ COMMITTED can improve throughput.

7. Monitoring locks and deadlocks

Inspecting current locks

Use INFORMATION_SCHEMA to see active transactions and locks:

SELECT * FROM information_schema.innodb_trx\G

SELECT * FROM information_schema.innodb_locks\G

SELECT * FROM information_schema.innodb_lock_waits\G

On newer MySQL versions, performance_schema offers richer views; check your documentation for the exact table names such as data_locks and data_lock_waits.

Deadlock detection

InnoDB automatically detects deadlocks and rolls back one victim transaction. To inspect the latest deadlock:

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section. It shows the queries involved, the locks they held, and the locks they waited for.

Best practices to reduce deadlocks:

  • Access tables and rows in a consistent order across your application.
  • Keep transactions short; avoid user interaction between statements.
  • Use covering, selective indexes to minimise scanned/locked rows.
  • Consider READ COMMITTED if range locking is a major source of deadlocks.

8. Practical tuning and schema tips

1. Always index your foreign keys and join columns

Without indexes, InnoDB must scan and lock many rows, increasing contention and deadlock risk.

ALTER TABLE orders
  ADD INDEX idx_orders_customer_id (customer_id);

2. Avoid "hidden" full table scans

Watch for non-sargable predicates (e.g. wrapping columns in functions) that prevent index use:

-- Bad: function on column, likely full scan and broad locking
SELECT * FROM orders
WHERE DATE(created_at) = '2025-01-01';

-- Better: use range on the indexed column
SELECT * FROM orders
WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02';

3. Keep transactions focused

  • Group related changes, but avoid "chatty" long-lived transactions.
  • Commit as soon as your logical unit of work is complete.
  • Do not hold transactions open while calling external services.

4. Tune InnoDB for long-running transactions

Long transactions keep old snapshots alive, preventing purge of old undo records and growing the undo history. Monitor for transactions that stay open for minutes or hours.

On busy systems, this can cause:

  • Increased disk usage (undo tablespaces).
  • Slower queries due to longer version chains.

5. Use explicit locking only where necessary

Patterns like "get row then update" often tempt developers to overuse SELECT ... FOR UPDATE. Restrict explicit locks to cases where you truly need serialisation, such as:

  • Allocating unique sequence-like values in a custom way.
  • Implementing a reliable work queue where each job is processed once.
  • Enforcing business rules that span multiple rows.

Otherwise, rely on MVCC and unique constraints.

9. Summary

InnoDB's MVCC and locking design allows high concurrency while maintaining transactional guarantees. Rows keep multiple versions through the undo log, and consistent snapshots let readers avoid blocking writers. At the same time, record, gap, and next-key locks protect index ranges and enforce correctness.

If you understand how isolation levels, index choices, and transaction design interact with these mechanisms, you can reason about blocking, reduce deadlocks, and tune your MySQL workloads with confidence.

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 *