🍫 Introduction: The Playground Swap
Imagine you are on the playground and want to trade your Snickers bar for your friend's Skittles bag:
- The Risky Way: You hand your friend the Snickers bar. But before they hand you the Skittles, the school bell rings, and they run inside! You lost your chocolate and got nothing.
- The Safe Way (Transaction): Both of you hold out your candy. You count: "One, Two, Three, Swap!" You both exchange candies at the exact same moment. If either of you lets go early, the swap is cancelled, and both keep their original candy.
In databases, this swap is called a Transaction!
A transaction is a group of database commands that are executed as a single unit of work. Either all the commands succeed together, or none of them do (the database rolls back to its original state).
🛡️ ACID Properties: The Code of Trust
Every transaction follows the ACID rules to guarantee safety:
+--------------------------------+
| BEGIN TRANSACTION |
+--------------------------------+
|
Execute SQL Writes
- Deduct from Account A
- Deposit into Account B
|
(Any statement failed?)
/ YES NO
/ v v
[ROLLBACK] [COMMIT]
(All changes erased) (All changes saved)
- Atomicity (All-or-Nothing): A transaction is like an atom—it cannot be split. If a transaction has 10 updates, and step 9 fails, all previous 8 steps are erased (
ROLLBACK). - Consistency (Valid State): The database must move from one valid state to another, obeying all constraints (like primary keys and checks).
- Isolation (No Peeking): If multiple users are updating the database at the same time, their transactions are isolated from each other. They cannot see each other's half-finished steps.
- Durability (Permanence): Once a transaction is saved (
COMMIT), it is written to non-volatile disk. Even if the server loses power or crashes a millisecond later, your data is safe.
💻 Code Examples
Let's write a transaction to transfer money between two bank accounts.
SQL Transaction Script
-- Start the transaction block
BEGIN TRANSACTION;
-- Step 1: Deduct $50 from Alice
UPDATE bank_accounts
SET balance = balance - 50
WHERE owner = 'Alice';
-- Step 2: Deposit $50 into Bob
UPDATE bank_accounts
SET balance = balance + 50
WHERE owner = 'Bob';
-- If both succeeded, save the changes permanently!
COMMIT;
-- If anything failed, discard changes:
-- ROLLBACK;Multi-Language Execution
⚠️ Common Mistakes
1. Long-Running Transactions
Leaving a transaction open while waiting for user interaction (e.g. keeping a transaction open while waiting for the user to click a button). This locks the table rows and blocks other database users, freezing the system!
2. Nesting Transactions
Trying to call BEGIN TRANSACTION inside another open transaction. Most databases don't support nested transactions natively and will throw syntax or runtime errors. Use Savepoints instead.
🔍 Interview Corner
Q1: What is the meaning of ACID in database systems?
- A - Atomicity: Complete transaction execution or no execution at all (All-or-Nothing).
- C - Consistency: Transactions move the database from one valid state to another, preserving all schemas and checks.
- I - Isolation: Concurrent transaction executions do not interfere or read uncommitted edits of each other.
- D - Durability: Once committed, changes are written to disk and survive server power failures.
Q2: What are transaction isolation levels?
Isolation levels control the balance between consistency and performance. The four standard levels are:
- Read Uncommitted: Fast, but allows dirty reads (reading unsaved data).
- Read Committed: Prevents dirty reads (only reads saved data).
- Repeatable Read: Guarantees that reading the same row twice inside a transaction returns the identical values.
- Serializable: Strictly orders transaction executions. Slowest but safest (prevents phantom reads).
📝 Summary
- A Transaction groups multiple database updates into a single atomic block.
- Transations are All-or-Nothing: either all succeed (
COMMIT) or all fail (ROLLBACK). - Transactions adhere to the ACID properties to guarantee data safety.