UPES - University of Petroleum and Energy Studies
School of Computer Science | Cloud & Software Operations Cluster
M.Tech - Database Systems | First Semester

Lecture 11: Concurrency Control Protocols & Deadlocks

Unit II: Transaction Management

Instructor Information

Dr. Mohsin Dar

Assistant Professor

Cloud & Software Operations Cluster

Slide 1 of 20

📋 Today's Lecture Agenda

Part I: Concurrency Control Protocols

  • Introduction to Locking Mechanisms
  • Basic Two-Phase Locking (2PL)
  • Conservative 2PL
  • Rigorous 2PL
  • Strict 2PL
  • Multi-Version Concurrency Control (MVCC)
  • Timestamp-Based Protocols
  • Thomas Write Rule

Part II: Deadlock Management

  • Deadlock Detection
  • Deadlock Prevention
  • Deadlock Avoidance
Slide 3 of 20

🔐 Concurrency Control: The Foundation

Why Do We Need Concurrency Control?

Concurrency control protocols ensure that concurrent transactions maintain database consistency while maximizing system throughput.

Key Objectives:

  • Isolation: Transactions should not interfere with each other
  • Consistency: Database remains in a valid state
  • Serializability: Concurrent execution equivalent to serial execution
  • Performance: Maximize concurrent transaction processing

Lock Types

🔓 Shared Lock (S)

Read-only access

Multiple transactions can hold shared locks on the same data item simultaneously.

Notation: S(X)

🔒 Exclusive Lock (X)

Read and Write access

Only one transaction can hold an exclusive lock on a data item at a time.

Notation: X(X)

Slide 4 of 20

🔄 Basic Two-Phase Locking (2PL)

Protocol Definition

Basic 2PL divides transaction execution into two distinct phases:

  1. Growing Phase: Transaction can acquire locks but cannot release any locks
  2. Shrinking Phase: Transaction can release locks but cannot acquire any new locks

Key Characteristics

  • Lock Point: The point where transaction acquires its last lock
  • Guarantees: Conflict Serializability
  • Issues: May suffer from cascading rollbacks, deadlocks possible

Example: Basic 2PL Schedule

T1: S(A), Read(A), X(B), Write(B), Unlock(A), Unlock(B) T2: S(B), Read(B), X(A), Write(A), Unlock(B), Unlock(A)

Growing Phase T1: S(A), X(B)

Shrinking Phase T1: Unlock(A), Unlock(B)

Important: Basic 2PL ensures serializability but doesn't guarantee recoverability or avoid cascading rollbacks!
Slide 5 of 20

🛡️ Conservative Two-Phase Locking

Protocol Definition

Conservative 2PL requires a transaction to lock ALL data items it needs BEFORE beginning execution.

  • Also known as Static 2PL
  • Transaction must predeclare its read and write sets
  • If all locks cannot be obtained, transaction waits

Advantages

✅ Deadlock-Free

No circular wait can occur since all locks are acquired at once

✅ Simple Logic

Easy to implement and reason about

Disadvantages

  • Difficult to Predict: Transaction must know all data items in advance
  • Reduced Concurrency: Locks held for entire transaction duration
  • Lock Overhead: May lock more data than actually needed

Example: Conservative 2PL

T1: [Request all locks: S(A), X(B), X(C)] If all granted → Execute: Read(A), Write(B), Write(C) Finally → Release: Unlock(A), Unlock(B), Unlock(C)
Slide 6 of 20

🔐 Strict Two-Phase Locking

Protocol Definition

Strict 2PL holds all EXCLUSIVE (X) locks until the transaction commits or aborts.

  • Shared locks can be released during shrinking phase
  • Exclusive locks held until transaction end
  • Most commonly used variant in commercial databases

Why Strict 2PL?

Problem with Basic 2PL: If T1 writes X and releases lock, then T2 reads X, and T1 aborts → T2 must abort (Cascading Rollback)
Solution with Strict 2PL: Hold X locks until commit → No transaction can read uncommitted data → Cascadeless Schedule!

Properties

  • ✅ Guarantees Cascadeless Schedules
  • ✅ Prevents Dirty Reads
  • ✅ Easier Recovery mechanism
  • ⚠️ Deadlocks still possible

Example Comparison

Time Basic 2PL (Cascading) Strict 2PL (Cascadeless)
t1 T1: X(A), Write(A) T1: X(A), Write(A)
t2 T1: Unlock(A) T1: (holds X(A))
t3 T2: S(A), Read(A) ❌ T2: WAITS
t4 T1: ABORT → T2 must abort T1: COMMIT, Unlock(A) ✅
Slide 7 of 20

