Transcript ppt



CS 728
Advanced Database Systems
Chapter 21
Introduction to Protocols for
Concurrency Control in Databases

1



Concurrency Control


The main aim of any Database Management
System is to control requests for the same data, at
the same time, from multiple users.

Concurrency control algorithms try to coordinate
the operations of concurrent transactions to
prevent interference among concurrently
executing transactions in order to achieve
transaction consistency.
2



Concurrency Control


Purpose of Concurrency Control
 To enforce Isolation (through mutual exclusion) among
conflicting transactions.
 To preserve database consistency through consistency
preserving execution of transactions.
 To resolve read-write and write-write conflicts.

Example:
 In concurrent execution environment if T1 conflicts
with T2 over a data item A, then the existing
concurrency control decides if T1 or T2 should get the
A and if the other transaction is rolled-back or waits.
3


Lock-Based Protocols

Concurrency control ensures that transactions are updated in
the correct order, i.e. it ensures the serializability of
transactions in a multi-user database environment.

One way to insure serializability is to allow a transaction to
access a data item only if it is currently holding a lock on that
item.

A lock is a variable associated with a data item that describes
the status of the item with respect to possible operations that
can be applied to it.
 Generally, there is one lock for each data item in the
database.

Lock requests are made to concurrency-control manager
Transaction can proceed only after request is granted


4



Lock-Based Protocols


Locking is an operation which secures permission to Read
and Write a data item for a transaction.
 Example:
 Lock (X): Data item X is locked in behalf of the requesting
transaction.

Unlocking is an operation which removes these permissions
from the data item.
 Example:
 Unlock (X): Data item X is made available to all other
transactions.


Lock and Unlock are Atomic operations.
5



Shared/Exclusive Locks

Data items can be locked in two modes :
 Shared (read) mode: read_lock(Q)/lock-S(Q)
 More than one transaction can apply share lock on Q
for reading its value but no write lock can be applied
on Q by any other transaction.

Exclusive (write) mode: write_lock(Q)/lock-X(Q)
 Only one write lock on Q can exist at any time and no
shared lock can be applied by any other transaction on
Q.
6
Read
Write
Y
N
Write

Conflict matrix
 Lock-compatibility matrix
Read

N
N


Shared/Exclusive Locks

A transaction may be granted a lock on an item if
 the requested lock is compatible with locks already
held on the item by other transactions
 Any number of transactions can hold shared locks on
an item, but
 if any transaction holds an exclusive on the item no
other transaction may hold any lock on the item
 If a lock cannot be granted,
 the requesting transaction is made to wait till all
incompatible locks held by other transactions have
been released.
 The lock is then granted.


7


Shared/Exclusive Locks



In shared/exclusive locking system, every transaction must
obey the following rules:
1. Issue the operation lock-S(Q) or lock-X(Q) before any
read(Q) operation,
2. Issue the operation lock-X(Q) before any write(Q)
operation is performed,
3. Issue the operation unlock(Q) after all read(Q) and
write(Q) operations,
4. Not issue a lock-S(Q) operation if it already holds an
exclusive lock on item Q,
5. Not issue a lock-X(Q) operation if it already holds a
shared lock or exclusive lock on item Q,
6. Not issue an unlock(Q) operation unless it already
holds a read lock or write lock on item Q.
8


Lock-Based Protocols



The following code performs the read_lock(X) operation:
B: if LOCK(X) = ”unlocked” then
begin LOCK(X) = “read-locked”;
no_of_reads(X) = 1;
end
else if LOCK(X) = “read-locked” then
no_of_reads(X)++
else
begin
wait(until LOCK(X)=”unlocked”
and the lock manager wakes up
the transaction);
go to B
end;
9


Lock-Based Protocols


The following code performs the write_lock(X) operation:
B: if LOCK(X) = “unlocked” then
LOCK(X) = “write-locked”;
else
begin
wait (until LOCK(X)=“unlocked”
and the lock manager wakes up
the transaction);
go to B
end;

10



Lock-Based Protocols

