Transactions in DBMS
Introduction and ACID Properties
In a database system, data is constantly being inserted, updated, and deleted. When multiple operations are performed together, we must ensure that the database remains correct and consistent.
This is achieved using Transactions.
In this article, we will understand:
- What is a transaction
- Why transactions are needed
- Real-world example
- ACID properties
- Transaction states
- COMMIT and ROLLBACK
1. What is a Transaction?
A Transaction is a sequence of one or more database operations treated as a single logical unit of work.
These operations may include:
- INSERT
- UPDATE
- DELETE
- SELECT
A transaction must either:
- Complete entirely, or
- Not executed at all
There is no partial execution allowed.
2. Real-World Example: Bank Transfer
Consider transferring ₹1000 from Account A to Account B.
This involves two operations:
- Deduct ₹1000 from Account A
- Add ₹1000 to Account B
If the system deducts money from Account A but fails before adding it to Account B, the database becomes incorrect.
To prevent this, both operations must execute together as a single transaction.
Either:
- Both succeed
or - Both fail
3. Why Do We Need Transactions?
Transactions ensure:
- Data consistency
- Protection against system failure
- Safe multi-step operations
- Correct database state
Without transactions:
- Partial updates may occur
- Data may become inconsistent
- System crashes may corrupt data
Transactions guarantee reliability.
4. ACID Properties
Every transaction follows four important properties known as ACID.
ACID stands for:
- Atomicity
- Consistency
- Isolation
- Durability
4.1 Atomicity
Atomicity means:
A transaction is indivisible.
It either completes fully or has no effect at all.
In the bank example:
If the system crashes after deducting money but before adding it, the entire transaction must be rolled back.
The database should return to its previous state.
4.2 Consistency
Consistency means:
A transaction must take the database from one valid state to another valid state.
For example:
If the total money in the system was ₹10,000 before the transaction, it should remain ₹10,000 after the transfer.
Constraints, rules, and integrity conditions must always be satisfied.
4.3 Isolation
Isolation means:
Even if multiple transactions execute at the same time, they should not interfere with each other in a way that breaks consistency.
Each transaction should behave as if it were running alone.
A detailed discussion of isolation will be covered under concurrency control.
4.4 Durability
Durability means:
Once a transaction is committed, its changes are permanent.
Even if the system crashes immediately after commit, the changes must not be lost.
This is usually achieved using logs and stable storage.
5. Transaction States
During execution, a transaction passes through different states.
5.1 Active
The transaction is currently executing its operations.
5.2 Partially Committed
All statements have executed successfully, but changes are not yet permanently saved.
5.3 Committed
The transaction has completed successfully.
All changes are permanently stored.
5.4 Failed
An error occurs during execution.
Examples:
- System crash
- Constraint violation
- Hardware failure
5.5 Aborted
The transaction is rolled back.
All changes are undone.
After abortion, the transaction may either:
- Restart
or - Be permanently terminated
6. COMMIT and ROLLBACK
These are transaction control commands.
COMMIT
- Saves all changes permanently.
- Makes the transaction successful.
ROLLBACK
- Undoes all changes made during the transaction.
- Restores the database to its previous state.
Example:
BEGIN TRANSACTION
UPDATE Account SET Balance = Balance - 1000 WHERE ID = 'A';
UPDATE Account SET Balance = Balance + 1000 WHERE ID = 'B';
COMMIT;
If any error occurs before COMMIT, the system executes ROLLBACK.
7. Important Points
- A transaction is a logical unit of work.
- ACID properties guarantee correctness and reliability.
- COMMIT makes changes permanent.
- ROLLBACK cancels changes.
- Transactions protect the database from inconsistencies and system failures.
Summary
A transaction is a sequence of database operations executed as a single unit.
It follows the ACID properties:
- Atomicity ensures all-or-nothing execution.
- Consistency ensures valid database states.
- Isolation ensures safe concurrent execution.
- Durability ensures permanent storage of committed data.