Understanding how InnoDB manages concurrent reads and writes is essential if you want predictable MySQL behaviour under load. This article walks through InnoDB's Multi-Version Concurrency Control (MVCC), row locking, and common pitfalls, with practical examples you can test on a development instance.

1. The basics: MVCC in InnoDB

InnoDB uses MVCC to let readers and writers proceed concurrently without blocking each other unnecessarily.

Conceptually, each row has multiple versions over time:

┌───────────────┐
│  undo logs    │  <-- older versions
└─────┬─────────┘
      │  (pointers)
┌─────▼─────────┐
│ clustered     │
│ index (data)  │  <-- latest committed version
└───────────────┘

Each transaction sees a snapshot of rows based on:

• Its isolation level.
• The transaction start time (or first read time, depending on level).
• Which versions were committed before that snapshot.

Key points:

• Reads usually do not block writes; writes usually do not block reads.
• Writers block other writers on the same row (or range), via locks.
• Old versions live in the undo logs until no transaction needs them.

2. Isolation levels and what you actually see

MySQL supports the standard SQL isolation levels, but InnoDB implements them using MVCC and row locks.

• READ UNCOMMITTED: Can see uncommitted changes (dirty reads). Rarely appropriate.
• READ COMMITTED: Each statement sees only committed data as of its start time.
• REPEATABLE READ (default): A transaction sees a consistent snapshot as of its first read, plus its own changes.
• SERIALIZABLE: Adds extra locking; behaves like REPEATABLE READ plus implicit locking reads.

Check your current isolation level:

SHOW VARIABLES LIKE 'tx_isolation';  -- or 'transaction_isolation' on newer versions

Set it for the current session only (safe to experiment):

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Best practice:

• Use REPEATABLE READ for most OLTP workloads.
• Consider READ COMMITTED if you struggle with long-running transactions and history bloat, or if your app expects "fresh" reads each statement.
• Avoid READ UNCOMMITTED and SERIALIZABLE unless you have a specific, justified reason.

3. How InnoDB row locks work

InnoDB uses different lock types, but for most engineers the key ones are:

• Record locks: Lock individual index records.
• Gap locks: Lock gaps between index records (no actual row, just the space).
• Next-key locks: Record lock + gap lock on the interval up to the next record.

In REPEATABLE READ, InnoDB typically uses next-key locks to avoid phantom reads for locking reads (e.g. SELECT ... FOR UPDATE). In READ COMMITTED, it tends to use more record-only locking, reducing contention but allowing phantoms.

3.1 Example table

CREATE TABLE accounts (
    id          BIGINT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    balance     DECIMAL(18,2) NOT NULL,
    status      TINYINT NOT NULL,
    KEY idx_user_status (user_id, status)
) ENGINE=InnoDB;

3.2 Simple row lock

Session A:

START TRANSACTION;
UPDATE accounts
   SET balance = balance - 100
 WHERE id = 42;

Session B (concurrent):

START TRANSACTION;
UPDATE accounts
   SET balance = balance + 100
 WHERE id = 42;  -- will block until Session A commits or rolls back

Because id is the primary key, InnoDB locks exactly that row.

4. Why indexes matter for locking

InnoDB always locks index records. If your WHERE clause cannot use an index, InnoDB may scan and lock many more rows than you expect.

4.1 Index-based vs table scan locking

Suppose you run:

-- No index on status alone
START TRANSACTION;
UPDATE accounts
   SET status = 2
 WHERE status = 1;

If idx_user_status is not useful for this predicate (e.g. no leading user_id filter), InnoDB may need to scan the whole table, locking many rows.

Better:

CREATE INDEX idx_status ON accounts(status);

Now the UPDATE can lock only rows where status = 1 using the index.

Best practices:

• Ensure all high-traffic UPDATE/DELETE statements are backed by suitable indexes.
• Check execution plans with EXPLAIN to confirm index use.
• Be wary of functions on columns in WHERE clauses; they often disable index usage.

5. Gap locks and "phantom" protection

Gap locks and next-key locks are often the source of confusing blocking behaviour.

5.1 Visualising gaps

Index values in idx_user_status (simplified):

(user_id, status)
(10, 1)
(10, 2)
(20, 1)
(30, 1)