The following code performs the unlock operation:
if LOCK (X) = “write-locked” then
begin
LOCK (X)  “unlocked”;
wakes up one of the transactions, if any
end
else if LOCK (X)  “read-locked” then
begin
no_of_reads(X)  no_of_reads(X)-1
if no_of_reads (X) = 0 then
begin
LOCK (X) = “unlocked”;
wake up one of the transactions, if any
end
end;

11



Pitfalls of Lock-Based Protocols

Consider the partial schedule

Neither T3 nor T4 can make progress
 executing lock-S(B) causes T4 to wait for T3 to
release its lock on B, while executing lockX(A) causes T3 to wait for T4 to release its lock
on A.
12




Pitfalls of Lock-Based Protocols

Such a situation is called a deadlock.

To handle a deadlock,
 one of T3 or T4 must be rolled back and its
locks released.

The potential for deadlock exists in most locking
protocols.

Deadlocks are a necessary evil.
13



Dealing with Deadlock




A partial schedule of T1 and T2  that is in a state
of deadlock.
A wait-for graph for the partial schedule in (a).
14



Dealing with Starvation


Starvation is the situation in which a transaction
cannot proceed for an indefinite period of time
while other transactions in the system continue
normally.

For example:
 A transaction may be waiting for an X-lock on
an item, while a sequence of other transactions
request and are granted an S-lock on the same
item.
 The same transaction is repeatedly rolled back
due to deadlocks.

One solution for starvation is to have a fair scheme,
such as using a first-come-first-served.
15


The Two-Phase Locking Protocol


This protocol ensures conflict-serializable schedules

If all transactions obey the 2PL then all possible
interleaved schedules are serializable.

This protocol requires that each transaction issues
lock and unlock requests in two phases:

Phase 1: Growing Phase
transaction may obtain locks but may not release locks

Phase 2: Shrinking Phase
transaction may release locks but may not obtain locks

16


The Two-Phase Locking Protocol



No transaction should request a lock after it releases
one of its locks.
17


The Two-Phase Locking Protocol


It can be proved that the
transactions can be serialized in
the order of their lock points
a
point where a transaction
acquired its final lock
 end


of its growing phase
2PL does not ensure freedom
from deadlocks
18



Two-Phase Locking



Transactions that do not obey 2PL
Two transactions T1 and T2.
Results of possible serial schedules of T1 and T2.
T2

19
T1


Two-Phase Locking




Transactions that do not obey 2PL
(c) A nonserializable schedule S that uses locks.
20


Two-Phase Locking



Transactions T1 & T2 , which are the same as T1 &
T2 but which follow 2PL.
21



Strict Two-Phase Locking (S2PL)

To avoid cascading rollback, follow a modified
protocol called Strict 2PL (S2PL).
 2PL
a
transaction must hold all its exclusive locks till
it commits/aborts.

S2PL does not prevent deadlock.
locks

T commits
22
time


Rigorous two-phase locking (R2PL) 

R2PL is even stricter
 all
locks are held till commit/abort.
 In
this protocol, transactions can be serialized in
the order in which they commit.
 S2PL permits higher degree of concurrency than
R2PL but less than 2PL.

23


Lock Conversions


2PL with lock conversions:
 First Phase (Growing):
 can acquire a lock-S/lock-X on item
 can convert a lock-S to a lock-X (upgrade)
– if Ti has a read-lock (X) and Tj has no read-lock (X) (i  j) then
» convert read-lock(X) to write-lock(X)
– Else force Ti to wait until Tj unlocks X

Second Phase (Shrinking):
 can release a lock-S/lock-X
 can convert a lock-X to a lock-S (downgrade)
– Ti has a write-lock(X) (*no transaction can have any lock on X*)
– convert write-lock(X) to read-lock(X)


This protocol assures serializability
24


Timestamp-Based Protocols

Each transaction is issued a timestamp when it starts
 CC techniques based on timestamp ordering do not
use locks, and thus deadlocks cannot occur (no
transaction ever waits)
 may not be (cascadeless and recoverable)
 The protocol manages concurrent execution such that
the time-stamps determine the serializability order.
 If an old transaction Ti has time-stamp TS(Ti), a new
transaction Tj is assigned time-stamp TS(Tj) such that
TS(Ti)  TS(Tj)


