Carnegie Mellon Univ' Dept' of Computer Science 15415 Database Applications

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

• C. Faloutsos
• Concurrency control

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
Concurrency overview

• why we want it?
• what does it mean correct interleaving?
• precedence graph
• how to achieve correct interleavings
  automatically?
• concurrency control

5
Problem

• concurrent access to data (consider lost update
  problem)
• how to solve it?

6
Lost update problem no locks
Read(N)
time
7
Solution part 1

• locks! (most popular solution)
• lock manager grants/denies lock requests

8
Lost update problem with locks
T2
T1
lock manager
grants lock denies lock
lock(N) Read(N) NN-1 Write(N) Unlock(N)
lock(N)
time
T2 waits
grants lock to T2
Read(N) ...
9
Locks

• but, what if we all just want to read N?

10
Solution part 1

• Locks and their flavors
• exclusive (or write-) locks
• shared (or read-) locks
• ltand more ... gt
• compatibility matrix

11
Solution part 1

• transactions request locks (or upgrades)
• lock manager grants or blocks requests
• transactions release locks
• lock manager updates lock-table

12
Solution part 2

• locks are not enough eg., inconsistent
  analysis

13
Inconsistent analysis
time
Precedence graph?
14
Inconsistent analysis w/ locks
time
T1 L(A) Read(A) ... U(A)
T2 L(A) .... L(B) ....
the problem remains! Solution??
15
General solution

• Protocol(s)
• Most popular protocol 2 Phase Locking (2PL)

16
2PL

• X-lock version transactions issue no lock
  requests, after the first unlock
• THEOREM if all transactions obey 2PL -gt all
  schedules are serializable

17
2PL example

• inconsistent analysis why not 2PL?
• how would it be under 2PL?

18
2PL X/S lock version

• transactions issue no lock/upgrade request, after
  the first unlock/downgrade
• In general growing and shrinking phase

19
2PL observations

• limits concurrency
• may lead to deadlocks
• 2PLC (keep locks until commit)

20
Concurrency overview

• what does it mean correct interleaving?
• precedence graph
• how to achieve correct interleavings
  automatically? concurrency control
• locks protocols
• 2PL, 2PLC
• graph protocols
• multiple granularity locks
• ltcc without locks optimistic ccgt

21
Other protocols than 2-PL graph

• assumption there is a (hierarchical) ordering on
  the data items, like, eg., pages of a B-tree

A
B
C
22
Eg., tree protocol (X-lock version)

• a xact can request any item, on its first lock
  request
• from then on, it can only request items for which
  it holds the parent lock
• it can release locks at any time
• it can NOT request an item twice

23
Tree protocol - example
A

• 2PL?
• correct?

• T1 T2
• L(B)
• L(D)
• L(H)
• U(D)
• L(E)
• U(E)
• L(D)
• U(B)
• U(H)
• L(G)
• U(D)
• U(G)

C
B
F
E
D
G
I
H
24
Tree protocol

• equivalent to 2PL?
• deadlocks?

25
More protocols

• lock granularity field? record? page? table?
• Pros and cons?
• (Ideally, each transaction should obtain a few
  locks)

26
Multiple granularity
DB

• Eg

Table1
Table2
record-n
record2
record1
attr1
attr2
attr1
27
what types of locks?

• X/S locks for leaf level
• intent locks, for higher levels
• IS intent to obtain S-lock underneath
• IX intent .... X-lock ...
• S shared lock for this level
• X ex- lock for this level
• SIX shared lock here IX

28
Protocol

• each xact obtains appropriate lock at highest
  level
• proceeds to desirable lower levels

29
Compatibility matrix
30
Conclusions

• ACID for transactions
• concurrency
• serializability (precedence graph)
• one (popular) solution locks
• 2PL(C) protocol
• graph protocols multiple gran.