Transactions and Concurrency Control

Transactions ensure database consistency and handle concurrent access.

Transaction

Transaction is a sequence of operations that must execute atomically.

Properties:

All operations succeed (commit) or all fail (rollback)
Database moves from one consistent state to another

Transaction States

Active: Transaction executing
Partially Committed: All operations completed, but not yet committed
Committed: Changes made permanent
Failed: Cannot proceed
Aborted: Rolled back, database restored

ACID Properties

1. Atomicity

All or Nothing: Either all operations execute or none.

Implementation: Logging and recovery

2. Consistency

Valid State Transitions: Database remains in consistent state.

Implementation: Integrity constraints

3. Isolation

Concurrent Execution: Transactions don't interfere with each other.

Implementation: Concurrency control

4. Durability

Permanent Changes: Committed changes persist even after system failure.

Implementation: Logging to stable storage

Concurrency Control

Concurrency Control manages simultaneous transaction execution.

Problems Without Concurrency Control

1. Lost Update

Problem: Two transactions update same data, one overwrites other.

Example:

T1: Read balance = 100, Write balance = 150
T2: Read balance = 100, Write balance = 200
Result: T1's update lost

2. Dirty Read

Problem: Read uncommitted data.

Example:

T1: Write balance = 150 (not committed)
T2: Read balance = 150
T1: Rollback
T2: Read incorrect data

3. Unrepeatable Read

Problem: Same read gives different results.

Example:

T1: Read balance = 100
T2: Update balance = 200, Commit
T1: Read balance = 200 (different!)

4. Phantom Read

Problem: New rows appear during transaction.

Example:

T1: Count students = 100
T2: Insert new student, Commit
T1: Count students = 101 (phantom row)

Locking

Locking prevents concurrent access conflicts.

Lock Types

1. Shared Lock (Read Lock)

Multiple transactions can hold
Allows reading
Prevents writing

Notation: S-lock

2. Exclusive Lock (Write Lock)

Only one transaction can hold
Allows reading and writing
Prevents other locks

Notation: X-lock

Lock Compatibility Matrix

	S-lock	X-lock
S-lock	✓	✗
X-lock	✗	✗

Two-Phase Locking (2PL)

Phase 1: Growing Phase

Acquire locks
Cannot release locks

Phase 2: Shrinking Phase

Release locks
Cannot acquire locks

Strict 2PL:

Release all locks only at commit/abort
Prevents cascading rollbacks

Deadlock

Deadlock occurs when transactions wait for each other.

Example:

T1 holds lock on A, waits for B
T2 holds lock on B, waits for A
Both wait forever

Deadlock Handling:

Prevention: Order resources, timeout
Detection: Wait-for graph, detect cycles
Recovery: Abort one transaction

Serializability

Serial Schedule: Transactions execute one after another.

Serializable Schedule: Equivalent to some serial schedule.

Conflict Serializability:

Two operations conflict if:
On same data item
At least one is write
Swap non-conflicting operations

Precedence Graph:

Node: Transaction
Edge: T1 → T2 if T1's operation precedes conflicting T2's operation
Acyclic graph → Conflict serializable

Isolation Levels

1. Read Uncommitted

Lowest isolation
Allows dirty reads

2. Read Committed

Prevents dirty reads
Allows unrepeatable reads

3. Repeatable Read

Prevents dirty and unrepeatable reads
Allows phantom reads

4. Serializable

Highest isolation
Prevents all problems

GATE CS Important Points

ACID Properties: Understand all four properties
Concurrency Problems: Lost update, dirty read, unrepeatable read, phantom read
Locking: Shared locks, exclusive locks, 2PL
Deadlock: Detection and handling
Serializability: Conflict serializability, precedence graph

Practice Tips

Identify Problems: Recognize concurrency problems
Lock Protocols: Understand 2PL and strict 2PL
Deadlock Detection: Draw wait-for graphs
Serializability: Check conflict serializability
Previous Year Questions: Solve GATE transaction questions

Transactions and Concurrency Control