25


Timestamp-Ordering Protocol


In order to assure such behavior, the protocol maintains for each data Q two
timestamp values:

W-timestamp(Q) (W-TS(Q))
 is the largest time-stamp of any transaction that executed write(Q)
successfully.
 If W-TS(Q) = TS(T), then T is the youngest transaction that has written Q
successfully.

R-timestamp(Q) (R-TS(Q))
 is the largest time-stamp of any transaction that executed read(Q)
successfully.
 If R-TS(Q) = TS(T), then T is the youngest transaction that has read Q
successfully.


These TSs are updated whenever a new read(Q) or write(Q) is executed
26


Timestamp-Ordering Protocol


The timestamp ordering protocol ensures that any
conflicting read and write operations are executed
in timestamp order

Suppose a transaction T issues read(Q)
 If TS(T)  W-TS(Q) then T needs to read a
value of Q that was already overwritten. Hence,
the read operation is rejected, and T is rolled
back.

If TS(T)  W-TS(Q), then the read operation is
executed, and
 R-TS(Q) = max(R-TS(Q), TS(T))

27


Timestamp-Ordering Protocol


Suppose that transaction T issues write(Q).
 If TS(T)  R-TS(Q), then the value of Q that T is
producing was needed previously, and the system
assumed that the value would never be produced.
Hence, the write operation is rejected, and T is
rolled back.
TS(T)  W-TS(Q), then T is attempting to write
an obsolete value of Q. Hence, this write
operation is rejected, and T is rolled back.
 If
 Otherwise,
the write operation is executed, and
W-TS(Q) = TS(T)

28


Example Use of the Protocol


TS(T1) = 1, TS(T2) = 2, TS(T3) = 3
T1
read(A)
T2
T3
read(A)
read(A)
R-TS(A)
0
1
3
3
W-TS(A)
0
0
0
0
write(A)
rejected

29



Transactions timestamps are 1, 2, 3, 4, 5
T1
T2
T3
T4
R-TS
(X, Y, Z)
W-TS
(X, Y, Z)
T5
(0, 0, 0)
(0, 0, 0)
read(X)
(5, 0, 0)
(5, 2, 0)
(5, 2, 0)
(5, 2, 0)
(5, 2, 0)
(5, 2, 5)
(5, 2, 5)
(5, 2, 5)
(5, 2, 5)
(0, 0, 0)
(0, 0, 0)
(0, 0, 0)
(0, 3, 0)
(0, 3, 3)
(0, 3, 3)
(0, 3, 3)
(0, 3, 3)
(0, 3, 3)
read(Y)
read(Y)
write(Y)
write(Z)
read(Z)
read(X)
read(X)
write(Z)
reject & roll back


Example Use of the Protocol
write(Y)
write(Z)
30

Correctness of Timestamp-Ordering
Protocol


The timestamp-ordering protocol guarantees
serializability since all the arcs in the precedence
graph are of the form:
transaction
with smaller
timestamp



transaction
with larger
timestamp
Thus, there will be no cycles in the precedence
graph
31


Recoverability and Cascade Freedom 

Problem with timestamp-ordering protocol:
 Suppose Ti aborts, but Tj has read a data item
written by Ti, then Tj must abort
 If Tj had been allowed to commit earlier, the
schedule is not recoverable.
 Further,
any transaction that has read a data item
written by Tj must abort
 This
can lead to cascading rollback
 a chain of rollbacks

32


Recoverability and Cascade Freedom 

Solution:
 A transaction is structured such that its writes are
all performed at the end of its processing
 All
writes of a transaction form an atomic action;
no transaction may execute while a transaction is
being written
 A transaction
that aborts is restarted with a new
timestamp

33


Thomas’ Write Rule




Modified version of the timestamp-ordering protocol in
which obsolete (outdated) write (the value that will never
need to be read) operations may be ignored under certain
circumstances.
When T attempts to write data item Q, if TS(T)  W-TS(Q),
then T is attempting to write an obsolete value of Q. Hence,
rather than rolling back T as the timestamp ordering protocol
would have done, this write operation can be ignored.

