- Pessimistic Concurrency Control

Pessimistic Concurrency Control

April 8, 2021

Garcia-Molina/Ullman/Widom: Ch. 18.3-18.7, 19.2
Schedule
A sequence of read and writes from one or more transactions to objects
Serial Schedule
A schedule with no interleaving
... but with an arbitrary transaction order
Serializable Schedule
A schedule that produces the same output as a serial schedule
TimeT1T2T3
| W(A)
| W(A)
| W(B)
| W(B)
W(B)

T1's write to A "happens before" T2's write

T2's write to B "happens before" T3's write

T2's write to B "happens before" T1's write

Cycle! No equivalent serial schedule!

An acyclic "Happens Before" or Dependency Graph is conflict serializable.

Forcing Acyclicity

Locking
Snapshot Isolation
Timestamp Concurrency Control*

Goal: Enforce acyclic conflict graphs

Observation: Conflicts only occur on accesses to the same object

Idea: When a second transaction tries to access an object, require the first to COMMIT or ABORT first.

Idea: When a second transaction tries to access an object, require the first to COMMIT or ABORT first agree to never create more conflicts.

2-Phase Locking

Create one lock for each object.

Each transaction operates in two "phases".

Acquire Phase
Before accessing an object, the transaction must acquire the object's lock.
The transaction does not release locks.
Release Phase
A transaction can release locks
A transaction can never again access an object it doesn't have a lock for.

In practice, the release phase happens all at once at the end

TimeT1T2T3
| R(C)
| W(A)
| R(B)
| W(C)
| W(B)
W(C)

Conflict Serializable.

Can 2PL create this schedule?

L(...)Acquire (Lock)
U(...)Release (Unlock)

2PL can create this schedule

TimeT1T2T3
|
|
L(C)
R(C)
|
|
L(A)
W(A)
|
|
L(B)
R(B)
|
|
W(C)
U(C)
|
|
L(C)
U(B)
|
|
L(B)
W(B)
|
L(C)
W(C)

Optimizations

Observation 1: Read-Read conflicts aren't a problem

Solution: Reader/Writer Locks

(Any number of readers or one writer can hold the lock)

... also called Shared (S)/Exclusive (X) locks

Requested
SX
H
e
l
d
S Allow Block
X Block Block

An object is ...

  • ... a table
  • ... a record
  • ... a page
  • ... a column

Which should be used?

Observation 1: Too coarse locking prevents concurrency

Observation 2: Too fine locking is slow

Idea 1: Separate locks for tables, pages, rows, cells.

Doesn't Work! Need to use the same lock for all conflicts.

Idea 2: Organize locks hierarchically. Set a flag in the parent when a child is locked.

New Lock Modes

Exclusive (X)
The holding thread can safely modify the object
Shared (S)
A holding thread can safely read the object
Intent-Exclusive (IX)
Descendant locks may be held Exclusive
Intent-Shared (IS)
Descendant locks may be held Shared

Before acquiring a descendant lock, a thread must first intent-acquire all ancestors (top-down)

Requested
IS IX S X
H
e
l
d
None Allow Allow Allow Allow
IS Allow Allow Allow Block
IX Allow Allow Block Block
S Allow Block Allow Block
X Block Block Block Block

Example 1

Example 2

TimeT1T2T3T4
| S(A)
| R(A)
| X(B)
| W(B)
| S(B)
| S(C)
| R(C)
| X(C)
| X(B)
X(A)

Deadlock

A cycle of transactions waiting on each other's locks

Approach 1 Detect deadlock situations as they occur and abort deadlocked transaction

Approach 2 Anticipate deadlock situations before they happen

The Waits-For Graph

Nodes
Every running transaction
Directed Edges
From a transaction blocked to the transaction it's waiting for
TimeT1T2T3T4
| S(A)
| R(A)
| X(B)
| W(B)
| S(B)
| S(C)
| R(C)
| X(C)
| X(B)
X(A)
T1 T2 T4 T3

A cycle is a set of transactions that will never finish

Periodically check for cycles in the waits-for graph

Abort transactions until the cycle is broken

Detecting cycles is expensive!

Idea 2: Create an order over the locks (give each a #)
Only allow locks to be acquired in sequence order

TimeT1T2T3T4
| S(A)
| R(A)
| X(B)
| W(B)
| S(B)
| S(C)
| R(C)
| X(C)
| X(B)
X(A)

T3 can't acquire X(A) because it already has S(C)

Idea 2.B: Acquire all locks at the start of a transaction.

Pro: No deadlocks... ever.

Pro: No (expensive) cycle detection.

Con: Not all transactions are supported
or transactions need to know all necessary locks in advance.

Idea 3: False positive deadlock detections are ok

Trivial solution: Timeouts

... but how long should the timeout be?

Alternative: Create an order over transactions
Only allow a transaction to block on "older" transactions

Variant 1

T1 holds a lock on A
T2 tries to acquire the lock on A and blocks
(ok, because T1 is "older")
T2 holds a lock on A
T1 tries to acquire the lock on A
ABORT T1 and restart it as a "younger" transaction

"Wait-Die"

Variant 2

T1 holds a lock on A
T2 tries to acquire the lock on A and blocks
(ok, because T1 is "older")
T2 holds a lock on A
T1 tries to acquire the lock on A
ABORT T2 and restart it at the same age.

"Wait-Wound"

Wait-Die
Abort an older transaction that tries to wait on a younger one.
Wait-Wound
If an older transaction tries to wait on a younger one, kill the younger.

Managing Deadlocks

Approach 1: Detection
Detect cycles (or conditions that could indicate cycles)
Abort transactions until the cycles go away
Approach 2: Recovery
Enforce an invariant on lock acquisition order
Acquire all locks upfront