transacion.ppt

7/28/2019 Transacion.ppt

1/54

Concurrency control


2/54

Not all concurrent executions result in acorrect state

If control of concurrent execution is leftentirely to the operating system, many

possible schedules, including ones that leavethe database in an inconsistent state arepossible.

It is the job of the database system to ensure

that any schedule that is executed will leave thedatabase in a consistent state. The concurrency-control component of the

database system carries out this task.


3/54

We can ensure consistency of the databaseunder concurrent execution by making sure thatany schedule that is executed has the same

effect as a schedule that could have occurredwithout any concurrent execution.

That is, the schedule should, in some sense, beequivalent to a serial schedule. Such schedules

are called serializable schedules.


4/54

Before we can consider how the concurrency-control component of the database system canensure serializability.

We consider how to determine when a

schedule is serializable. Certainly, serial schedules are serializable, but if

steps of multiple transactions are interleaved, itis harder to determine whether a schedule is

serializable.Consider only read() & write() we discuss different forms of schedule

equivalence, but focus on a particular formcalled conflict serializability.


5/54

Let us consider a schedule S in which there aretwo consecutive instructions,

ITi and JTj

IfIand Jreferdifferent data items, then wecan swap Iand Jwithout affecting the results ofany instruction in the schedule.

However, ifIand Jrefer to the same data itemQ, then the order of the two steps may matter.


6/54

Thus, only in the case where both Iand Jare read instructions does the relativeorder of their execution not matter.

We say that Iand Jconflict if they areoperations by different transactions on thesame data item, and at least one of these

instructions is a write operation.


7/54

Ques:Find the equivalent

Schedule

Let Iand Jbe consecutive instructions of a schedule S. If Iand Jare instructions of different transactions and I

and Jdo not conflict, then we can swap the order ofIand Jto produce a new schedule S.

Sis equivalent to S, since all instructions appear in the same order in both schedules

except forIand J, whose order does not matter


8/54

Since the write(A) instruction of T2 inschedule 3 of Figure 14.6 does not conflictwith the read(B) instruction ofT1, we can

swap these instructions to generate anequivalent schedule, schedule 5, in Figure14.7.

Regardless of the initial system state,schedules 3 and 5 both produce the samefinal system state.


9/54

Ques: Try to Swap

We continue to swap nonconflicting instructions: Swap the read(B) instruction ofT1 with the read(A) instruction of

T2.

Swap the write(B) instruction ofT1 with the write(A) instruction ofT2.

Swap the write(B) instruction ofT1 with the read(A) instruction

ofT2.

The final result of these swaps, schedule 6 of Figure14.8, is a serial schedule


10/54


11/54

If a schedule S can be transformed into a schedule S bya series of swaps of non-conflicting instructions, we saythat S and S are conflict equivalent.

Not all serial schedules are conflict equivalent to eachother.

For example, schedules 1 and 2 are not conflictequivalent.

The concept of conflict equivalence leads to the conceptof conflict serializability.

We say that a schedule S is conflict serializable if it isconflict equivalent to a serial schedule. Thus, schedule 3 is conflict serializable, since it is

conflict equivalent to the serial schedule 1


12/54


13/54


14/54

Finally, consider schedule 7 of Figure 14.9; it consists of only thesignificant operations (that is, the read and write) of transactions T3and T4.

This schedule is not conflict serializable, since it is not equivalentto either the serial schedule or the serial schedule.


15/54

Method for det conflict serializablity

a simple and efficient method for determining conflict serializabilityof a schedule.

Consider a schedule S. We construct a directed graph, called a precedence graph, fromS. This graph consists of a pairG = (V, E), where Vis a set of vertices

and Eis a set of edges. The set of vertices consists of all the transactions participating in the

schedule. The set of edges consists of all edges TiTjfor which one of three

conditions holds: 1. Tiexecutes write(Q) before Tjexecutes read(Q).

2. Tiexecutes read(Q) before Tjexecutes write(Q). 3. Tiexecutes write(Q) before Tjexecutes write(Q). If an edge Ti Tjexists in the precedence graph, then, in any serial

schedule Sequivalent to S, Timust appear before Tj.


16/54

A serializability orderof the transactionscan be obtained by finding a linear orderconsistent with the partial order of the

precedence graph.

This process is called topologicalsorting.


17/54

There are various concurrency-controlpolicies To ensure when multipletransactions are executed concurrently,

