What is a Transaction?
A transaction is a sequence of one or more SQL operations performed as a single logical unit of work. A transaction is either completed successfully or rolled back completely.
Characteristics of a Transaction
- Logical unit of work
- Maintains database consistency
- Can be committed or rolled back
- Supports concurrent users
ACID Properties
- Atomicity – All operations execute completely or none execute.
- Consistency – Database remains valid before and after a transaction.
- Isolation – Transactions do not interfere with each other.
- Durability – Committed data is permanently stored.
Concurrency Control
Concurrency Control ensures that multiple users can access the database simultaneously without causing data inconsistency.
Concurrency Problems
- Lost Update
- Dirty Read
- Non-Repeatable Read
- Phantom Read
Locking
\Locking is a technique used to prevent conflicts between simultaneous transactions.
- Shared Lock (Read Lock)
- Exclusive Lock (Write Lock)
Two-Phase Locking (2PL)
Two-Phase Locking is a protocol that ensures serializability by dividing transaction execution into:
- Growing Phase – Acquire locks only.
- Shrinking Phase – Release locks only.
Transaction States
- Active
- Partially Committed
- Committed
- Failed
- Aborted
Advantages
- Maintains data integrity
- Supports multi-user databases
- Ensures reliable transactions
- Reduces data conflicts
Exam Points
- Transaction = Logical unit of work
- ACID = Atomicity, Consistency, Isolation, Durability
- Shared Lock = Read
- Exclusive Lock = Write
- 2PL = Growing Phase + Shrinking Phase