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