Gaps:
(-∞, (10,1))
((10,1), (10,2))
((10,2), (20,1))
((20,1), (30,1))
((30,1), +∞)

A next-key lock on (10,1) covers:

• The record (10,1).
• The gap before the next record, e.g. up to (10,2).

5.2 Example: preventing inserts into a range

Session A:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts
 WHERE user_id = 10 AND status = 1
 FOR UPDATE;

This typically acquires next-key locks on the matching rows and adjacent gaps in idx_user_status.

Session B:

INSERT INTO accounts (id, user_id, balance, status)
VALUES (1000, 10, 0.00, 1);  -- may block, as it falls in a locked gap

In REPEATABLE READ, this insert can block because Session A's next-key lock protects the range from phantoms. If you switch to READ COMMITTED, InnoDB uses fewer gap locks for simple statements, reducing such blocking at the cost of allowing phantoms.

6. Locking reads: FOR UPDATE vs LOCK IN SHARE MODE

InnoDB supports two main locking read modifiers:

• SELECT ... FOR UPDATE: Acquires exclusive locks; blocks other writers and locking readers.
• SELECT ... LOCK IN SHARE MODE (or FOR SHARE in newer syntax): Shared locks; block writers but allow other shared locks.

6.1 Typical pattern for "select then update"

Session A:

START TRANSACTION;
SELECT balance
  FROM accounts
 WHERE id = 42
 FOR UPDATE;  -- lock the row

UPDATE accounts
   SET balance = balance - 100
 WHERE id = 42;

COMMIT;

This avoids the "lost update" problem when two sessions read the same balance and update it independently.

Best practices:

• Always use a locking read when implementing "check then write" logic based on the current row state.
• Keep transactions short; hold locks for as little time as possible.

7. Detecting and analysing locks

On RHEL/Rocky Linux, you can use the MySQL client to inspect lock information.

7.1 See running transactions

SELECT *
  FROM information_schema.innodb_trx
 ORDER BY trx_started;

Key columns:

• trx_started: When the transaction began.
• trx_state: RUNNING, LOCK WAIT, etc.
• trx_query: The current (or last) statement.

7.2 See lock waits

SELECT *
  FROM information_schema.innodb_lock_waits;

Join with innodb_locks (on older versions) or use performance_schema.data_locks on newer releases to see which rows or indexes are involved.

7.3 Basic deadlock investigation

When InnoDB detects a deadlock, it rolls back one transaction and logs details. On RHEL/Rocky Linux, check:

sudo grep -i deadlock /var/log/mysqld.log

or wherever your MySQL error log is configured. The log includes the statements and locks involved, which you can use to redesign queries or add indexes.

8. Configuration and operational tips

8.1 Transaction timeouts

Long-running transactions hold locks and keep old row versions around. Consider setting:

-- Time a statement will wait for a lock before giving up
SET GLOBAL innodb_lock_wait_timeout = 50;  -- seconds, tune to your workload

Be cautious changing this globally on production. Test the impact first, and consider setting it at session level for specific workloads.

8.2 Autocommit and implicit transactions

By default, autocommit = 1, so each statement is its own transaction. If you disable autocommit:

SET autocommit = 0;

then every statement runs inside a transaction that stays open until you explicitly COMMIT or ROLLBACK. This can easily lead to forgotten long-running transactions.

Best practices:

• Keep autocommit = 1 at the server level unless your application manages transactions very carefully.
• In application code, always ensure transactions have clear begin/commit/rollback paths, including error handling.

9. Practical design guidelines

• Index for your write paths: Design composite indexes to match your most common UPDATE/DELETE/SELECT … FOR UPDATE predicates.
• Avoid wide range locks: Narrow WHERE clauses with appropriate indexes to minimise locked ranges.
• Keep transactions short: Do not wait for user input or long external calls inside a transaction.
• Consistent locking order: When touching multiple tables or rows, access them in a consistent order across your application to reduce deadlocks.
• Monitor: Regularly inspect innodb_trx and lock wait metrics in performance_schema or your monitoring system.

Conclusion

InnoDB's MVCC and row locking model can look complex, but with a clear grasp of isolation levels, indexes, and lock types, you can predict and control concurrency behaviour. Design your schema and queries to lock as little as possible, keep transactions short, and monitor for lock waits and deadlocks. These practices will give your MySQL workloads far more consistent performance under real-world contention.

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