ACID
| Property | Meaning |
|---|---|
| Atomicity | All operations succeed or none do |
| Consistency | Constraints remain valid before and after |
| Isolation | Concurrent transactions do not corrupt each other |
| Durability | Committed changes survive failures |
Concurrency Problems
Dirty read means reading uncommitted data. Lost update means one transaction overwrites another transaction's update. Non-repeatable read means a row changes between two reads. Phantom read means a query returns a different set of rows because another transaction inserted or deleted matching data.
Control Techniques
Databases use locks, timestamps, MVCC, and isolation levels to coordinate concurrent transactions.
Indexes and Triggers
Indexes speed reads by reducing scan work, but they add maintenance cost to inserts, updates, and deletes. Triggers automatically run SQL logic in response to events such as INSERT, UPDATE, or DELETE.
Deadlocks
A deadlock occurs when transactions hold resources and wait for each other in a cycle. Databases detect or prevent deadlocks and typically abort one transaction.
Interview Scenario Practice
Scenario 1: Dirty Read
Scenario: Transaction B reads a value updated by Transaction A, but Transaction A later rolls back.
Strong answer: This is a dirty read. Use an isolation level or database engine behavior that prevents reading uncommitted data.
Why it works: Transactions should not build decisions on data that may disappear.
Common mistake: Confusing dirty reads with phantom reads. Phantom reads are about new matching rows appearing between queries.
Scenario 2: Slow Search Query
Scenario: A user search by email scans millions of rows.
Strong answer: Add an appropriate index on the searched column if the query pattern and selectivity justify it.
Why it works: Indexes reduce scan work for reads, but add storage and write-maintenance cost.
Common mistake: Adding indexes to every column without checking query patterns.
Scenario 3: Deadlock During Updates
Scenario: Two transactions update Account A and Account B in opposite order and both get stuck.
Strong answer: This is a deadlock cycle. Use consistent lock ordering, shorter transactions, retries, and proper indexing to reduce lock duration.
Why it works: Deadlocks happen when transactions hold resources while waiting for each other.
Common mistake: Assuming deadlocks only happen when the database is broken. They are often caused by application update order.