Otherwise this protocol is the same as the timestamp ordering
protocol.

Thomas' Write Rule allows greater potential concurrency.
34


Deadlock Handling

Consider the following two transactions:
T1: write (A)
T2: write(B)
write(B)
write(A)
 Schedule with deadlock


35


Deadlock Prevention

Deadlock prevention protocols ensure that the
system will never enter into a deadlock state.

Some prevention strategies :
 Requires that each transaction locks all its data
items before it begins execution.

 Low degree of concurrency

36


Deadlock Prevention



Conservative 2 PL (static & deadlock-free):
 requires a transaction T to pre-declare all the
read & write set of items; and lock all these
items before T begins execution.
 If any of the pre-declared items can not be
locked, T does not lock any item at all. Instead,
T waits and tries again until all the items are
available for locking.
37


Deadlock Prevention



Assume that Ti requests a data item currently held
by Tj.
wait-die scheme:
 If Ti is older than Tj
(i.e., TS(Ti)  TS(Tj))
 Then wait(Ti)
 Else die(Ti)
 Ti is aborted and restarted with its old starting time.


Younger transactions never wait for older ones;
they are rolled back instead.

A transaction may die several times before
acquiring needed data item
38


Wait-Die: Example
T1 (ts =10)

wait
T2
wait?
(ts =20)
T3
wait
(ts =25)

39


Deadlock Prevention


wound-wait scheme
 If Ti is older than Tj (i.e., TS(Ti)  TS(Tj))
 then wound(Tj)
// Tj is wounded by Ti
 Tj is aborted and restart it with its old starting time.


else (Ti is younger than Tj) wait(Ti)

Older transaction wounds (forces rollback) of
younger transaction instead of waiting for it.

Younger transactions may wait for older ones.

May be fewer rollbacks than wait-die scheme.
40



Deadlock Prevention
Older transactions thus have precedence over newer
ones, and starvation is hence avoided.
 Example:
 T1: W(X)
W(Y)
 T2:
W(Y)
W(X)
 T1 is older.


wait-die:


X-Lock2(Y)
wait(T1,Y)
X-Lock2(Y)
abort(T2)
…
wound-wait:


X-Lock1(X)
X-Lock1(X)
41
X-Lock1(Y)
…


Wound-Wait: Example
T1 (ts =25)

wait
T2
wait
(ts =20)
T3
wait
(ts =10)

42


Timeout-Based Schemes


If a transaction waits for a lock more than a
specified amount of time, the transaction is rolled
back.
 deadlocks
are not possible
 simple to implement
 starvation is possible
 difficult to select a good timeout value

43


Deadlock Detection


Deadlocks can be described as a wait-for graph,
which consists of a pair G = (V, E),
 V is a set of vertices (all the transactions)
 E is a set of edges
each element is an ordered pair Ti Tj.
Ti  Tj is in E, then Ti is waiting for Tj to
release a data item.
 When Ti requests a data item currently being held
by Tj, then the edge Ti  Tj is inserted in the waitfor graph.
 This edge is removed only when Tj is no longer
holding a data item needed by Ti.
 If

44


Deadlock Detection

The system is in a deadlock state if and only if the
wait-for graph has a cycle.
 Must invoke a deadlock-detection algorithm
periodically to look for cycles.

Wait-for graph without a cycle
Wait-for graph with a cycle

45


Deadlock Recovery

When deadlock is detected :
 Some transaction will have to rolled back (made a
victim) to break deadlock.
 Select that transaction as victim that will incur
minimum cost.
 Factors in selecting a victim transaction:
 The amount of effort already made in the
transaction.
 The cost of aborting the transaction.

It may cause cascading aborts.
 How

close the transaction is to complete?
 The number of deadlocks that can be broken
when the transaction is aborted.
46


Deadlock Recovery


When deadlock is detected:
 Rollback: determine how far to rollback
transaction
Total rollback: Abort the transaction and then restart it.
Partial rollback: More effective to roll back transaction
only as far as necessary to break deadlock.
 Starvation
happens if same transaction is always
chosen as victim.
Include the number of rollbacks in the cost factor to
avoid starvation

47