💪 Rigorous Two-Phase Locking

Protocol Definition

Rigorous 2PL holds ALL locks (both Shared and Exclusive) until the transaction commits or aborts.

  • No locks are released until transaction completion
  • Strictest variant of 2PL
  • Most restrictive but safest protocol

Comparison: Strict vs Rigorous 2PL

Aspect Strict 2PL Rigorous 2PL
Exclusive Locks Held until commit/abort Held until commit/abort
Shared Locks Can be released early Held until commit/abort
Concurrency Higher Lower
Recoverability Cascadeless Strict (No cascading at all)

Properties

  • ✅ Guarantees Strict Schedules
  • ✅ Simplest recovery mechanism
  • ✅ No uncommitted data ever visible
  • ⚠️ Lowest concurrency among 2PL variants
  • ⚠️ Deadlocks still possible
Trade-off: Rigorous 2PL sacrifices concurrency for maximum safety and simplicity in recovery.
Slide 8 of 20

📊 Two-Phase Locking: Complete Comparison

Protocol Growing Phase Shrinking Phase Deadlock-Free? Recoverability
Basic 2PL Acquire locks Release locks anytime ❌ No May have cascading rollbacks
Conservative 2PL All locks at start Release anytime ✅ Yes Depends on when locks released
Strict 2PL Acquire locks X locks at commit only ❌ No Cascadeless
Rigorous 2PL Acquire locks All locks at commit only ❌ No Strict (Strongest)

Usage in Real Systems

MySQL InnoDB

Uses Strict 2PL with deadlock detection

PostgreSQL

Uses variations of Strict 2PL with MVCC

Oracle

Primarily uses MVCC, not 2PL

SQL Server

Uses Strict 2PL with various isolation levels

Slide 9 of 20

⏰ Timestamp Ordering Protocol

Basic Principle

Each transaction is assigned a unique timestamp when it begins. The protocol ensures that operations execute in timestamp order.

  • TS(Ti): Timestamp of transaction Ti
  • Read-TS(X): Largest timestamp of any transaction that read X
  • Write-TS(X): Largest timestamp of any transaction that wrote X

Protocol Rules

For Read Operation: Ti reads X

  • If TS(Ti) < Write-TS(X): ❌ REJECT & ROLLBACK Ti
    Ti is trying to read a value that was written by a younger transaction
  • If TS(Ti) ≥ Write-TS(X): ✅ ALLOW read
    Update Read-TS(X) = max(Read-TS(X), TS(Ti))

For Write Operation: Ti writes X

  • If TS(Ti) < Read-TS(X): ❌ REJECT & ROLLBACK Ti
    Ti is trying to write a value that was already read by a younger transaction
  • If TS(Ti) < Write-TS(X): ❌ REJECT & ROLLBACK Ti
    Obsolete write attempt
  • Otherwise: ✅ ALLOW write
    Update Write-TS(X) = TS(Ti)
Key Feature: Timestamp ordering is deadlock-free because transactions never wait for locks. However, it may cause more rollbacks.
Slide 11 of 20

⏰ Timestamp Ordering: Complete Example

Scenario Setup

Initial State: Data items A and B

Read-TS(A) = 0, Write-TS(A) = 0

Read-TS(B) = 0, Write-TS(B) = 0

Time T1 (TS=1) T2 (TS=2) T3 (TS=3) Status
t1 Read(A) - - ✅ R-TS(A)=1, W-TS(A)=0
t2 - Write(A) - ✅ W-TS(A)=2
t3 Write(A) - - ❌ TS(T1)=1 < W-TS(A)=2 → ROLLBACK T1
t4 - - Read(A) ✅ R-TS(A)=3, W-TS(A)=2
t5 - Write(B) - ✅ W-TS(B)=2
Result: T1 is rolled back because it tried to write a value (Write(A)) that was already overwritten by a younger transaction T2. T2 and T3 complete successfully.

Timestamp Protocol Characteristics

  • ✅ Deadlock-Free: No waiting involved
  • ✅ Serializable: Equivalent to serial order by timestamps
  • ⚠️ High Rollback Rate: Conflicts cause immediate rollbacks
  • ⚠️ Starvation Possible: Old transactions might keep getting rolled back
Slide 12 of 20

✍️ Thomas Write Rule

