In a real-world application, thousands of users might read and write to the same database simultaneously. Transactions and concurrency control ensure that these operations happen predictably without corrupting the data.
The Role of Transactions
A transaction is a single logical unit of work. Consider a bank transfer:
- Deduct money from Account A.
- Add money to Account B.
If step 2 fails, step 1 must also be undone. A transaction ensures “all or nothing” execution.
Module Roadmap
| Topic | Description | Interview Weight |
|---|---|---|
| ACID Properties | Atomicity, Consistency, Isolation, Durability | Very High |
| Isolation Levels | Read Uncommitted to Serializable | Very High |
| Locks & Deadlocks | Shared/Exclusive locks, Deadlock detection | High |
Why This Matters
- Reliability: Prevents data corruption from partial updates.
- Consistency: Ensures database invariants are always maintained.
- Performance: Choosing the right isolation level trades consistency for speed.
- Debugging: Understanding locks helps you diagnose “hung” queries and deadlocks.
ACID at a Glance
| Property | Meaning |
|---|---|
| Atomicity | All operations complete or none do |
| Consistency | Data is always valid |
| Isolation | Concurrent transactions don’t interfere |
| Durability | Committed data survives crashes |
Q: Define a Database Transaction. A: A transaction is a sequence of one or more SQL
operations treated as a single unit. It must either be completed entirely (COMMIT) or not at all (ROLLBACK).
Q: What is a Race Condition in DB? A: A race condition occurs when two or more
transactions try to update the same data at the same time, and the final result depends on which transaction finished first, often leading to incorrect data.
Q: How do you start and end a transaction? A: Use BEGIN or START TRANSACTION to
begin, COMMIT to make changes permanent, and ROLLBACK to undo all changes since the
transaction started.
Premium Content
Unlock Transactions & Concurrency Overview and all premium lessons with a subscription.
From ₹199.99/year — See plans