Download Concurrency Control: Principles, Potential Anomalies, and Algorithms and more Slides Introduction to Database Management Systems in PDF only on Docsity!
9-
Concurrency Control
n The problem of synchronizing concurrent
transactions such that the consistency of the
database is maintained while, at the same time,
maximum degree of concurrency is achieved.
n Principles:
l We want to interleave the execution of transactions for
performance reasons
‡ E.g., execute operations of another transaction when the first one starts doing I/O.
l However, we want the results of interleaved executions
to be equivalent to non- interleaved execution for
correctness
‡ We need to be able to reason about the execution order of transactions.
Potential Anomalies Due to
Concurrent Execution
n Lost updates
l The effects of some transactions are not reflected in the database. l Transaction T 2 reading uncommitted changes to data made by transaction T 1. ‡ Write-Read conflicts l Transaction T 2 overwriting uncommitted changes of transaction T 1. ‡ Write-Write conflicts
n Inconsistent retrievals (unrepeatable reads)
l A transaction, if it reads the same data item more than once, should always read the same value. l Transaction T 2 modifies data that is being accessed by transaction T 1. ‡ Read-Write conflicts
9-
Execution Schedule (or History)
n An order in which the operations of a set of
transactions are executed.
n A schedule (history) can be defined as a partial
order over the operations of a set of transactions.
H 1 = W 2 ( x ) R 1 ( x ) R 3 ( x ) W 1 ( x ) C 1 W 2 ( y ) R 3 ( y ) R 2 ( z ) C 2 R 3 ( z ) C 3 }
T 1 : Read( x ) T 2 : Write( x ) T 3 : Read( x ) Write( x ) Write( y ) Read( y ) Commit Read( z ) Read( z ) Commit Commit
A complete schedule SC ( T ) over a set of transactions
T ={ T 1 , …, Tn } is a partial order SC ( T )={Σ T , < T }
where
∂ Σ T = ∪ i Σ i , for i = 1, 2, …, n
∑ < T ⊇ ∪ i < i , for i = 1, 2, …, n
∏ For any two conflicting operations oij , okl ∈ Σ T , either
oij < T okl or okl < T oij
(Remember: oij is an operation of transaction Ti )
Formalization of Schedule
9-
Serial Schedule
n All the actions of a transaction occur consecutively.
n No interleaving of transaction operations.
n If each transaction is consistent (obeys integrity
rules), then the database is guaranteed to be
consistent at the end of executing a serial schedule.
T 1 : Read( x ) T 2 : Write( x ) T 3 : Read( x ) Write( x ) Write( y ) Read( y ) Commit Read( z ) Read( z ) Commit Commit
Hs = W 2 ( x ) W 2 ( y ) R 2 ( z ) C 2 R 1 ( x ) W 1 ( x ) C 1 R 3 ( x ) R 3 ( y ) R 3 ( z ) C 3 }
T 2 → T 1 → T 3
Serializable Schedule
n Transactions execute concurrently, but the net effect
of the resulting schedule upon the database is
equivalent to some serial schedule.
n Equivalent with respect to what?
l Conflict equivalence : the relative order of execution of the conflicting operations belonging to committed transactions in two schedules are the same. l Conflicting operations : two incompatible operations (e.g., Read and Write) conflict if they both access the same data item. ‡ Incompatible operations of each transaction is assumed to conflict; do not change their execution orders. ‡ If two operations from two different transactions conflict, the corresponding transactions are also said to conflict.
9-
Serializable Schedule
The following are not conflict equivalent
Hs = W 2 ( x ) W 2 ( y ) R 2 ( z ) C 2 R 1 ( x ) W 1 ( x ) C 1 R 3 ( x ) R 3 ( y ) R 3 ( z ) C 3
H 1 = W 2 ( x ) R 1 ( x ) R 3 ( x ) W 1 ( x ) C 1 W 2 ( y ) R 3 ( y ) R 2 ( z ) C 2 R 3 ( z ) C 3
The following are conflict equivalent; therefore
H 2 is serializable.
Hs = W 2 ( x ) W 2 ( y ) R 2 ( z ) C 2 R 1 ( x ) W 1 ( x ) C 1 R 3 ( x ) R 3 ( y ) R 3 ( z ) C 3
H 2 = W 2 ( x ) R 1 ( x ) W 1 ( x ) C 1 R 3 ( x ) W 2 ( y ) R 3 ( y ) R 2 ( z ) C 2 R 3 ( z ) C 3
T 1 : Read( x ) T 2 : Write( x ) T 3: Read( x ) Write( x ) Write( y ) Read( y ) Commit Read( z ) Read( z ) Commit Commit
Serializability Graph
n Serializability graph SGH ={ V , E } for schedule H :
l V ={ T | T is a committed transaction in H }
l E ={ Ti → Tj if oij ∈ Ti and okl ∈ Tk conflict and oij < H okl }
T 2 T 1
T 3
H 1
T 2 T 1
T 3
H 2
n Theorem: Schedule H is serializable iff SGH does
not contain any cycles.
9-
Two-Phase Locking (2PL)
∂ A Transaction locks an object before using it.
∑ When an object is locked by another transaction, the
requesting transaction must wait.
∏ When a transaction releases a lock, it may not request
another lock.
Obtain lock
Release lock
Lock point
Phase 1 Phase 2 BEGIN END
No. of locks
Strict 2PL
Hold locks until the end.
Obtain lock
Release lock
BEGIN END
Transaction period of duration data item use
9-
Timestamp Ordering
∂ Transaction ( Ti ) is assigned a globally unique timestamp ts ( Ti ).
∑ Transaction manager attaches the timestamp to all operations
issued by the transaction.
∏ Each data item is assigned a write timestamp ( wts ) and a read
timestamp ( rts ):
l rts ( x ) = largest timestamp of any read on x l wts ( x ) = largest timestamp of any write on x
π Conflicting operations are resolved by timestamp order.
Basic T/O:
for Ri ( x ): for Wi ( x ): if ts ( Ti ) < wts ( x ) if ts ( Ti ) < rts ( x ) or ts ( Ti ) < wts ( x ) then reject Ri ( x ) then reject Wi ( x ) else {accept Ri ( x ) else {accept Wi ( x ) rts ( x ) ← ts ( Ti ) } wts ( x ) ← ts ( Ti ) }
Multiversion Timestamp Ordering
n Do not modify the values in the database, create new
values.
n A Ri ( x ) is translated into a read on one version of x.
l Find a version of x (say xv ) such that ts ( xv ) is the largest timestamp less than ts ( Ti ).
n A Wi ( x ) is translated into Wi ( xw ) and accepted if the
scheduler has not yet processed any Rj ( xr ) such that
ts ( Ti ) < ts ( xr ) < ts ( Tj )
Wi ( x ) Some other transaction
created xr
Rj ( xr )
9-
Optimistic CC Validation Test
∑ If there is any transaction Tk such that ts ( Tk )< ts ( Ti )
and which completes its write phase while Ti is in
its read phase, then validation succeeds if WS ( Tk ) ∩
RS ( Ti ) = Ø
l Read and write phases overlap, but Ti does not read
data items written by Tk
T^ R^ V^ W
k R V W Ti
Optimistic CC Validation Test
∏ If there is any transaction Tk such that ts ( Tk )< ts ( Ti )
and which completes its read phase before Ti
completes its read phase, then validation succeeds if
WS ( Tk ) ∩ RS ( Ti ) = Ø and WS ( Tk ) ∩ WS ( Ti ) = Ø
l They overlap, but don't access any common data items.
T^ R^ V^ W
k R V W Ti
9-
n A transaction is deadlocked if it is blocked and will
remain blocked until there is intervention.
n Locking-based CC algorithms may cause
deadlocks.
n Wait-for graph
l If transaction Ti waits for another transaction Tj to
release a lock on an entity, then Ti → Tj in WFG.
Deadlock
Ti Tj
n Prevention
l Guaranteeing that deadlocks can never occur in
the first place. Check transaction when it is
initiated. Requires no run time support.
n Avoidance
l Detecting potential deadlocks in advance and
taking action to insure that deadlock will not
occur. Requires run time support.
n Detection and Recovery
l Allowing deadlocks to form and then finding
and breaking them. As in the avoidance
scheme, this requires run time support.
Deadlock Management
9-
WAIT-DIE Rule: If Ti requests a lock on a data item
which is already locked by Tj , then Ti is permitted to
wait iff ts ( Ti )< ts ( Tj ). If ts ( Ti )> ts ( Tj ), then Ti is aborted
and restarted with the same timestamp.
l if ts ( Ti )< ts ( Tj ) then Ti waits else Ti dies
l non-preemptive: Ti never preempts Tj
WOUND-WAIT Rule: If Ti requests a lock on a data
item which is already locked by Tj , then Ti is
permitted to wait iff ts ( Ti )> ts ( Tj ). If ts ( Ti )< ts ( Tj ), then
Tj is aborted and the lock is granted to Ti.
l if ts ( Ti )< ts ( Tj ) then Tj is wounded else Ti waits
l preemptive: Ti preempts Tj if it is younger
Deadlock Avoidance –
Wait-Die & Wound-Wait Algorithms
n Transactions are allowed to wait freely.
n Wait-for graphs and cycles.
Deadlock Detection
