Concurrency Control

Concurrency Control

Ensures correct execution of concurrent transactions without conflicts

Goal of Concurrency Control

Preserve database consistency and isolation of transactions

Conflicts

Read-Write, Write-Read, Write-Write conflicts occur on same data items

Serializability

Concurrent schedule equivalent to some serial execution order

Conflict Serializability

Schedule can be transformed into a serial one by swapping non-conflicting operations

View Serializability

Based on equivalence of reads and writes, more general than conflict serializability

Two-Phase Locking (2PL)

Acquire all locks before releasing any; guarantees conflict serializability

Strict 2PL

Releases all locks only after commit or abort; ensures recoverability

Deadlock

A cycle in wait-for graph causing transactions to wait indefinitely

Deadlock Detection

Uses wait-for graph to detect cycles and resolve by aborting transactions

Deadlock Prevention

Prevents deadlocks by ordering lock acquisition or timeout

Lock Types

Shared (S) locks for read, Exclusive (X) locks for write

Lock Compatibility

Shared locks compatible with other shared locks, exclusive locks are not compatible

Timestamp Ordering

Assigns timestamps to transactions to serialize by timestamp order

Optimistic Concurrency Control

Transactions execute without locks, validate before commit

Multiversion Concurrency Control (MVCC)

Maintains multiple versions of data for better read concurrency

Phantom Problem

Occurs when new rows match a predicate in between repeated reads

Isolation Levels

Define allowed anomalies: Read Uncommitted, Read Committed, Repeatable Read, Serializable

Granularity of Locks

Can be at row, page, table, or database level; finer granularity increases concurrency