Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications

1
Carnegie Mellon Univ.Dept. of Computer
Science15-415 - Database Applications

• C. Faloutsos
• Concurrency control - deadlocks

2
General Overview

• Relational model - SQL
• Functional Dependencies Normalization
• Physical Design Indexing
• Query optimization
• Transaction processing
• concurrency control
• recovery

3
Transactions - dfn

• unit of work, eg.
• move 10 from savings to checking
• Atomicity (all or none)
• Consistency
• Isolation (as if alone)
• Durability

recovery
concurrency control
4
Isolation - concurrency control

• serializability lt-gt correctness
• precedence graph
• automatically correct interleavings
• locks protocol (2PL, 2PLC)
• but deadlocks!

5
Deadlocks

• detection
• handling
• (prevention)

6
Deadlock detection
time

• T1 T2 lock manager
• L(A) Yes
• L(B) Yes
• ...
• L(B) No
• ltwaitsgt
• L(A) No

DEADLOCK
7
Algo for deadlock detection?
8
Algo for deadlock detection?

• wait-for graph
• nodes -gt transactions
• arcs -gt Tsource waits for Tsink
• if cycle, then deadlock!

9
Eg
time

• T1 T2 lock manager
• L(A) Yes
• L(B) Yes
• ...
• L(B) No
• ltwaitsgt
• L(A) No

for B
T1
T2
A
B
for A
10
Another example

• is there a deadlock?
• if yes, which xacts are involved?

11
Another example

• now, is there a deadlock?
• if yes, which xacts are involved?

12
Deadlock detection

• how often should we run the algo?
• how many transactions are typically involved?

13
Deadlock handling

• Q what to do?

14
Deadlock handling

• Q0 what to do?
• A select a victim rollback
• Q1 which/how to choose?

15
Deadlock handling

• Q1 which/how to choose?
• A1.1 by age
• A1.2 by progress
• A1.3 by items locked already...
• A1.4 by xacts to rollback
• Q2 How far to rollback?

16
Deadlock handling

• Q2 How far to rollback?
• A2.1 completely
• A2.2 minimally
• Q3 Starvation??

17
Deadlock handling

• Q3 Starvation??
• A3.1 include rollbacks in victim selection
  criterion.

18
SQL statement

• usually, conc. control is transparent to the
  user, but
• LOCK lttable-namegt EXCLUSIVESHARED

19
Concurrency control - conclusions

• serializability lt-gt correctness
• automatically correct interleavings
• locks protocol (2PL, 2PLC, ...)
• deadlock detection handling

20
Quiz

• is there a serial schedule ( interleaving) that
  is not serializable?
• is there a serializable schedule that is not
  serial?
• can 2PL produce a non-serializable schedule?
  (assume no deadlocks)

21
Quiz - contd

• is there a serializable schedule that can not be
  produced by 2PL?
• a xact obeys 2PL - can it be involved in a
  non-serializable schedule?
• all xacts obey 2PL - can they end up in a
  deadlock?

22
Quiz - hints
Q 2PLC??
serializable schedules
2PL schedules
serializable schedules
serial schs
23
Quiz - hints
2PL schedules
serializable schedules
serializable schedules
serial schs
2PLC