Concurrency Control

1
Concurrency Control
2
Example Schedules
Transactions T1 transfers 50
from A to B T2 transfers 10 of A
to B
Example 1 a serial schedule
T1 read(A) A A -50 write(A) read(B) BB50 write
(B)
T2 read(A) tmp A0.1 A A
tmp write(A) read(B) B B tmp write(B)
Constraint The sum of AB must be the same
Before 10050
150, consistent
After 45105
3
Example Schedule

• Another serial schedule

T1 read(A) A A -50 write(A) read(B) BB
50 write(B)
T2 read(A) tmp A0.1 A A tmp write(A) read(B
) B B tmp write(B)
Before 10050
150, consistent
After 40110
Consistent but not the same as previous
schedule.. Either is OK!
4
Example Schedule (Cont.)

• Another good schedule

T1 read(A) A A -50 write(A) read(B) BB50
write(B)
T2 read(A) tmp A0.1 A A
tmp write(A) read(B) B B tmp write(B)
Effect Before After A
100 45 B 50
105
Same as one of the serial schedules Serializable
5
Example Schedules (Cont.)

• A bad schedule

T1 read(A) A A -50 write(A) read(B) BB50
write(B)
T2 read(A) tmp A0.1 A A
tmp write(A) read(B) B B tmp write(B)
Before 10050 150
After 5060 110 !!
Not consistent
Non Serializable
6
Equivalence by Swapping
7
Equivalence by Swapping
8
Equivalence by Swapping
9
Example Schedules (Cont.)

• A bad schedule

T1 read(A) A A -50 write(A) read(B) BB50
write(B)
T2 read(A) tmp A0.1 A A
tmp write(A) read(B) B B tmp write(B)
Cant move Y below X read(B) and write(B)
conflict
Y
X
10
Example Schedules (Cont.)

• A bad schedule

T1 read(A) A A -50 write(A) read(B) BB50
write(B)
T2 read(A) tmp A0.1 A A
tmp write(A) read(B) B B tmp write(B)
Cant move Y below X read(B) and write(B)
conflict
Y
Other options dont work either
X
So Not Conflict Serializable
11
Lock instructions

• New instructions
• - lock-S shared lock request
• - lock-X exclusive lock request
• - unlock release previously held lock
• Example

T1
T2
lock-X(B) read(B) B ?B-50 write(B) unlock(B) lock-
X(A) read(A) A ?A 50 write(A) unlock(A)
lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlo
ck(B) display(AB)
12
Locking Issues

• No xction proceeds
• Deadlock
• - T1 waits for T2 to unlock A
• - T2 waits for T1 to unlock B

T1 T2
lock-X(B) read(B) B ? B-50 write(B) lock-X(A) lock-S(A) read(A) lock-S(B)
Rollback transactions Can be costly...
13
Locking Issues

• Does not ensure serializability by itself

T1
lock-X(B) read(B) B ?B-50 write(B) unlock(B)
lock-X(A) read(A) A ?A 50 write(A) unlock(A)
T2
lock-S(A) read(A) unlock(A) lock-S(B) read(B) unlo
ck(B) display(AB)
T2 displays 50 less!!
14
2PhaseLocking

• Example T1 in 2PL

T1
lock-X(B) read(B) B ? B - 50 write(B) lock-X(A) read(A) A ? A - 50 write(A) unlock(B) unlock(A)
Growing phase
Shrinking phase
15
2PL Issues

• 2PL does not prevent deadlock
• gt 2 xctions involved?
• - Rollbacks expensive

T1 T2
lock-X(B) read(B) B ? B-50 write(B) lock-X(A) lock-S(A) read(A) lock-S(B)
16
Strict 2PL
T1 T2 T3
lock-X(A) read(A) lock-S(B) read(B) write(A) unlock(A) ltxction failsgt lock-X(A) read(A) write(A) unlock(A) lock-S(A) read(A)
Strict 2PL will not allow that
17
Dealing with Deadlocks

• How do you detect a deadlock?
• Wait-for graph
• Directed edge from Ti to Tj
• Ti waiting for Tj

T2
T4
T1
T1 T2 T3 T4
S(V) X(V) S(W) X(Z) S(V) X(W)
T3
Suppose T4 requests lock-S(Z)....
18
Example of Granularity Hierarchy

• The highest level in the example hierarchy is
  the entire database.
• The levels below are of type area, file or
  relation and record in that order.

19
Compatibility Matrix with Intention Lock Modes

• The compatibility matrix for all lock modes is

requestor
holder
20

• Parent Child can be
• locked in locked in
• IS
• IX
• S
• SIX
• X

P
IS, S IS, S, IX, X, SIX S, IS not necessary X,
IX, SIX none
C
21
Example
R1
t1
t4
t2
t3
22
Examples
T1(IX)
T1(IS)
R
R
T1(IX)
t3
T1(S)
t4
t2
t1
t3
t4
t2
t1
T1(X)
f4.2
f4.2
f2.2
f2.1
f4.2
f4.2
f2.2
f2.1
T1(SIX)
Can T2 access object f2.2 in X mode? What locks
will T2 get?
R
T1(IX)
t3
t4
t2
t1
T1(X)
f4.2
f4.2
f2.2
f2.1
23
Examples

• T1 scans R, and updates a few tuples
• T1 gets an SIX lock on R, then repeatedly gets an
  S lock on tuples of R, and occasionally upgrades
  to X on the tuples.
• T2 uses an index to read only part of R
• T2 gets an IS lock on R, and repeatedly gets an
  S lock on tuples of R.
• T3 reads all of R
• T3 gets an S lock on R.
• OR, T3 could behave like T2 can
• use lock escalation to decide which.