Optimization for Timestamp Ordering

Thomas Write Rule is a modification to the basic timestamp ordering protocol that ignores obsolete writes instead of rolling back transactions.

Modified Write Rule

When Ti wants to Write(X):

  • If TS(Ti) < Read-TS(X): ❌ REJECT & ROLLBACK Ti
    (Same as basic protocol)
  • If TS(Ti) < Write-TS(X):
    🔄 IGNORE the write (don't rollback!)
    This write is obsolete - a later transaction already wrote X
  • Otherwise: ✅ ALLOW write
    Update Write-TS(X) = TS(Ti)

Example: Thomas Write Rule in Action

Operation Basic Timestamp Thomas Write Rule
T1(TS=1): Write(X) ✅ W-TS(X) = 1 ✅ W-TS(X) = 1
T3(TS=3): Write(X) ✅ W-TS(X) = 3 ✅ W-TS(X) = 3
T2(TS=2): Write(X) ❌ ROLLBACK T2
(TS(T2)=2 < W-TS(X)=3)		 ✅ IGNORE write
T2 continues

Why Does This Work?

Reasoning: If T2 writes X but T3 (with later timestamp) already wrote X, then in the equivalent serial schedule, T3's write comes after T2's write anyway. So T2's write would be overwritten and can be safely ignored!

Benefits

  • ✅ Fewer transaction rollbacks
  • ✅ Better performance than basic timestamp ordering
  • ✅ Still guarantees conflict serializability
  • ✅ Allows View Serializable schedules (not just conflict serializable)
Slide 13 of 20

🔄 Multi-Version Concurrency Control (MVCC)

Core Concept

MVCC maintains multiple versions of each data item to allow greater concurrency without locking for reads.

  • Each write creates a NEW version of the data item
  • Each transaction sees a consistent snapshot
  • Readers don't block writers, writers don't block readers

How MVCC Works

Version Management

Each version has metadata:

  • Write-Timestamp (WTS): When version was created
  • Read-Timestamp (RTS): Latest transaction that read it
  • Transaction ID: Which transaction created it

MVCC Read Rule

When transaction Ti with timestamp TS(Ti) wants to read X:

Select the version Xk where: WTS(Xk) is the largest timestamp ≤ TS(Ti) AND the transaction that created Xk has committed

This ensures Ti reads the most recent committed version visible to it.

MVCC Write Rule

When transaction Ti with timestamp TS(Ti) wants to write X:

  • If RTS(X) > TS(Ti): ❌ REJECT - a younger transaction already read X
  • Otherwise: ✅ Create a new version Xnew with WTS(Xnew) = TS(Ti)
Key Advantage: Read operations never wait! They always find an appropriate version to read.
Slide 10 of 20

📊 Concurrency Control Protocols: Complete Comparison

Protocol Deadlock-Free? Conflict Serializability View Serializability Recoverability Cascadelessness Rollbacks Concurrency Best Use Case
Basic 2PL ❌ ✅ ✅ ❌ ❌ Medium Medium General purpose with deadlock detection
Conservative 2PL ✅ ✅ ✅ ❌ ❌ Low Low When all data items known upfront
Strict 2PL ❌ ✅ ✅ ✅ ✅ Medium Medium-High Most commercial databases
Rigorous 2PL ❌ ✅ ✅ ✅ ✅ (Strict) Low Low When simplicity > performance
MVCC ✅ ✅ ✅ ✅ ✅ Low Very High Read-heavy workloads, PostgreSQL, Oracle
Timestamp ✅ ✅ ✅ ❌ ❌ High High Distributed systems, low contention
Thomas Write ✅ ❌ ✅ ❌ ❌ Medium High Optimization for timestamp ordering

Lock-Based Protocols

Pros: Well understood, predictable

Cons: Deadlocks, lower concurrency

Timestamp-Based

Pros: Deadlock-free, high concurrency

Cons: More rollbacks, starvation risk

MVCC

Pros: Highest concurrency, no read locks

Cons: Storage overhead, complexity

Slide 14 of 20

💀 Deadlocks in Database Systems

What is a Deadlock?

A deadlock occurs when two or more transactions are waiting indefinitely for each other to release locks, creating a circular wait condition.

Classic Deadlock Example

The Dining Philosophers Problem (Database Version)

Time Transaction T1 Transaction T2 Status
t1 X(A) ✅ - T1 holds lock on A
t2 - X(B) ✅ T2 holds lock on B
t3 X(B) ⏳ - T1 waits for B (held by T2)
t4 - X(A) ⏳ T2 waits for A (held by T1)
Result 💀 DEADLOCK: Both transactions wait forever!

Necessary Conditions for Deadlock

1. Mutual Exclusion

Resources cannot be shared (e.g., exclusive locks)

2. Hold and Wait

Transactions hold resources while waiting for others

3. No Preemption

Resources cannot be forcibly taken from transactions

4. Circular Wait

A circular chain of transactions waiting for each other

All four conditions must be present for a deadlock to occur. Breaking any one condition prevents deadlocks!
Slide 15 of 20

🔍 Deadlock Detection

Detection Strategy

Allow deadlocks to occur, but detect and resolve them when they happen.

  • Build a Wait-For Graph (WFG)
  • Check for cycles periodically
  • If cycle found → Deadlock exists!
  • Select a victim transaction to rollback

Wait-For Graph (WFG)

Construction Rules:

  • Nodes: Each transaction is a node
  • Edges: Ti → Tj if Ti is waiting for a lock held by Tj
  • Cycle: Indicates deadlock

Example: Deadlock Detection

Scenario:

T1 holds X(A), wants X(B) T2 holds X(B), wants X(C) T3 holds X(C), wants X(A)

Wait-For Graph:

T1 → T2 ↑ ↓ T3 ← T2 → T3 Cycle detected: T1 → T2 → T3 → T1

💀 DEADLOCK DETECTED!

Victim Selection Criteria

  • 📊 Age: Rollback youngest transaction (minimize wasted work)
  • ⚖️ Work Done: Rollback transaction with least progress
  • 🔄 Rollback Count: Avoid starvation - don't repeatedly pick same transaction
  • 🔒 Lock Count: Rollback transaction holding fewest locks
Trade-off: Detection frequency vs. overhead. Check too often = waste CPU. Check too rarely = long waits.
Slide 16 of 20

🛡️ Deadlock Prevention

Prevention Strategy

Design protocols that ensure at least one of the four necessary conditions cannot occur.

Prevention Techniques

1. Prevent Mutual Exclusion

Approach: Make all resources shareable

Reality: ❌ Not practical for databases - writes need exclusive access

2. Prevent Hold and Wait

Approach: Require transactions to request all locks at once

Implementation: Conservative 2PL!

  • ✅ Guarantees no deadlock
  • ⚠️ Reduces concurrency
  • ⚠️ Requires knowing all data items in advance

3. Allow Preemption

Approach: If Ti waits for Tj, abort one of them

Schemes:

  • Wound-Wait: Older transaction "wounds" younger
    If TS(Ti) < TS(Tj): Abort Tj, else Ti waits
  • Wait-Die: Younger transaction "dies"
    If TS(Ti) < TS(Tj): Ti waits, else abort Ti

4. Prevent Circular Wait

Approach: Impose ordering on resource requests

Implementation: Assign unique IDs to all data items. Transactions must request locks in increasing order of IDs.

  • ✅ Provably prevents deadlocks
  • ⚠️ May not match natural access patterns
Example: Data items ordered A(ID=1), B(ID=2), C(ID=3) Valid: Lock(A) → Lock(B) → Lock(C) Invalid: Lock(C) → Lock(A) ❌
Slide 17 of 20

⚠️ Deadlock Avoidance

Avoidance Strategy

Dynamically examine resource allocation state to ensure the system never enters an unsafe state that could lead to deadlock.

Safe State Concept

Definitions:

  • Safe State: System can allocate resources to each transaction in some order and still avoid deadlock
  • Unsafe State: No guarantee that deadlock can be avoided
  • Key Point: Unsafe ≠ Deadlock, but unsafe state may lead to deadlock

Wait-Die and Wound-Wait Schemes (Detailed)

Wait-Die (Non-Preemptive)

Rule: Older transactions wait, younger transactions die

If Ti requests lock held by Tj: If TS(Ti) < TS(Tj): // Ti is older Ti WAITS for Tj Else: // Ti is younger ABORT Ti (Ti "dies")

Example: T1(TS=5) wants lock from T2(TS=10)

→ T1 is older, so T1 waits ✅

Example: T2(TS=10) wants lock from T1(TS=5)

→ T2 is younger, so T2 aborts ❌

Wound-Wait (Preemptive)

Rule: Older transactions wound (preempt) younger, younger wait for older

If Ti requests lock held by Tj: If TS(Ti) < TS(Tj): // Ti is older ABORT Tj (Ti "wounds" Tj) Else: // Ti is younger Ti WAITS for Tj

Example: T1(TS=5) wants lock from T2(TS=10)

→ T1 is older, so T2 is aborted (wounded) ❌

Example: T2(TS=10) wants lock from T1(TS=5)

→ T2 is younger, so T2 waits ✅

Comparison

Aspect Wait-Die Wound-Wait
Type Non-preemptive Preemptive
Who Dies? Younger transaction Younger transaction
When? When younger requests lock When older requests lock
Rollbacks More frequent Less frequent
Both schemes prevent deadlock by ensuring transactions only wait for older ones, eliminating circular wait!
Slide 18 of 20

📊 Deadlock Management: Complete Comparison

Approach When Applied Overhead Advantages Disadvantages
Detection After deadlock occurs Periodic WFG construction • Max concurrency
• No false alarms
• Simple to implement 		• Victim must rollback
• Wasted work
• Detection delay
Prevention During protocol design Low runtime overhead • No deadlocks possible
• No detection needed
• Predictable behavior 		• Reduced concurrency
• May not fit all scenarios
• Conservative approach
Avoidance Before granting locks Check state on each request • No deadlocks
• Better concurrency than prevention
• Dynamic decision 		• Complex algorithms
• Overhead on every request
• May abort unnecessarily

Real-World Database Systems

🔷 MySQL InnoDB

Strategy: Detection

Uses timeout + WFG detection. Automatically rolls back one transaction in deadlock.

🐘 PostgreSQL

Strategy: Detection

Builds WFG and detects cycles. Uses deadlock_timeout parameter.

🔶 Oracle

Strategy: Detection + MVCC

Reduces deadlocks through MVCC. Detects remaining cases.

⚡ SQL Server

Strategy: Detection

Deadlock monitor runs every 5 seconds. Chooses victim based on cost.

Choosing the Right Strategy

Decision Factors:

  • Deadlock Frequency: If rare → Detection. If common → Prevention/Avoidance
  • Transaction Patterns: If predictable → Prevention. If dynamic → Detection
  • Performance Priority: If max throughput → Detection. If predictability → Prevention
  • System Resources: If CPU available → Detection. If memory constrained → Prevention
Slide 19 of 20

📚 Lecture Summary

What We Covered Today

Part I: Concurrency Control Protocols

Lock-Based Protocols (2PL Family):

  • Basic 2PL: Growing and shrinking phases - guarantees serializability
  • Conservative 2PL: All locks upfront - deadlock-free but low concurrency
  • Strict 2PL: Hold X locks until commit - prevents cascading rollbacks
  • Rigorous 2PL: Hold all locks until commit - strictest recoverability

Alternative Protocols:

  • MVCC: Multiple versions for high concurrency - used in PostgreSQL, Oracle
  • Timestamp Ordering: Deadlock-free but may have high rollbacks
  • Thomas Write Rule: Optimization that ignores obsolete writes

Part II: Deadlock Management

Detection

✓ Wait-For Graphs
✓ Cycle detection
✓ Victim selection

Prevention

✓ Break necessary conditions
✓ Resource ordering
✓ Conservative 2PL

Avoidance

✓ Safe state checking
✓ Wait-Die scheme
✓ Wound-Wait scheme

Key Takeaways

  • 🔒 No perfect protocol - each has trade-offs between concurrency, complexity, and guarantees
  • 📊 Strict 2PL is most common in commercial databases due to balance of safety and performance
  • 🔄 MVCC provides highest concurrency for read-heavy workloads
  • 💀 Deadlocks are inevitable in lock-based systems - must have strategy to handle them
  • ⚖️ Detection is most flexible but requires overhead; Prevention sacrifices concurrency

Practice Questions

  1. Given a schedule, determine if it follows Strict 2PL
  2. Construct a Wait-For Graph and detect deadlocks
  3. Apply Thomas Write Rule to a timestamp-ordered schedule
  4. Compare two transactions using Wait-Die and Wound-Wait schemes
  5. Design a protocol for a specific use case with given requirements

🎯 Next Lecture Preview

Lecture 15: Recovery Systems

  • Log-based recovery
  • Checkpointing techniques
  • ARIES recovery algorithm
  • Shadow paging

Thank You!

Questions? Office Hours: 10:00 AM - 05:00 PM

Email: mohsin.dar@ddn.upes.ac.in

Slide 20 of 20