Transcript 18.3 Enforcing Serializability by Locks

Locks and Locking Mode
(18.3-18.4)
CS257 Spring/2009
Professor Tsau Lin
Student: Suntorn Sae-Eung
1
Review 18.1 and 18.2
 Concurrency Control







Schedules
Serial Schedules
Serializable Schedules
Transaction Sematics
Notation
Conflict-Serializability
Precedence Graphs and a Test for
Conflict-Serializability
2
18.3 Enforcing Serializability by
Locks
 “Locks”-the most common scheduler
 Protects unserializable behavior
 Prevents other transactions access
database elements at the same time
3
18.3.1 Locks
 Scheduler uses a lock table.
1. Request locks to DB elements
2. Perform Operation: R/W
requests from
transactions
3. Release the locks
lock
table
Schedule
4
Locks (cont.)
 Desired results of lock usage
 Consistency of Transactions: Actions and
locks must relate in the expected ways:
1. A transaction can R/W an element if it was
previously granted a lock on the element and
has not yet released the lock.
2. If a transaction locks an element, it must later
unlock that element.
 Legality of Schedules: Locks must have
their intended meaning
5
Locks (cont.)
 Notation extension
li (X) : Transaction Ti requests a lock on element X
ui (X) : Transaction Ti releases its lock on element X
ri (X) : Ti reads on X
wi (X) : Ti writes on X
Consistency Condition
li (X)
ri (X)/wi (X)
ui (X)
6
18.3.2 The Locking Scheduler
 Jobs base on lock requests
 If a request is not granted, the
transaction is delayed.
 “Lock table” tells scheduler--every
database element currently holds a
lock on that element.
7
The Locking Scheduler (cont.)
 T1: l1 (A); r1(A);
A:=A+100; w1(A);
l1 (B); u1(A); r1(B); B:=B+100; w1(B); u1(B);
 T2: l2 (A); r2(A);
A:=A*2; w2(A);
l2 (B); u2(A);
r2(B); B:=B*2;
w2(B); u2(B);
8
18.3.3 Two-Phrase Locking (2PL)
 Guarantees a legal schedule is conflict
serializable:
 Any transactions, all lock actions precede
all unlock actions.
 A transaction obeys 2PL condition is a
“two-phrase-locked transaction” or
2PL transaction.
9
18.3.4 Why 2PL Works
 Proved by an induction of n transactions in
schedule S
 First, consider only actions without locks
 Claim that Ti can move to beginning of S without
conflicting reads/write to another transactions
 Restore locks and obviously, Ti is not 2PL, as
assumed
 The conclusion
 It is possible to move a non-conflicting transaction
(Ti), to the beginning of S follow by tails.
Therefore, schedule S is conflict-serializable.
10
A Risk of Deadlock
11
18.4 Locking System with Several
Lock Modes
 In 18.3, a transaction must lock a
database element (X) either reads or
writes.
 No reason why several transactions could
not read X at the same time, as long as
none write X.
 Introduce
 Shared lock– for reading
 Exclusive lock– for writing
12
18.4.1 Shared and Exclusive Locks
 Consider lock for writing is “stronger”
than for reading.
 Notation:
sli (X)– Ti requests shared lock on DB element X
xli (X)– Ti requests exclusive lock on DB element X
ui (X)– Ti relinguishes whatever lock on X
 Example 
13
Shared and Exclusive Locks (cont.)
14
18.4.2 Compatibility Matrices
 A convenient way to describe lockmanagement policies
Lock requested
S
X
Lock held
S
Yes
No
in mode
X
No
No
15
18.4.3 Upgrading Locks
 A transaction (T) taking a shared lock is
friendly toward other transaction.
 T wants to read and write a new value X
 At first, take a shared lock on X
 T performs operations on X (may spend long
time)
 When T is ready to write a new value,
“Upgrade” lock to exclusive lock on X.
16
Upgrading Locks (cont.)
 Observe the example
 T1 cannot take an exclusive lock on B until all locks
on B are released.
17
Upgrading Locks (cont.)
 The worse case, which upgrading can
simply cause a “Deadlock”
18
18.4.Update Locks
 The third lock mode preventing the
deadlock problem on upgrading lock.
 Only the “Update lock” can be upgraded to a
write lock later; a read lock cannot do.
 An update lock can be granted on X when there
are already shared locks on X,
 Once there is an update lock, it prevents
additional any kinds of lock.
 Notation: uli (X)
19
Update Locks (cont.)
 Compatibility matrix (asymmetric)
Lock requested
S
X
U
Lock held
S
Yes
No
Yes
in mode
X
No
No
No
U
No
No
No
20
Update Locks (cont.)
 Example
21
18.4.5 Increment Locks
 A kind of lock which is useful for
increasing/decreasing transactions.
e.g. money transfer between bank accounts.
 If 2 transactions add constants to the
same database element
 It doesn’t matter which goes first, but no
reads are allowed in between transaction
processing
 Let see on following exhibits
22
Increment Locks (cont.)
CASE 1
T1: INC (A,2)
A=7
A=5
T2: INC (A,10)
T2: INC (A,10)
A=17
A=15
T1: INC (A,2)
CASE 2
23
Increment Locks (cont.)
 What if
T1: INC (A,2)
A=7
T2: INC (A,10)
A=5
A=15
T2: INC (A,10)
A=15
A != 17
T1: INC (A,2)
A=5
A=7
24
Increment Locks (cont.)
 INC (A, c):
 is increment action of write constant ‘c’ to
database element A
 stands for an atomic execution of
 READ(A,t);
 t=t+c;
 WRITE(A,t);
 Notation:
 ili (X)– action of Ti requesting an increment lock on X
 inci (X)– action of Ti increments X by some constant;
don’t care about the value of the constant.
25
Increment Locks (cont.)
 Compatibility matrix
Lock requested
S
X
I
Lock held
S
Yes
No
No
in mode
X
No
No
No
I
No
No
Yes
26
Increment Locks (cont.)
 Example
27
Question & Answers
28
For your attention
29
Reference
[1] H. Garcia-Molina, J. Ullman, and J. Widom,
“Database System: The Complete Book,”
second edition: p.897-913, Prentice Hall, New
Jersy, 2008
30