Transaction Manager

Definition

The Transaction Manager is the DBMS subsystem responsible for ensuring that groups of operations (transactions) execute with the ACID properties — Atomicity, Consistency, Isolation, Durability — even when many users run concurrently or the system crashes mid-operation.

How It Works — Example

Consider a transaction transferring marks records: BEGIN; UPDATE accounts SET balance = balance - 500 WHERE id = 1; UPDATE accounts SET balance = balance + 500 WHERE id = 2; COMMIT;. The Transaction Manager guarantees:

• Atomicity — either both UPDATE statements take effect, or neither does; there is no state where money has left account 1 but never arrived at account 2.
• Consistency — the database moves from one valid state to another, respecting all constraints (e.g., a CHECK that balance cannot go negative).
• Isolation — other concurrent transactions do not see the partially-updated balances mid-transfer; they see either the state before or the state after, never in between. This is achieved through locking or multi-version concurrency control (MVCC).
• Durability — once COMMIT succeeds, the change survives even a crash immediately afterward, typically guaranteed via write-ahead logging (WAL), where the change is durably logged before being confirmed to the client.

A course-registration example: two students try to register for the last available seat in a course at the exact same instant. The Transaction Manager ensures that only one of the two transactions can successfully decrement the seat counter — the second either blocks and retries, or fails cleanly with an error — rather than both succeeding and overselling the course.

Edge Cases and Pitfalls

• Long-running transactions cause lock contention or bloat. A transaction that holds locks for a long time can block other users' transactions from proceeding; under MVCC, long-running transactions can also cause old row versions to pile up, bloating storage until they are cleaned up.
• Choosing the wrong isolation level has real consequences. Using a weak isolation level like READ UNCOMMITTED for a use case that needs strong guarantees risks "dirty reads" — reading data from another transaction that later gets rolled back and never actually existed. Conversely, using the strictest level (SERIALIZABLE) everywhere, even where it isn't needed, can needlessly hurt throughput.
• Forgetting to commit or roll back leaves resources held. An application that opens a transaction (BEGIN) but never explicitly commits or rolls it back can silently hold locks or resources far longer than intended, degrading concurrency for everyone else.

Key Takeaways / Interview Q&A

Q: What do the four letters in ACID stand for, and what does each guarantee? A: Atomicity (all-or-nothing execution), Consistency (valid state to valid state), Isolation (concurrent transactions don't see each other's partial work), Durability (committed changes survive crashes).

Q: How does the Transaction Manager prevent two students from both registering for the last seat in a course? A: By ensuring only one transaction can successfully complete the seat-decrement operation — via locking or MVCC — while the other is blocked, retried, or cleanly failed.

Q: What is the risk of using READ UNCOMMITTED isolation for a workload that actually needs strong guarantees? A: It permits dirty reads — a transaction may read data written by another transaction that is later rolled back, meaning it read data that, from the database's final perspective, never actually existed.