More Transaction Management

1
More Transaction Management

• Concurrency control and recovery
• Resolving deadlocks
• Logical logging

Source slides by Hector Garcia-Molina
2
Concurrency control recovery

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

? Cascading rollback (Bad!)
3

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

4

• 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)

5
To model this, two new actions

• Ci - transaction Ti commits
• Ai - transaction Ti aborts
• Each transaction Ti has exactly one, either Ci or
  Ai
• Every read and write of Ti precedes the Ci (or
  Ai)

6
Back to example Tj Ti
Wj(A) ri(A) Ci ? can we commit
here?
...
...
...
...
7
Definition

• Ti reads from Tj in S (denoted Tj ?S Ti) if
• there is a write wj(A) in S that is followed by a
  read ri(A) in S
• there is no abort of Tj in S before the ri(A) in
  S
• if there is a write to A in S by another
  transaction Tk, then Tk aborts before the ri(A)

8
Definition

• Schedule S is recoverable if whenever Ti reads
  from another transaction Tj,
• Ti does not commit until after Cj commits.

9
How to achieve recoverable schedules?
10
? With 2PL, hold write locks until transaction
commits (strict 2PL)

• Tj Ti
• Wj(A)
• Cj
• uj(A)
• ri(A)

...
...
...
...
...
...
...
11

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

12

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

RC
Avoids cascading rollback
ST
SERIAL
ACR
13
Where are serializable schedules?
SERIALIZABLE
14
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

15
More Examples
w2(A) w1(B) w1(A) r2(B) c1 c2
SERIALIZABLE
w1(A) w1(B) w2(A) r2(B) c2 c1
16
Deadlocks

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

17
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
18
Resource Ordering

• Order all elements A1, A2, , An
• Each transaction must acquire locks on elements
  in this order (i.e., T cannot lock Aj before Ai
  if j gt i)

Problem Ordered lock requests not realistic in
most cases
19
Timeout

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

20
Wait-die

• Each transaction Ti is given a timestamp when it
  starts, denoted ts(Ti)
• Suppose Ti requests a lock currently held by Tj
• if ts(Ti)lt ts(Tj)
• then Ti waits for Tj (older waits for
  younger)
• else Ti dies (aborts) (younger dies)
• If Ti dies then it later restarts with the same
  timestamp

21
Example

• T1
• (ts 10)
• T2
• (ts 20)
• T3
• (ts 25)

wait
wait?
wait
No.
22
Wound-wait

• Each transaction Ti is given a timestamp when it
  starts, denoted ts(Ti)
• Suppose Ti requests a lock currently held by Tj
• If ts(Ti)lt ts(Tj)
• then Ti wounds Tj (younger yields lock to
  older)
• else Ti waits (younger waits for older)
• Wound Tj rolls back and gives lock to Ti
• If Tj dies then later it restarts with the same
  timestamp

23
Example

• T1
• (ts 25)
• T2
• (ts 20)
• T3
• (ts 10)

wait
wait?
No.
wait
(Note that timestamps are different than in
previous example)
24
Comparing Deadlock Management Schemes

• Wait-die and Wound-wait ensure no starvation
  (unlike the others)
• Wait-die tends to roll back more transactions
  than Wound-wait but they tend to have done less
  work
• Wait-die and Wound-wait are easier to implement
  than waits-free graph
• Waits-for graph technique only aborts
  transactions if there really is a deadlock
  (unlike the others)

25

• Managing Rollbacks Using Locking
• What are we really locking?

26
Example

• Ti
• Read record r1
• Read record r1 do record
• locking
• Modify record r3

...
...
...
...
27
But underneath
record id
R1
R2
R3
Disk pages
28
Solution view DB at two levels

• Top level record actions
• record locks
• undo/redo actions logical
• e.g., Insert record(X,Y,Z)
• Redo insert(X,Y,Z)
• Undo delete

Low level deal with physical details
29

• Note undo does not return physical DB to
  original state only same logical state
• e.g., Insert R3 Undo (delete R3)

R1
R1
R1
R2
R2
R2
R3
30
Logging Logical Actions

• Logical action typically span one block
• Undo/redo log entry specifies undo/redo logical
  action

• Challenge making actions idempotent
• Example (bad) redo insert ? key inserted
  multiple times!