Download Concurrency Control in DBMS: 2PL & Deadlock Prevention - Prof. Kristen R. Lefevre and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 1
Concurrency Control
Chapter 17
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 2
Agenda
Last time: transactions, schedules, lock
basics
How can we use locking to guarantee
schedules with desirable properties?
Serializable, recoverable, avoid cascading
aborts (ACA)
Lock Manager Implementation
Deadlock
Definition, detection, and prevention
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 3
Two-Phase Locking (2PL)
2PL :
If T wants to read (modify) an object, first obtains a
shared (exclusive) lock
If T releases any lock, it can acquire no new locks!
Gurantees serializability!
Strict 2PL :
Hold all locks until end of Xact
Guarantees serializability and
ACA too!
Note ACA schedules are
always recoverable Time
# locks
growing phase (^) Shrinking phase lock point
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 4
Example
Two accounts, A and B
Two transactions, T1 and T
T1: Transfer $100 from A to B
T2: Add 10% interest to A and B
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 5
Example - Strict 2PL
W(A)
W(B)
begin T2: Add 10% interest to A & B T1: Transfer $100 from A to B commit
R(B); B+= 100
begin W(B)
R(B); B *= 1.
R(A); A *= 1.
commit
W(A)
R(A); A -= 100
Wait
T1 acquires S lock on A
T1 acquires X lock on A
T1 acquires S lock on B
T1 acquires X lock on B
T1 releases all locks
T2 acquires S lock on A
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 6
Precedence Graph
Precedence (or serializability) graph for schedule S
A node for each committed transaction in S
An arc from Ti to Tj if some action in Ti precedes and
conflicts with some action in Tj
Commit
W(A)
Commit
W(A)
Commit
W(A)
R(A)
T1 T2 T
T1 T
T
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 10
2PL & Serializability
2PL guarantees acyclic precedence graph
Guarantees a conflict serializable schedule
Intuitively, equivalent serial schedule given by
order in which transactions enter shrinking
phase
Why Strict 2PL?
Guarantees ACA (read only committed values)
How? Hold X locks until end of transaction; no
WW or WR conflicts
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 11
Exercise
Is this schedule
allowed by 2PL?
Strict 2PL?
Is it serializable?
If so, what is the
corresponding
serial schedule?
Is the schedule
recoverable?
ACA?
commit
W(B)
commit
W(A)
begin T2: Add 10% interest to A, B & C T1: Transfer $ from A to B R(B); B *= 1.
R(A); A -= 100
begin W(A)
R(A); A *= 1.
W(B)
R(B); B+= 100
W(C)
R(C); C *= 1.
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 12
Lock Manager Implementation
Main data structure: Lock Table
Hash table with Object ID (OID) as key
For each object:
List of transactions holding the lock
Lock Mode (Shared or Exclusive)
Pointer to queue of waiting requests
On lock release (OID, XID)
Update list of transactions
Grant request to first transaction of queue
(Optionally, prioritize upgrades)
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 13
What about deadlocks?
commit
W(B)
begin T2: Add 10% interest to A & B T1: Transfer $100 from A to B commit
R(B); B *= 1.
begin W(A)
R(A); A *= 1.
W(B)
R(B); B+= 100
W(A)
R(A); A -= 100
T1 gets S, then X lock on A
T2 gets S, then X lock on B
T1 waits for lock on B
T2 waits for lock on A
T1 T
“Waits-for” Graph
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 14
Deadlock Detection
Observation:
Deadlocks are rare
Often involve only a few transactions
Detect rather than prevent
Lock Mgr maintains waits-for graph
Periodically check graph for cycles.
Abort some Xact to break the cycle.
Simpler hack: time-outs.
Abort if no progress made for a while.
3/31/09 EECS 484: Database Management Systems, Kristen LeFevre 15
Deadlock Prevention
Assign priorities to transactions
Use timestamp to assign priorities
Ti requests a lock, held by Tj (in a conflicting mode)
Wait-Die: If Ti has higher priority, it waits; else Ti aborts
Wound-Wait: If Ti has higher priority, abort Tj; else Ti
waits
After abort, restart with original timestamp
Guarantees the transaction eventually runs!