only acceptable schedules aregenerated, regardless of how theoperating system time-shares resources

(such as CPU time) among thetransactions.

As a trivial example of a concurrency control policy


18/54

As a trivial example of a concurrency-control policy,consider this:

A transaction acquires a lockon the entiredatabase before it starts and releases the lock

after it has committed.

While a transaction holds a lock, no othertransaction is allowed to acquire the lock, and

all must therefore wait for the lock to be released.

As a result of the locking policy, only onetransaction can execute at a time.

Therefore, only serial schedules are generated.

These are trivially serializable, and it is easy toverify that they are recoverable and cascadeless as

well.


19/54

A concurrency-control policy such as this oneleads to poor performance, since it forcestransactions to wait for preceding transactions tofinish before they can start.

In otherwords, it provides a poor degree ofconcurrency (indeed, no concurrency at all).

concurrent execution has substantial performancebenefits.

The goal of concurrency-control policies is toprovide a high degree of concurrency,whileensuring that all schedules that can begenerated are conflict or view serializable,recoverable, and cascadeless.


20/54

some of most important concurrency control mechanisms

1) Locking

Instead of locking the entire database, a transaction could, instead,lock only those data items that it accesses.

Under such a policy, the transaction must hold locks long enoughto ensure serializability, but for a period short enough not to harmperformance excessively.

Two-phase locking protocol, a simple, widely used technique thatensures serializability.

Stated simply, two-phase locking requires a transaction to have twophases, one where it acquires locks but does not release any, and

a second phase where the transaction releases locks but does notacquire any.


21/54

improvements to locking result if we have two kinds ofLocks Shared

Exclusive.

Shared locks are used for data that the transaction reads

Exclusive locks are used for those it writes.

Many transactions can hold shared locks on the same dataitem at the same time,

A transaction is allowed an exclusive lock on a data itemonly if no other transaction holds any lock (regardless ofwhether shared or exclusive) on the data item.

This use of two modes of locks along with two-phaselocking allows concurrent reading of data while stillensuring serializability.


22/54

2) Timestamps

implementation of isolation assigns each transaction atimestamp, typically when it begins

Foreach data item, the system keeps twotimestamps.

Read timestamp of a data item holds the largest (that is,the most recent) timestamp of those transactions thatread the data item.

The write timestamp of a data item holds the timestampof the transaction that wrote the current value of the dataitem.

Timestamps are used to ensure that transactions accesseach data item in order of the transactionstimestamps if their accesses conflict.

When this is not possible, offending transactions areaborted and restarted with a new timestamp


23/54

3) Multiple Versions andSnapshot Isolation

we can imagine that each transaction is givenits own version, or snapshot, of the databasewhen it begins.

It reads data from this private version and is

thus isolated from the updates made by othertransactions. If the transaction updates the database, that

update appears only in its own version, not in

the actual database itself. Information about these updates is saved so that

the updates can be applied to the realdatabase if the transaction commits

When a transaction T enters the partially committed state


24/54

When a transaction Tenters the partially committed state,it then proceeds to the committed state only if no otherconcurrent transaction has modified data that Tintends to update.

Transactions that, as a result, cannot commit abort instead. Snapshot isolation ensures that attempts to read data

never need to wait (unlike locking). Read-only transactions cannot be aborted; only those

that modify data run a slight risk of aborting.

Since each transaction reads its own version or snapshotof the database, reading data does not cause subsequentupdate attempts by other transactions to wait (unlikelocking).

Since most transactions are read-only (and most othersread more data than they update), this is often a majorsource of performance improvement as compared tolocking.

Major Problem : too much isolation


25/54

Concurrency Control

One of the fundamental properties of atransaction is isolation.

To ensure that it is, the system must control

the interaction among the concurrenttransactions; this control is achieved throughone of a variety of mechanisms calledconcurrencycont ro lschemes.

In practice, the most frequently used schemesare two-phase lockingand

snapshot isolation


26/54

Lock-Based Protocols

Oneway to ensure isolationdata itemsbe accessed in a mutually exclusivemanner; that is, while one transaction is

accessing a data item, no othertransaction can modify that data item.

The most common method used toimplement this requirement is to allow atransaction to access a data item only if itis currently holding a lock on that item


27/54

Locks

There are various modes in which a dataitem may be locked. two modes: 1. Shared. If a transaction Tihas obtained a

