SQL Interview: Indexes, Transactions and ACID
The performance and concurrency half of a SQL interview: how indexes work, what they cost, ACID, isolation levels and deadlocks.
Indexes
Q. What is an index and how does it work?
A separate sorted structure - usually a B-tree - holding indexed column values with pointers back to rows. It turns "examine every row" into a tree descent of three or four page reads, so lookup cost grows with the logarithm of the table rather than its size.
Q. What do indexes cost?
Disk space, memory in the buffer pool, and write speed: every INSERT, UPDATE and DELETE must maintain every affected index. Indexes make reads faster and writes slower.
Q. Clustered versus non clustered?
In InnoDB the primary key is the clustered index and the rows are stored inside it. Secondary indexes store the primary key as their pointer, so a secondary lookup does two descents - one in the index, one in the clustered index. That is why a wide primary key inflates every index in the table.
Q. What is a covering index?
An index containing every column the query needs, so the table is never read. EXPLAIN shows Using index. It is also the strongest practical argument against SELECT *.
Q. Explain the leftmost prefix rule.
An index on (a, b, c) serves queries filtering on a, on a, b, or on a, b, c - any leftmost prefix. It cannot serve a query filtering only on b or only on c. Like a phone book sorted by surname then first name: you cannot look up every "Asha".
Q. When does an index not help?
- The query returns most of the table - a scan is genuinely cheaper.
- The column is wrapped in a function:
WHERE YEAR(order_date) = 2024. - A leading wildcard:
LIKE '%text'. - Very low cardinality, on its own.
- A type mismatch forcing a conversion.
Q. What does sargable mean?
A predicate an index can seek on: the indexed column appears bare on one side of a comparison. WHERE order_date >= '2024-01-01' is sargable; WHERE YEAR(order_date) = 2024 is not.
Transactions and ACID
Q. What are the ACID properties?
- Atomicity - all statements apply or none do. Implemented with the undo log.
- Consistency - every declared constraint holds before and after.
- Isolation - concurrent transactions do not corrupt each other, to the degree the isolation level promises.
- Durability - a committed change survives a crash. Implemented with write ahead logging.
Q. What is a savepoint?
A marker inside a transaction. ROLLBACK TO SAVEPOINT undoes work back to it and leaves the transaction open, so one failed optional step need not discard everything.
Q. Can you roll back a TRUNCATE?
Not in MySQL or Oracle - it is DDL and auto commits. In PostgreSQL and SQL Server, yes.
Isolation and concurrency
Q. Name the isolation levels and what each prevents.
| Level | Dirty read | Non repeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Prevented | Possible | Possible |
| REPEATABLE READ | Prevented | Prevented | Possible by the standard |
| SERIALIZABLE | Prevented | Prevented | Prevented |
Two details that impress: MySQL's default is REPEATABLE READ while PostgreSQL, SQL Server and Oracle default to READ COMMITTED; and InnoDB's REPEATABLE READ also prevents most phantoms using next key locks, which is stricter than the standard requires.
Q. Define the three read phenomena.
- Dirty read - reading uncommitted data that may be rolled back.
- Non repeatable read - the same row read twice gives different values.
- Phantom read - the same query returns extra rows the second time.
Q. What is a deadlock and how do you handle it?
Two transactions each holding a lock the other needs. The engine detects the cycle and rolls one back with error 1213. The correct response is to retry the whole transaction with a short backoff. The prevention is to acquire locks in a consistent order everywhere.
Q. Shared versus exclusive lock?
A shared lock allows other shared locks but blocks writes; an exclusive lock blocks both. SELECT ... FOR UPDATE takes an exclusive lock; FOR SHARE takes a shared one. In InnoDB a plain SELECT takes no lock at all - it reads the MVCC snapshot.
Q. Why can an UPDATE lock the whole table?
InnoDB locks rows through the index it uses. With no usable index on the WHERE column, it scans and locks everything it examines - including non matching rows. An index is a concurrency feature as much as a performance one.
Questions to have an answer ready for
- Design the index for
WHERE customer_id = ? AND status = ? ORDER BY order_date DESC. - Explain why a full table scan is sometimes the correct plan.
- Two sessions transfer money between the same two accounts and deadlock. What went wrong and how do you fix it?
- Which ACID property does a foreign key violation demonstrate?