Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Concurrency Control: Principles, Potential Anomalies, and Algorithms, Slides of Introduction to Database Management Systems

The principles of concurrency control in databases, potential anomalies due to concurrent execution, and various concurrency control algorithms. It covers topics such as serial schedules, serializable schedules, and serializability graphs, as well as pessimistic and optimistic algorithms like two-phase locking and timestamp ordering.

Typology: Slides

2011/2012

Uploaded on 02/13/2012

richiey
richiey 🇨🇦

32 documents

1 / 13

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
9-1
Concurrency Control
nThe 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.
nPrinciples:
lWe 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.
lHowever, 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.
9-2
Potential Anomalies Due to
Concurrent Execution
nLost updates
lThe effects of some transactions are not reflected in the database.
lTransaction T2reading uncommitted changes to data made by
transaction T1.
àWrite-Read conflicts
lTransaction T2overwriting uncommitted changes of transaction T1.
àWrite-Write conflicts
nInconsistent retrievals (unrepeatable reads)
lA transaction, if it reads the same data item more than once, should
always read the same value.
lTransaction T2modifies data that is being accessed by transaction T1.
àRead-Write conflicts
pf3
pf4
pf5
pf8
pf9
pfa
pfd

Partial preview of the text

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