shared-mode lock (denoted by S) on item Q,then Tican read, but cannot write, Q.

2. Exclusive. If a transaction Tihas obtainedan exclusive-mode lock (denoted by X) onitem Q, then Tican both read and write Q.

We require that every transaction request a


28/54

We require that every transaction request alock in an appropriate mode on data item Q,depending on the types of operations that it

will perform on Q. The transaction makes the request to the

concurrency-control manager.

The transaction can proceed with theoperation only after the concurrency-controlmanagergrants the lock to the transaction.

The use of these two lock modes allows

multiple transactions to read a data item butlimits write access to just one transaction at atime.


29/54

A transaction requests a shared lock on data

item Q by executing lock-S(Q) instruction. Similarly, a transaction requests an exclusive

lock through thelock-X(Q) instruction.

A transaction can unlock a data item Q by theunlock(Q) instruction.

To access a data item transaction Ti must first


30/54

To access a data item, transaction Timust firstlock that item.

If the data item is already locked by another

transaction in an incompatible mode, the

concurrency control managerwill not grant thelock until all incompatible locks held by othertransactions have been released.

Thus, Tiis made to wait until all incompatiblelocks held by other transactions have beenreleased

it is not necessarily desirable for a transaction to

unlock a data item immediately after its finalaccess of that data item, since serializability maynot be ensured.


31/54

As an illustration, consider againthe banking example

LetA and B be two accounts that are accessed bytransactions T1 and T2

Transaction T1 transfers $50 from account B to accountA(Figure 15.2).

Transaction T2 displays the total amount of money in

accountsA and Bthat is, the sumA + B (Figure 15.3).


32/54

Suppose that the values of accountsAand B are $100 and $200, respectively.

If these two transactions are executed

serially, either in the orderT1, T2 or theorderT2, T1, then transaction T2 willdisplay the value $300.

If, however, these transactions areexecuted concurrently, then schedule 1, inFigure 15.4, is possible


33/54

Suppose now that unlocking is delayed to the


34/54

Suppose now that unlocking is delayed to theend of the transaction.

Transaction T3 corresponds to T1 withunlocking delayed (Figure 15.5).

Transaction T4 corresponds to T2 withunlocking delayed


35/54

Deadlock

Unfortunately, locking can lead to anundesirable situation.

Consider the partial schedule of Figure15.7 forT3 and T4.


36/54

If we do not use locking, or if we unlock data itemstoo soon after reading or writing them, we may getinconsistent states.

On the other hand, if we do not unlock a data itembefore requesting a lock on another data item,deadlocks may occur.

There are ways to avoid deadlock in some situations

We shall require that each transaction in the systemfollow a set of rules, called a locking protocol,indicating when a transaction may lock and unlockeach of the data items.

Locking protocols restrict the number of possibleschedules.

The set of all such schedules is a proper subset of allpossible serializable schedules


37/54

The Two-Phase Locking Protocol

One protocol that ensures serializability is the two-phase locking protocol.

This protocol requires that each transaction issue lockand unlock requests in two phases:

1. Growing phase. A transaction may obtain locks, butmay not release any lock.

2. Shrinking phase. A transaction may release locks,but may not obtain any new locks.

Initially, a transaction is in the growing phase.

The transaction acquires locks as needed. Once the transaction releases a lock, it enters the

shrinking phase, and it can issue no more lockrequests.

F l t ti T3 d T4 t h


38/54

For example, transactions T3 and T4 are two phase

Note that the unlock instructions do not need toappear at the end of the transaction.

For example, in the case of transaction T3, we couldmove the unlock(B) instruction to just after the lock-X(A) instruction, and still retain the two-phase lockingproperty

We can show that the two-phase locking protocolensures conflict serializability.

The point in the schedule where the transaction hasobtained its final lock (the end of its growing phase) is

called the lock point of the transaction. Now, transactions can be ordered according to their

lock points-a serializability ordering for thetransactions

Two phase locking does not ensure freedom


39/54

Two-phase locking does notensure freedomfrom deadlock.

Observe that transactions T3 and T4 aretwo phase, but, in schedule 2 (Figure 15.7),they are deadlocked.


40/54

Cascading rollback

In addition to being serializable, schedulesshould be cascadeless.

Cascading rollback may occur under two-phase locking.

As an illustration, consider the partialschedule of Figure 15.8.

