MySQL interview questions: InnoDB transactions and plans

Why does a primary key affect InnoDB secondary indexes?

InnoDB organizes table rows by the clustered primary key. Each secondary-index entry carries that key to locate its row, so a wide primary key increases secondary-index footprint.

Choose a primary key that is stable and reasonably compact, then index real filters and ordering. Randomness, monotonicity, key width and insertion patterns have different trade-offs; no single surrogate-key rule fits every workload.

Every extra index costs storage and work on inserts or indexed updates. Verify with the actual DDL and query plan before adding indexes reflexively.

CREATE TABLE orders (id BIGINT NOT NULL PRIMARY KEY, tenant_id BIGINT NOT NULL, created_at DATETIME NOT NULL, KEY tenant_created (tenant_id, created_at, id)) ENGINE=InnoDB;

Read the source: InnoDB clustered and secondary indexes · Primary key optimization

↑ Back to questions

What differs between a consistent read and a locking read?

A normal consistent SELECT reads from a snapshot. SELECT ... FOR UPDATE is a current locking read for a transaction making an update decision.

Under InnoDB’s default REPEATABLE READ, consistent reads use a read view; locking reads and writes operate on current records. Lock ranges depend on isolation, index and scanned range, so a predicate can lock more than the returned rows.

Use the locking form when subsequent writes depend on the rows remaining available. Keep the transaction short and inspect the actual index path when contention surprises you.

START TRANSACTION;
SELECT available FROM inventory WHERE sku = 'A17' FOR UPDATE;
UPDATE inventory SET available = available - 1 WHERE sku = 'A17';
COMMIT;

Read the source: InnoDB transaction isolation levels · InnoDB locking

↑ Back to questions

What does InnoDB REPEATABLE READ guarantee?

Ordinary consistent reads in a transaction use a stable read view, while locking reads use current records. The label does not make every kind of read interchangeable.

InnoDB uses REPEATABLE READ by default. A transaction that mixes a snapshot SELECT with a locking read can see different versions because the operations have different purposes. Use a locking read, conditional update or constraint for a current-value invariant.

READ COMMITTED may reduce some gap-lock contention, but could change assumptions around phantoms and repeated reads. Change it only after reproducing the relevant interleaving.

START TRANSACTION;
SELECT quantity FROM stock WHERE sku = 'A17'; -- consistent read
SELECT quantity FROM stock WHERE sku = 'A17' FOR UPDATE; -- locking read
COMMIT;

Read the source: InnoDB transaction isolation levels

↑ Back to questions

What should an application do after an InnoDB deadlock?

Treat it as an aborted transaction, roll back and retry the whole idempotent unit a bounded number of times.

Deadlocks arise when transactions acquire incompatible locks in a cycle. InnoDB detects one and rolls back a victim. A retry must repeat the reads that formed the decision rather than rerun only the failed UPDATE.

Reduce recurrence by keeping transactions short and taking rows in deterministic order. Repeated retry exhaustion is an operational signal, not something to hide with an infinite loop.

for attempt in range(max_attempts):
    try:
        transfer_in_one_transaction(source, destination, amount)
        break
    except DeadlockError:
        if attempt + 1 == max_attempts:
            raise

Read the source: InnoDB deadlocks · InnoDB locking

↑ Back to questions

How do you investigate a slow MySQL query?

Use EXPLAIN to read access path, join order, estimated rows and chosen indexes. Compare estimates with actual behavior safely on representative data.

A large estimate error can indicate skew, stale statistics or a predicate that does not match an index prefix. Composite index order should follow the equality/range/order requirements the query actually has.

EXPLAIN ANALYZE executes the statement. For a write it can change data; use a disposable environment or a controlled reversible operation. Indexes also consume buffer-pool memory and add write work.

EXPLAIN FORMAT=TREE
SELECT id, created_at FROM orders
WHERE tenant_id = 42 AND created_at >= '2026-01-01'
ORDER BY created_at DESC LIMIT 50;

Read the source: EXPLAIN statement · InnoDB statistics estimation

↑ Back to questions

Where should MySQL enforce integrity?

Use NOT NULL, UNIQUE, CHECK and foreign-key constraints for invariants that must survive concurrent writes. Validate at the application boundary too for clear errors.

A check-then-insert duplicate test races. A unique index arbitrates simultaneous requests. In MySQL 8.4, InnoDB enforces CHECK constraints; confirm server release and storage engine on older systems.

Choose foreign-key actions deliberately and plan existing invalid rows before adding constraints. A migration has locking and compatibility effects beyond the logical rule.

CREATE TABLE enrollment (student_id BIGINT NOT NULL, course_id BIGINT NOT NULL, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(id), CHECK (student_id > 0)) ENGINE=InnoDB;

Read the source: CHECK constraints · Foreign key constraints

↑ Back to questions

How do you page through a large changing table?

Use a composite order and cursor predicate for deep pages. A unique tie-breaker gives each result one position.

An order by created_at alone has ties. Include the primary key in ordering and cursor, with an index that starts from equality filters and supports the sort. Separate page requests still do not share one snapshot.

For an export that must represent a point in time, define a snapshot or high-water-mark policy. Keyset pagination solves deep skipping, not every consistency question.

SELECT id, created_at FROM orders
WHERE tenant_id = ? AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC LIMIT 50;

Read the source: LIMIT optimization · Multiple-column indexes

↑ Back to questions

What makes an index or constraint migration risky?

DDL has lock, I/O, disk and compatibility costs. Check the exact MySQL release, table and supported algorithm before planning the change.

Some ALTER operations can be instant or in-place under specific conditions; online does not mean zero metadata lock or resource use. Expand-and-contract keeps old and new application versions compatible while the schema changes.

Detect existing violations before adding a constraint. Observe disk headroom and replication lag during index creation in a safe rehearsal; this example does not authorize production DDL.

-- Planning query: locate duplicate values before a unique index.
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
-- Resolve data and deploy compatible code before a separately planned migration.

Read the source: Online DDL operations · ALTER TABLE

↑ Back to questions

Keep exploring