Transaction Management
Transaction
A transaction is a sequence of operations performed as a single logical unit of work. Example: Transfer ₹1000 from Account A to B
Characteristics
- Must be completed fully or not at all
- Maintains database consistency
Properties of Transactions (ACID)
ACID Properties Table
| Property | Description |
|---|---|
| Atomicity | All or nothing execution |
| Consistency | DB remains valid |
| Isolation | No interference |
| Durability | Permanent changes |
Explanation
- Atomicity: If one operation fails → entire transaction fails
- Consistency: Database moves from one valid state to another
- Isolation:Transactions execute independently
- Durability: Changes saved permanently
ACID Diagram
Transaction
|
-------------------------
| A | C | I | D |
-------------------------
Types of Transactions
Types Table
| Type | Description |
|---|---|
| Read-only | Only reads data |
| Read-write | Reads & updates |
| Distributed | Executes on multiple sites |
| Nested | Sub-transactions |
| Long-lived | Runs for long time |
Distributed Concurrency Control
Ensures multiple transactions execute correctly across distributed systems.
Goals
- Maintain consistency
- Avoid conflicts
- Ensure serializability
Serializability
Execution of concurrent transactions should be equivalent to some serial order.
Types
| Type | Description |
|---|---|
| Conflict Serializability | Based on conflicting operations |
| View Serializability | Based on the final results |
Example
T1 → Read(A) → Write(A)
T2 → Read(A) → Write(A)
Must ensure correct order.
Concurrency Control Mechanisms
1. Lock-Based Protocol
Types of Locks
| Lock | Use |
|---|---|
| Shared (S) | Read |
| Exclusive (X) | Write |
Two-Phase Locking (2PL)
Phases
Growing Phase → Acquire locks
Shrinking Phase → Release locks
Advantages
- Ensures serializability
Disadvantages
- Deadlocks possible
Timestamp-Based Concurrency Control
Each transaction gets a timestamp.
Rules
- Older transactions get priority
- Prevents conflicts
Working
T1 (TS=1) → Execute first
T2 (TS=2) → Execute later
Advantages
- No deadlocks
Disadvantages
- Possible rollback
Optimistic Concurrency Control (OCC)
Assumes conflicts are rare and checks at commit time.
Phases
Read Phase
↓
Validation Phase
↓
Write Phase
Advantages
- High performance
- No locking
Disadvantages
- High rollback in conflicts
Distributed Concurrency Control Algorithms
1. Primary Copy Method
- One site controls updates
2. Voting Protocol
Idea
- Each site votes for transaction
3. Distributed 2PL
- Locks managed across sites
Deadlock Management
A situation where transactions wait indefinitely for each other.
Deadlock Example
T1 → locks A → waits for B
T2 → locks B → waits for A
Deadlock Handling Techniques
1. Deadlock Prevention
Methods:
- Lock ordering
- Timestamp ordering
2. Deadlock Avoidance
- Avoid unsafe states
3. Deadlock Detection
Wait-for Graph
T1 → T2 → T3 → T1 (Cycle = Deadlock)
4. Deadlock Recovery
- Abort one transaction
- Rollback
Techniques Comparison
| Technique | Advantage | Disadvantage |
|---|---|---|
| Prevention | No deadlock | Low concurrency |
| Detection | Flexible | Overhead |
| Avoidance | Safe | Complex |
Distributed Deadlock Management
Types
| Type | Description |
|---|---|
| Centralized | Single coordinator |
| Distributed | Each site manages |
| Hierarchical | Multi-level control |
Combined Workflow
Transaction Processing Flow
Transaction Start
↓
Concurrency Control
↓
Execution
↓
Commit / Rollback
Important Exam Questions
Short Questions
- Define transaction
- What is serializability?
- What is deadlock?
Long Question
- Explain ACID properties
- Describe concurrency control techniques
- Explain deadlock handling methods
Case-Based Questions
- Explain 2PL with an example
- Solve deadlock using the wait-for graph
- Compare timestamps and locking
Final Summary
- Transaction → unit of work
- ACID → ensures reliability
- Concurrency control → correctness
- 2PL, Timestamp, OCC → key methods
- Deadlock → major issue in DDBMS