.


41/54

Each transaction observes the two-phase

locking protocol, but the failure ofT5 afterthe read(A) step ofT7 leads to cascadingrollback ofT6 and T7


42/54

Cascading rollbacks can be avoided by amodification of two-phase locking called the

strict two-phase locking protocol. This protocol requires not only that locking be

two phase, but also that all exclusive-modelocks taken by a transaction be held until

that transaction commits.

This requirement ensures that any data writtenby an uncommitted transaction are locked inexclusive mode until the transaction commits,preventing any other transaction from readingthe data.

Another variant of two-phase locking is the


43/54

Another variant of two-phase locking is therigorous two-phase locking protocol,which requires that all locks be held until the

transaction commits. We can easily verify that, with rigorous two-

phase locking, transactions can be

serialized in the order in which they commit.


44/54

Consider the following two transactions, for which wehave shown only some of the significant read and writeoperations:

T8: read(a1);

read(a2);. . .

read(an);

write(a1).

T9: read(a1);read(a2);

display(a1 + a2).


45/54

If we employ the two-phase locking protocol,then T8 must lock a1 in exclusive mode.

Therefore, any concurrent execution of bothtransactions amounts to a serial execution.

However, that T8 needs an exclusive lock ona1 only at the end of its execution, when itwrites a1.

Thus, if T8 could initially lock a1 in shared

mode, and then could later change the lock toexclusive mode, we could get moreconcurrency, since T8 and T9 could access a1and a2 simultaneously.


46/54

Provide a mechanism for

upgrading a shared lock to an exclusive lock, and

downgrading an exclusive lock to a shared lock.

We denote conversion from shared to exclusive modes by upgrade, and

from exclusive to shared by downgrade.

Lock conversion cannot be allowed arbitrarily.

Rather, upgrading can take place in only thegrowing phase, whereas downgrading can takeplace in only the shrinking phase


47/54

Transactions T8 and T9 can run concurrently underthe refined two-phase locking protocol

a transaction attempting to upgrade a lock on anitem Q may be forced to wait. This enforced wait occurs ifQis currently

locked by anothertransaction in sharedmode.


48/54

two-phase locking with lock conversiongenerates only conflict-serializable schedules

if exclusive locks are held until the end of the

transaction, the schedules are cascadeless Strict two-phase locking and rigorous two-phase

locking (with lock conversions) are usedextensively in commercial database systems.


49/54

Implementation of Locking

A lock manager process that receivesmessages from transactions and sendsmessages in reply.

The lock-manager process replies to lock-

request messages with lock-grant messages,or with messages requesting rollback of thetransaction (in case of deadlocks).

Unlock messages require only anacknowledgment in response, but may resultin a grant message to another waitingtransaction.


50/54

The lock manager uses this data structure:lock table

For each data item that is currently


51/54

For each data item that is currentlylocked, it maintains a linked list of records,one for each request, in the order in which

the requests arrived. It uses a hash table, indexed on the name

of a data item, to find the linked list (if any)

for a data item; this table is called the locktable.

Each record of the linked list for a data item

notes which transaction made the request,and what lock mode it requested.

The record also notes if the request has

currently been granted

The lock manager processes


52/54

The lock manager processesrequests this way:

When a lock request message arrives, it adds a record to the end of the linked list for the data item, if

the linked list is present.

Otherwise it creates a new linked list, containing only the recordfor the request.

It always grants a lock request on a data item that isnot currently locked.

But if the transaction requests a lock on an item onwhich a lock iscurrently held, the lock managergrants

the request only if it is compatible with the locks thatare currently held, and all earlier requests have beengranted already.

Otherwise the request has to wait.

When the lock manager receives an unlock message froma transaction


53/54

a transaction, It deletes the record for that data item in the linked list

corresponding to that transaction. It tests the record that follows, if any, as described in the

previous paragraph, to see if that request can now begranted.

If it can, the lock manager grants that request, andprocesses the record following it, if any, similarly, and soon.

If a transaction aborts: Lock Manager deletes any waiting request made by the

transaction.

Once the database system has taken appropriate actionsto undo the transaction , it releases all locks held by theaborted transaction.

This algorithm guarantees freedom from


54/54

This algorithm guarantees freedom fromstarvation for lock requests, since a requestcan never be granted while a requestreceived earlier is waiting to be granted.

transacion.ppt

Documents