InnoDB’s transaction and locking model is one of MySQL’s biggest strengths, but it is also a common source of confusion and production incidents. This article walks through how InnoDB locks rows, how isolation levels affect behaviour, and how to design queries and schema to avoid surprises.
1. Core concepts: transactions, ACID, and MVCC
InnoDB is a transactional storage engine. Every change happens inside a transaction, whether you open it explicitly or let MySQL wrap each statement in an implicit transaction.
Key properties (ACID):
- Atomicity: a transaction’s changes are all applied or all rolled back.
- Consistency: constraints (PK, FK, unique) are preserved.
- Isolation: concurrent transactions see a controlled view of data.
- Durability: committed data survives crashes (subject to configuration).
InnoDB implements isolation using MVCC (Multi-Version Concurrency Control):
- Each row has hidden transaction IDs and rollback pointers.
- Readers can see a consistent snapshot without blocking writers (and vice versa) in many cases.
- Locks are still needed to protect modifications and certain reads.
2. InnoDB lock types in practice
InnoDB mainly uses four lock types:
- Shared (S): for reads that must be consistent with future updates (e.g.
SELECT ... FOR UPDATE). - Exclusive (X): for writes (INSERT/UPDATE/DELETE).
- Intention shared (IS) and intention exclusive (IX): lightweight table-level locks indicating intent to lock rows.
Lock granularity:
- Record locks: lock a specific index entry.
- Gap locks: lock the gap between index records (no row, just the space).
- Next-key locks: record lock + gap lock; used to prevent phantom rows at higher isolation levels.
A simplified view of a range lock using next-key locks:
Index values: 10 20 30 40
│ │ │ │
Range "15-35": ┌───────────────┐
│ next-key lock │
└───────────────┘Understanding that InnoDB locks index records, not just “rows” in the heap, is crucial. Poor indexing often leads to much wider locking than expected.
3. Isolation levels and what they really do
MySQL supports four SQL standard isolation levels, but two are used most often.
- READ COMMITTED
- Each statement sees the latest committed data at the time it starts.
- Fewer gap locks; better concurrency for some workloads.
- Can see different data in the same transaction if you run the same SELECT twice.
- REPEATABLE READ (MySQL default)
- Each transaction sees a consistent snapshot from when the first statement started.
- Prevents non-repeatable reads; uses more next-key locks for range queries.
- MySQL’s implementation also prevents phantom reads in most cases via locking.
- READ UNCOMMITTED: rarely useful; allows dirty reads.
- SERIALIZABLE: strongest isolation; internally behaves like REPEATABLE READ plus more locking and blocking.
Check and set the isolation level:
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;4. Step-by-step: seeing locks in action
Use a simple example table:
CREATE TABLE account (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
balance DECIMAL(18,2) NOT NULL,
status VARCHAR(16) NOT NULL,
KEY idx_customer (customer_id)
) ENGINE=InnoDB;4.1. Basic write lock
-- Session A
START TRANSACTION;
UPDATE account
SET balance = balance - 100
WHERE id = 1;
-- Row with id=1 is now X-locked by Session AIn another session:
-- Session B
START TRANSACTION;
UPDATE account
SET balance = balance + 100
WHERE id = 1; -- blocks until Session A commits or rolls backSession B waits because both updates need an exclusive lock on the same record.
4.2. Range locks and next-key behaviour
Now lock a range:
-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM account
WHERE customer_id BETWEEN 100 AND 200
FOR UPDATE;This creates next-key locks on the matching index records in idx_customer and the gaps between them. Session B:
-- Session B
START TRANSACTION;
INSERT INTO account (id, customer_id, balance, status)
VALUES (999, 150, 0.00, 'open'); -- likely to blockThe insert falls into a locked gap in the index, so it waits.
4.3. Reducing locking with READ COMMITTED
If Session A used READ COMMITTED:
-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM account
WHERE customer_id BETWEEN 100 AND 200
FOR UPDATE;InnoDB may lock fewer gaps, allowing more concurrent inserts. However, your transaction might see new rows that appear later in the same range if you re-run the query, which can surprise application logic.
5. Detecting and analysing lock waits
5.1. Finding blocking transactions
On modern MySQL versions, use performance_schema:
SELECT
r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread,
r.trx_query AS waiting_query,
b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r
ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b
ON w.blocking_trx_id = b.trx_id;This gives you a live view of who is blocking whom.
5.2. Deadlocks
A deadlock occurs when two transactions wait on each other. InnoDB detects this and rolls back one of them.
To inspect the latest deadlock:
SHOW ENGINE INNODB STATUS\GLook for the LATEST DETECTED DEADLOCK section. It shows the conflicting statements and lock types. Use this information to adjust index design or statement ordering.
6. Designing queries to minimise locking issues
6.1. Always use proper indexes
Without a suitable index, InnoDB may lock many more rows than you expect.
-- Bad: no index on status, may scan and lock many rows
SELECT * FROM account
WHERE status = 'open'
FOR UPDATE;Better:
ALTER TABLE account
ADD KEY idx_status (status);
SELECT * FROM account
WHERE status = 'open'
FOR UPDATE;Now InnoDB can lock only the relevant index entries.
6.2. Access rows in a consistent order
To reduce deadlocks, ensure that concurrent transactions update rows in the same logical order.
-- Better pattern: deterministic order
START TRANSACTION;
SELECT id FROM account
WHERE customer_id = 100
ORDER BY id
FOR UPDATE;
UPDATE account
SET balance = balance - 10
WHERE customer_id = 100
ORDER BY id;
COMMIT;By always locking rows in ascending id order, you reduce the chance that two transactions grab locks in opposite orders.
6.3. Keep transactions short and focused
- Do not hold open transactions while waiting on user input or external services.
- Avoid long-running reporting queries inside write transactions.
- Commit early once the critical changes are done.
On application side, ensure connection pools do not accidentally hold open transactions due to missing COMMIT or ROLLBACK.
7. Choosing the right isolation level
Common patterns:
- OLTP / web apps
- Default REPEATABLE READ is usually fine.
- Consider READ COMMITTED if you see excessive lock waits on range queries and your logic tolerates non-repeatable reads.
- Reporting / analytics
- Prefer read-only transactions.
- Use REPEATABLE READ to get a stable snapshot for a report.
- Critical financial logic
- Carefully design transactions using
SELECT ... FOR UPDATEon exact rows. - Use idempotent operations and application-level checks in addition to database isolation.
- Carefully design transactions using
Set isolation level per session in your application rather than globally, so different workloads can coexist on the same MySQL instance.
8. Operational best practices for DBAs
- Monitor for lock waits and deadlocks using performance_schema and periodic
SHOW ENGINE INNODB STATUSsnapshots. - Alert on long-running transactions (e.g. > 60 seconds) using queries against
information_schema.innodb_trx. - Review schema and indexes when you see recurring deadlocks; they are often a symptom of poor access paths.
- Document which isolation levels and locking patterns your applications rely on.
- Test concurrency scenarios with realistic data volumes before production changes.
This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.
Leave a Reply