Last modified: October 07, 2023
This article is written in: 🇺🇸
A database transaction is a sequence of operations performed as a single, indivisible unit of work. These operations—such as inserting, updating, or deleting records—are executed together to ensure data integrity and consistency, especially when multiple users or processes access the database at the same time.
1. Initial State:
┌───────────────────────┐ ┌───────────────────────┐
│ Account A │ │ Account B │
│ │ │ │
│ Balance: $100 │ │ Balance: $50 │
└───────────────────────┘ └───────────────────────┘
│ │
│ │
▼ ▼
2. Transaction Initiated:
┌───────────────────────────────────────────────────────────┐
│ Transferring $20 from Account A │
│ to Account B │
└───────────────────────────────────────────────────────────┘
│ │
▼ ▼
3. After Transaction:
┌───────────────────────┐ ┌───────────────────────┐
│ Account A │ │ Account B │
│ │ │ │
│ Balance: $80 │ │ Balance: $70 │
└───────────────────────┘ └───────────────────────┘In the example above, the transaction involves transferring $20 from Account A to Account B. If any part of this transaction fails—say, if the system crashes after debiting Account A but before crediting Account B—the transaction management system ensures that all changes are rolled back, returning the database to its initial state.
After reading the material, you should be able to answer the following questions:
Transactions in databases follow the ACID properties—Atomicity, Consistency, Isolation, and Durability—to ensure reliability, correctness, and robustness, even during errors or system failures.
Atomicity guarantees that a transaction is treated as a single, indivisible unit. Either all operations within the transaction succeed, or none do. If any operation within a transaction fails, all previously executed steps are reversed.
Transaction Example:
Initial State:
Account A: $100
Account B: $50
Transaction Steps:
1. Debit $20 from Account A (Account A: $80)
2. Credit $20 to Account B (Account B: $70)
If Step 2 fails:
Rollback Step 1 → Account A returns to $100Atomicity prevents partial updates, preserving database consistency.
Consistency ensures that transactions transition the database from one valid state to another valid state, following all rules, constraints, and triggers defined in the database.
Transaction Example:
Initial State:
Account A: $100
Account B: $50
After Transaction:
Account A: $80
Account B: $70
Total balance remains consistent:
Before: $150 | After: $150Consistency maintains data integrity throughout transactions.
Isolation ensures concurrent transactions operate independently, without affecting each other. Transactions run as if they are executed sequentially, preventing intermediate states from being visible to other concurrent transactions.
Isolation Example:
Transaction T1:
Reads Account A → Updates Account A
Transaction T2:
Reads Account B → Updates Account B
Even if T1 and T2 execute simultaneously:
T1 ↔ Account A (isolated)
T2 ↔ Account B (isolated)
No interference between transactions.Isolation prevents transactions from causing conflicts or inconsistency.
Durability guarantees that once a transaction is committed, its effects are permanently saved, even if a system failure occurs immediately afterward. The committed state is stored on durable, non-volatile storage.
Durability Example:
Transaction Commit:
→ Changes saved permanently to disk.
System Crash Occurs:
→ Restart System
After Recovery:
→ Committed changes still present.Durability ensures permanent recording of committed transactions.
Once upon a time in a small village, there was a dedicated postman named Tom. Tom's job was to ensure all letters sent from the village post office reached their intended recipients safely and quickly.
One day, Tom received a special request. A villager named Alice wanted to send two important letters—one to her friend Bob and another to her cousin Charlie. Tom learned these letters were part of a surprise birthday celebration. Alice made it clear that both letters had to arrive together; otherwise, the surprise would be spoiled. Understanding this, Tom promised Alice that either both letters would be delivered or neither would leave the post office. This clearly demonstrated the principle of ATOMICITY, where tasks must fully complete or not occur at all.
The village post office had strict rules for handling letters: each must be stamped, sealed, and properly addressed. Tom carefully checked Alice's letters and found one missing a stamp. Knowing the importance of following the rules, he held both letters back until the issue was resolved. This careful approach ensured the postal service's reliability, highlighting CONSISTENCY—making sure every action follows set standards.
While Tom was working on Alice's letters, the post office was busy with other activities. Villagers like Dave were sending packages and receiving letters at the same time. Tom skillfully managed multiple tasks, ensuring each delivery was handled independently. Even though he was multitasking, Alice's letters and Dave's package were treated separately without interfering with each other. This demonstrated the principle of ISOLATION, where tasks carried out simultaneously do not affect each other negatively.
Eventually, Alice fixed the stamp issue. Once both letters were ready, Tom sent them out for delivery. Once dispatched, the action became permanent and couldn't be reversed. Tom recorded the details in the official logbook, ensuring clear documentation. Even if issues like bad weather or mechanical problems arose, the post office had ways to ensure Bob and Charlie would eventually receive their letters. This illustrated DURABILITY, meaning that once an action is complete, it stays permanent and secure, just like committed database transactions.
Managing transactions involves coordinating their execution to uphold the ACID properties. This ensures that the database remains reliable and consistent, even when multiple transactions occur concurrently.