CS 245: Database System Principles deadlock prevention and recoverability

1
CS 245 Database System Principles deadlock
prevention and recoverability

• Modified from slides of Hector Garcia-Molina

2
Reading Assignments

• Chapter 19.1, 19.3
• Exercises for Final Exam on Chapters 18, 19
• Chapter 18
• 18.1.1, 18.2.4, 18.3.2, 18.4.1
• What is 2-phase locking? What is the deadlock
  problem with 2-phase locking?
• Explain the lock table for shared and exclusive
  locks.
• Chapter 19
• 19.1.1, 19.1.2, 19.1.4
• 19.3.1
• What is a wait-for-graph?

3
Chapter 10 More on transaction processing

• Topics
• Deadlocks (19.3)
• Prevention
• Detection
• Recoverable schedule (19.1)

4
Deadlocks

• Detection
• Wait-for graph
• Prevention
• Resource ordering
• Timeout
• Wound-wait

5
Deadlock Detection

• Build Wait-For graph
• Use lock table structures
• Build incrementally or periodically
• When cycle found, rollback victim

T5
T2
T1
T7
T4
T6
T3
6
Resource Ordering (prevention)

• Order all elements A1, A2, , An
• A transaction T can lock Ai after Aj only if i gt
  j

Problem Ordered lock requests not realistic in
most cases
7
Timeout (prevention)

• If transaction waits more than L sec., roll
  it back!
• Simple scheme
• Hard to select L

8
Wound-wait (prevention)

• Transactions given a timestamp when they arrive
  ts(Ti)
• Ti wounds Tj if ts(Ti)lt ts(Tj)
• else Ti waits
• Wound Tj rolls back and gives lock to Ti

9
Recoverability
10
Concurrency control recovery

• Example Tj Ti
• wj(A)
• ri(A)
• Commit Ti
• Abort Tj

Ti must also be aborted! ? Cascading
rollback (Bad!)
11

• Schedule is conflict serializable
• Tj Ti
• But not recoverable

12

• Need to make final decision for each
  transaction
• commit decision - system guarantees transaction
  will or has completed, no matter what
• abort decision - system guarantees transaction
  will or has been rolled back
• (has no effect)

13
To model this, two new actions

• ci - transaction Ti commits
• ai - transaction Ti aborts

14
Back to example Tj Ti
wj(A) ri(A) ci ? can we commit
here?
...
...
...
...
15
Definition Reads From

• Ti reads from Tj in S (Tj ?S Ti) if
• (1) wj(A) ltS ri(A)
• (2) aj ltS ri(A) (lt does not precede)
• (3) If wj(A) ltS wk(A) ltS ri(A) then
• ak ltS ri(A)

16
Definition Recoverable Schedule

• Schedule S is recoverable if whenever Tj ?S Ti
  (Ti reads from Tj) and j ? i and ci ? S then cj
  ltS ci
• Ti can commit only after all the transactions
  that Ti reads from are committed.

17

• Note in transactions, reads and writes
  precede commit or abort
• ? If ci ? Ti, then ri(A) lt ci
• wi(A) lt ci
• ? If ai ? Ti, then ri(A) lt ai
• wi(A) lt ai
• Also, one of Ci, ai per transaction

18
How to achieve recoverable schedules?
19
? With 2PL, hold write locks to commit (strict
2PL)

• Tj Ti
• wj(A)
• cj
• uj(A)
• ri(A)

...
...
...
...
...
...
...
20

• S is recoverable if each transaction commits only
  after all transactions from which it read have
  committed.
• S avoids cascading rollback (ACR) if each
  transaction may read only those values written by
  committed transactions.

21

• S is strict if each transaction may read and
  write only items previously written by committed
  transactions.

RC
Avoids cascading rollback
ST
SERIAL
ACR
22
Examples

• Recoverable
• w1(A) w1(B) w2(A) r2(B) c1 c2
• Avoids Cascading Rollback
• w1(A) w1(B) w2(A) c1 r2(B) c2
• Strict
• w1(A) w1(B) c1 w2(A) r2(B) c2

Assumes w2(A) is done without reading
23
Final Exam

• EVERYTHING THAT WE COVERED DURING THE SEMESTER!
• Lecture notes
• Reading assignments
• Homework exercises (including exercises given at
  the beginning of this lecture)
• Look up exercise solutions J. Ullmans page
• http//www-db.stanford.edu/ullman/dscbsol/sols.h
  tml
• May 7, 2003 -- 200-500 pm

24
Sample Final

• Will be posted on the class homepage by May 2nd
• For the new topics
• exercises similar to homework 4 and the one
  selected in this lecture
• Understand basic concepts, e.g., serializability,
  locking, B-trees, etc.
• Some definitions, e.g., what is a B-tree,
  precedence graph, recoverable schedule,
  serializable schedule, etc.

25
Office Hours

• Regular office hours from 1215-1300 on Tuesdays
  and Thursdays.
• Contact me if you need to meet other time/date
• Dont wait for the last day to prepare for the
  exam!
• New topics are difficult, need to practice.