Mental Model
A transaction is an all-or-nothing wrapper around a group of database operations. It ensures that if your server crashes halfway through a bank transfer, money doesn't vanish into the void.
When building financial systems, billing engines, or inventory trackers, you cannot afford partial updates. MySQL provides ACID transactions through its InnoDB storage engine to guarantee data integrity.
1. The ACID Guarantees
- Atomicity: "All or Nothing." If a transaction has 5 steps and step 4 fails, steps 1, 2, and 3 are rolled back as if they never happened.
- Consistency: A transaction can only bring the database from one valid state to another, respecting all constraints (e.g., Foreign Keys, UNIQUE constraints).
- Isolation: Concurrent transactions execute as if they are the only transaction running on the system (depending on the Isolation Level).
- Durability: Once a transaction is committed, it will survive a total power loss. (Achieved via the Write-Ahead Log or Redo Log in InnoDB).
2. Managing a Transaction
By default, MySQL runs in autocommit = 1 mode (every single query is its own transaction). To group queries, you must explicitly start a transaction.
START TRANSACTION;
-- Withdraw from Account A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Deposit to Account B
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If everything went well:
COMMIT;
-- If something failed (handled in application code):
-- ROLLBACK;
3. Isolation Levels and Race Conditions
If two users try to read and write the exact same data at the exact same time, what happens? MySQL offers 4 isolation levels to manage this trade-off.
- Read Uncommitted (Dirty Reads): A transaction can see uncommitted data from other transactions. (Extremely fast, highly dangerous. Never use for finance).
- Read Committed (Non-Repeatable Reads): You only see committed data. However, if you read the same row twice in a single transaction, another transaction might have updated it in between, giving you a different result.
- Repeatable Read (Default in InnoDB): If you read a row at the start of a transaction, it will look exactly the same if you read it again at the end, regardless of what other transactions have committed.
- Serializable (Phantom Reads Prevented): The strictest level. It effectively forces transactions to execute sequentially. It guarantees perfect correctness but severely bottlenecks performance via heavy locking.
4. Locking: Row-Level vs Table-Level
- MyISAM (Legacy): Uses Table-Level Locking. If one user updates a row, the entire table is locked. No one else can write to it until the update finishes.
- InnoDB (Modern): Uses Row-Level Locking. If User A updates row 1, User B can simultaneously update row 2. This is why InnoDB is scalable for high-concurrency applications.
Explicit Locking (SELECT ... FOR UPDATE)
Sometimes you need to manually lock a row so no one else can touch it until your transaction finishes. This is called a Pessimistic Lock.
START TRANSACTION;
-- Lock the row so no other transaction can modify it or acquire a lock on it
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- ... app logic ...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- Lock is released
Practice Question
Scenario: You are building a ticket booking system. Two users click "Book Seat A1" at the exact same millisecond. Question: How do you prevent double-booking at the database level?
View Answer
You must use a transaction with a Pessimistic Lock (`SELECT ... FOR UPDATE`).Thread 1 starts a transaction and runs SELECT status FROM seats WHERE id = 'A1' FOR UPDATE.
Thread 2 tries to run the same query but is blocked and put in a wait queue because Thread 1 holds the lock.
Thread 1 sees the seat is available, updates it to 'BOOKED', and COMMITs.
Thread 2 now acquires the lock, sees the seat is 'BOOKED', and fails gracefully.
Technical Trade-offs: Architectural Decision
| Strategy | Scalability | Complexity | Operational Cost | Performance |
|---|---|---|---|---|
| Monolithic | Low | Low | Low | Fast (Local) |
| Microservices | Very High | High | High | Slower (Network) |
| Serverless | Infinite | Medium | Variable | Variable (Cold Starts) |
Production Readiness Checklist
Before deploying this architecture to a production environment, ensure the following Staff-level criteria are met:
- High Availability: Have we eliminated single points of failure across all layers?
- Observability: Are we exporting structured JSON logs, custom Prometheus metrics, and OpenTelemetry traces?
- Circuit Breaking: Do all synchronous service-to-service calls have timeouts and fallbacks (e.g., via Resilience4j)?
- Idempotency: Can our APIs handle retries safely without causing duplicate side effects?
- Backpressure: Does the system gracefully degrade or return HTTP 429 when resources are saturated?
Verbal Interview Script
Interviewer: "What is a Deadlock, and how does InnoDB handle it?"
Candidate: "A deadlock occurs when Transaction A holds Lock 1 and waits for Lock 2, while Transaction B holds Lock 2 and waits for Lock 1. They are stuck in a circular wait forever. InnoDB detects deadlocks automatically by looking for cycles in its wait-for graph. When it finds one, it immediately rolls back the transaction that has done the least amount of work (the 'victim'), breaking the cycle and allowing the other transaction to proceed. As application developers, we must catch this rollback exception and safely retry the transaction."