concurrency control techniques

CSCI 453 -- Cocurrency Control Techniques

1

CONCURRENCY CONTROLTECHNIQUES

Dr. Awad KhalilDr. Awad Khalil

Computer Science DepartmentComputer Science Department

AUCAUC


2

Content

Locking TechniquesLocking Techniques

Binary LockBinary Lock

Multiple Mode LockMultiple Mode Lock Two Phase Locking (2PL)Two Phase Locking (2PL) Variations of 2PLVariations of 2PL DeadlockDeadlock Deadlock ManagementDeadlock Management


3

Locking Techniques

AA LOCK LOCK isis a variable associated with a data item in the a variable associated with a data item in the database and describes the status of that item with database and describes the status of that item with respect to possible operations that can be applied to the respect to possible operations that can be applied to the item.item.

Generally, there is one Generally, there is one lock lock for each data item in the for each data item in the database.database.

LocksLocks are used as means of synchronizing the access by are used as means of synchronizing the access by concurrent transactions to the database items.concurrent transactions to the database items.


4

Binary Lock

A A binary lockbinary lock can have two states or values: can have two states or values: lockedlocked and and unlocked unlocked (or (or 1 1 and and 00, for simplicity)., for simplicity).

A distinct lock is associated with each database item A distinct lock is associated with each database item XX.. If the value of the lock on If the value of the lock on XX is is 11, the item , the item XX cannot be accessed cannot be accessed

by a database operation that requests the item.by a database operation that requests the item. If the value of the lock on If the value of the lock on XX is is 00, the item , the item XX can be accessed can be accessed

when requested.when requested. We refer to the value of the lock associated with item We refer to the value of the lock associated with item XX as as

LOCK(X).LOCK(X).


5

Binary Lock (Cont’d)

Two operations, Two operations, lock_itemlock_item and and unlock_itemunlock_item, must be , must be included in the transactions when binary locking is used.included in the transactions when binary locking is used.

A transaction requests access to an item A transaction requests access to an item XX by issuing a by issuing a lock_item(X)lock_item(X) operation. If operation. If LOCK(X)LOCK(X) = 1, the transaction is = 1, the transaction is forced to wait; otherwise the transaction sets forced to wait; otherwise the transaction sets LOCK(X) LOCK(X) := 1 := 1 (locks the item) and is allowed access. (locks the item) and is allowed access.

When the transaction is through using the item, it issues When the transaction is through using the item, it issues unlock_item(X)unlock_item(X) operation, which sets operation, which sets LOCK(X) LOCK(X) := 0 (unlocks := 0 (unlocks the item) so that the item) so that XX may be accessed by other transactions. may be accessed by other transactions.


6

Binary Lock - Operationslock_item(X):B: if LOCK(X) = 0 (* item is unlocked *)B: if LOCK(X) = 0 (* item is unlocked *) then LOCK(X) then LOCK(X) 1 (* lock the item *) 1 (* lock the item *) else beginelse begin wait (until lock(X)=0 andwait (until lock(X)=0 and the lock manager wakes up the lock manager wakes up

the transaction); the transaction); go to Bgo to B end;end;

unlock_item(X):LOCK(X) LOCK(X) 0 (* unlock the item *) 0 (* unlock the item *)if any transactions are waiting then wakeup one of the waiting if any transactions are waiting then wakeup one of the waiting

transactions; transactions;


7

Binary Lock - Rules A transaction T must issue the operation lock_item(X) A transaction T must issue the operation lock_item(X)

before any read_item(X) or write_item(X) operations are before any read_item(X) or write_item(X) operations are performed in T.performed in T.

A transaction T must issue the operation unlock_item(X) A transaction T must issue the operation unlock_item(X) after all read_item(X) and write_item(X) operations are after all read_item(X) and write_item(X) operations are completed in T.completed in T.

A transaction T will not issue a lock_item(X) operation if A transaction T will not issue a lock_item(X) operation if

it already holds the lock on item X.it already holds the lock on item X. A transaction A transaction TT will not issue an will not issue an unlock_item(X)unlock_item(X)

operation unless it already holds the lock on operation unless it already holds the lock on XX..


8

Multiple-mode Lock

This is aThis is a multiple-mode lock multiple-mode lock, providing three locking operations: , providing three locking operations: read_lock(X),read_lock(X), write_lock(X),write_lock(X), and and unlock(X).unlock(X).

A lock associated with an item A lock associated with an item XX, , LOCK(X),LOCK(X), has three possible states: has three possible states:

“read-locked”“write-locked”“unlocked”

A read-locked item is also called A read-locked item is also called shared-lockedshared-locked, because other , because other transactions are allowed to read the item.transactions are allowed to read the item.

A write-locked item is called A write-locked item is called exclusive-lockedexclusive-locked, because a single , because a single transaction exclusively holds the lock on the item.transaction exclusively holds the lock on the item.


9

Multiple-mode Lock – Read_Lock(X)

read_lock(X):read_lock(X):B: if LOCK(X)=“unlocked”B: if LOCK(X)=“unlocked”then begin LOCK(X) then begin LOCK(X) “read_locked”; “read_locked”; no_of_reads(X)no_of_reads(X) 1 1 endendelse if LOCK(X)=“read_locked” else if LOCK(X)=“read_locked” then then no_of_reads(X)no_of_reads(X) no_of_reads(X)+1 no_of_reads(X)+1 elseelse begin wait (until LOCK(X)=“unlocked”begin wait (until LOCK(X)=“unlocked”

and the lock managerand the lock manager wakes up the transaction);wakes up the transaction); go to Bgo to B end;end;


10

Multiple-mode Lock – Write_Lock(X)

write_lock(X):write_lock(X):

B: if LOCK(X)=“unlocked”B: if LOCK(X)=“unlocked”

then LOCK(X) then LOCK(X) “write_locked”; “write_locked”;

else beginelse begin

wait (until LOCK(X)=“unlocked”wait (until LOCK(X)=“unlocked”

and the lock managerand the lock manager

wakes up the transaction);wakes up the transaction);

go to Bgo to B

end;end;


11

Multiple-mode Lock – Unlock_Item(X)

unlock_ltem(X):unlock_ltem(X):if LOCK(X)=“write_locked”if LOCK(X)=“write_locked”then beginthen begin LOCK(X) LOCK(X) “unlocked”; “unlocked”; wakeup one of the transactions, if any wakeup one of the transactions, if any endendelse if LOCK(X)=“read_locked”else if LOCK(X)=“read_locked”

thenthen beginbegin no_of_reads(X)no_of_reads(X) no_of_reads(X)-1; no_of_reads(X)-1; if no_of_reads(X)=0if no_of_reads(X)=0 then begin LOCK(X)=“unlocked”;then begin LOCK(X)=“unlocked”; wakeup one of the transactions wakeup one of the transactions end end end; end;


12

Multiple-mode Lock – The Rules

A transaction A transaction TT must issue the operation must issue the operation read_lock(X) read_lock(X) or or write_lock(X) write_lock(X) before any before any read_item(X) read_item(X) operation is performed in operation is performed in TT..

A transaction A transaction TT must issue the operation must issue the operation write_lock(X) write_lock(X) before any before any write_item(X) write_item(X) operation is performed in operation is performed in TT..

A transaction A transaction TT must issue the operation must issue the operation unlock_item(X)unlock_item(X) after all after all read_item(X)read_item(X) and and write_item(X)write_item(X) operations are completed in operations are completed in TT..

A transaction A transaction TT will not issue a will not issue a read_lock(X)read_lock(X) operation if it already holds operation if it already holds a read (shared) lock or write (exclusive) lock on item a read (shared) lock or write (exclusive) lock on item XX..

A transaction A transaction TT will not issue a will not issue a write_lock(X)write_lock(X) operation if it already holds operation if it already holds a write (exclusive) lock on item a write (exclusive) lock on item XX..

A transaction A transaction TT will not issue an will not issue an unlock(X)unlock(X) operation unless it already operation unless it already holds a read (shared) lock or a write (exclusive) lock on item holds a read (shared) lock or a write (exclusive) lock on item XX..


13

Two-Phase Locking (2PL)

A transaction is said to follow the A transaction is said to follow the two-phasetwo-phase locking protocollocking protocol if all locking operations (if all locking operations (read_lock, write_lockread_lock, write_lock) precede the ) precede the first unlock operation in the transaction.first unlock operation in the transaction.

Such a transaction can be divided into two phases:Such a transaction can be divided into two phases:

Expanding phaseExpanding phase, during which new locks on items can , during which new locks on items can

be acquired but none can be released; andbe acquired but none can be released; and

Shrinking phaseShrinking phase, during which existing locks can be , during which existing locks can be released but no new locks can be acquired.released but no new locks can be acquired.


14

Two-Phase Locking (2PL)


15

Two-Phase Locking – An Example

T1’T1’ T2’ T2’ ______________________ __________________

read_lock(Y);read_lock(Y); read_lock(X);read_lock(X);read_ltem(Y);read_ltem(Y); read_ltem(X);read_ltem(X);write_lock(X);write_lock(X); write_lock(Y);write_lock(Y);unlock(Y);unlock(Y); unlock(X);unlock(X); X:=X+Y;X:=X+Y; Y:=X+Y;Y:=X+Y;write_ltem(X);write_ltem(X); write_item(Y); write_item(Y); unlock(X);unlock(X); unlock(Y);unlock(Y);


16

Two-Phase Locking – Limitations

It can be proved that, if every transaction in a schedule It can be proved that, if every transaction in a schedule follows the two-phase locking protocol, the schedule is follows the two-phase locking protocol, the schedule is guaranteed to be guaranteed to be serializableserializable..

Two-phaseTwo-phase locking may limit the amount of locking may limit the amount of concurrency that can occur in a schedule. This is concurrency that can occur in a schedule. This is because a transaction because a transaction TT may not be able to release an may not be able to release an item item XX after it is through using it if after it is through using it if TT must lock an must lock an additional item additional item YY later on; or conversely. later on; or conversely.


17

Conservative Two-Phase Locking

Conservative 2PLConservative 2PL (or (or static 2PLstatic 2PL) requires a ) requires a transaction to lock all the items it accesses transaction to lock all the items it accesses beforebefore the transaction begins execution, by the transaction begins execution, by predeclaring its read set and write set. If any of predeclaring its read set and write set. If any of the predeclared items needed cannot be locked, the predeclared items needed cannot be locked, the transaction does not lock any item; instead, the transaction does not lock any item; instead, it waits until all the items are available for it waits until all the items are available for locking.locking.


18

Strict Two-Phase Locking

InIn Strict 2PLStrict 2PL locking scheme, a transaction locking scheme, a transaction does not release an item that is written by T does not release an item that is written by T unless T has committed, leading to a strict unless T has committed, leading to a strict schedule for recoverability.schedule for recoverability.


19

DEADLOCK

DeadlockDeadlock occurs when each of two transactions occurs when each of two transactions is waiting for the other to release the lock on an is waiting for the other to release the lock on an item.item.


20

Deadlock Management

Deadlock prevention protocolsDeadlock prevention protocols

Deadlock Detection ProtocolsDeadlock Detection Protocols


21

Deadlock Prevention Protocols

(1) Conservative 2PL(1) Conservative 2PL

Conservative 2PL protocol Conservative 2PL protocol requires every requires every transaction lock all the items it needs in advance; if transaction lock all the items it needs in advance; if any of the items cannot be obtained, none of the items any of the items cannot be obtained, none of the items are locked. This solution obviously limits are locked. This solution obviously limits concurrency.concurrency.


22

Deadlock Prevention Protocols (Cont’d)

(2) Wait-die and Wound-wait(2) Wait-die and Wound-wait

Wait-die and wound-wait Wait-die and wound-wait useuse the concept of transaction the concept of transaction timestamp timestamp TS(T),TS(T), which is a unique identifier assigned to each which is a unique identifier assigned to each transaction. The timestamps are ordered based on the order in transaction. The timestamps are ordered based on the order in which transactions are started.which transactions are started.

Suppose that transaction Suppose that transaction T1T1 starts before transaction starts before transaction T2T2, then , then TS(T1)TS(T1) TS(T2).TS(T2).

Suppose that transaction Suppose that transaction TiTi tries to lock an item tries to lock an item XX, but is not able , but is not able

to because to because X X is locked by some other transaction is locked by some other transaction TJTJ with a with a conflicting lock. The rules followed by the conflicting lock. The rules followed by the wait-diewait-die and and wound-wound-waitwait schemes are as follows: schemes are as follows:


23


Wait-die:

If TS(Ti) If TS(Ti) TS(Tj) then Ti is allowed to wait otherwise abort TS(Tj) then Ti is allowed to wait otherwise abort Ti and restart it later with the same timestamp.Ti and restart it later with the same timestamp.

Wound-wait:

If TS(Ti) If TS(Ti) TS(Tj) then abort Tj and restart it later with the TS(Tj) then abort Tj and restart it later with the same timestamp otherwise Ti is allowed to wait.same timestamp otherwise Ti is allowed to wait.


24


(3) No Waiting (NW) Protocol(3) No Waiting (NW) Protocol

In theIn the no waiting no waiting algorithm, if a transaction is unable algorithm, if a transaction is unable

to obtain a lock, it is immediately aborted and then to obtain a lock, it is immediately aborted and then restarted after a certain time delay without checking restarted after a certain time delay without checking whether a deadlock will actually occur or not. This whether a deadlock will actually occur or not. This scheme can cause transactions to abort and restart scheme can cause transactions to abort and restart needlessly.needlessly.


25


(4) Cautious Waiting (CW) Protocol(4) Cautious Waiting (CW) Protocol

The cautiousThe cautious waiting waiting approach is proposed to reduce the approach is proposed to reduce the number of needless aborts/restarts. Suppose that number of needless aborts/restarts. Suppose that transaction transaction TiTi tries to lock an item X but is not able to tries to lock an item X but is not able to do so because do so because XX is locked by some other transaction is locked by some other transaction TjTj with a conflicting lock. The cautious waiting rules are as with a conflicting lock. The cautious waiting rules are as follows:follows:

Cautious Waiting: If Tj is not blocked (not waiting for some other If Tj is not blocked (not waiting for some other

locked item) then Ti is blocked and allowed to locked item) then Ti is blocked and allowed to wait otherwise abort Ti.wait otherwise abort Ti.


26


(5) Timeout Scheme(5) Timeout Scheme

In that scheme, if a transaction waits longer than a In that scheme, if a transaction waits longer than a system-defined timeout, the system assumes that system-defined timeout, the system assumes that the transaction is deadlocked and aborts it, the transaction is deadlocked and aborts it, regardless of whether a deadlock situation actually regardless of whether a deadlock situation actually exists.exists.


27

Deadlock Detection Protocols

Wait-for graph:Wait-for graph: One node is created in the wait-for graph for each transaction that is currently One node is created in the wait-for graph for each transaction that is currently

executing in the schedule. Whenever a transaction executing in the schedule. Whenever a transaction TiTi is waiting to lock an item X is waiting to lock an item X that is currently locked by a transaction that is currently locked by a transaction TJTJ, create a directed edge (, create a directed edge (TiTi TjTj ). ).

When When TjTj releases the lock(s) on the items that releases the lock(s) on the items that TiTi was waiting for, the directed was waiting for, the directed edge is dropped from the edge is dropped from the wait-forwait-for graph. graph.

We have a state of deadlock if and only if theWe have a state of deadlock if and only if the wait-for wait-for graph has a graph has a cycle cycle..

If the system is in a state of deadlock, some of the transactions causing the If the system is in a state of deadlock, some of the transactions causing the deadlock must be aborted. Choosing which transaction to abort is known asdeadlock must be aborted. Choosing which transaction to abort is known as victim selectionvictim selection..

The algorithm for The algorithm for victim selectionvictim selection should generally avoid selecting transactions should generally avoid selecting transactions that have been running for a long time and that have performed many updates, that have been running for a long time and that have performed many updates, and should try instead to select transactions that have not many changes or that and should try instead to select transactions that have not many changes or that are involved in more than one deadlock cycle in the wait-for graph.are involved in more than one deadlock cycle in the wait-for graph.


28

Live Lock LivelockLivelock occurs when a transaction cannot proceed for an occurs when a transaction cannot proceed for an

indefinite period of time while other transactions in the indefinite period of time while other transactions in the system continue normally. This may occur if the waiting system continue normally. This may occur if the waiting scheme for locked items is unfair, giving priority to some scheme for locked items is unfair, giving priority to some transactions over others. The standard solution for livelock is transactions over others. The standard solution for livelock is to have fair waiting scheme, e.g., to have fair waiting scheme, e.g., a first-come-first-servea first-come-first-serve queue.queue.

StarvationStarvation problem, can also occur in the algorithms for problem, can also occur in the algorithms for dealing with deadlock. It occurs if the algorithms select the dealing with deadlock. It occurs if the algorithms select the same transaction as victim repeatedly; thus causing it to same transaction as victim repeatedly; thus causing it to abort and never finish execution. The abort and never finish execution. The wait-diewait-die and and wound-wound-waitwait schemes avoid starvation. schemes avoid starvation.


29

Granularity A database item could be chosen to be one of the following:A database item could be chosen to be one of the following:

A database record.A database record. A field value of a database record.A field value of a database record. A disk block.A disk block. A whole file.A whole file. The whole database.The whole database.

The size of data items is often called the The size of data items is often called the data item granularitydata item granularity..

Several trade-offs must be considered in choosing the Several trade-offs must be considered in choosing the data item granularitydata item granularity..

Fine granularityFine granularity refers to small item sizes, whereasrefers to small item sizes, whereas course granularitycourse granularity refers to large item sizesrefers to large item sizes..

The larger the data item granularity is, the lower is the degree of concurrency.The larger the data item granularity is, the lower is the degree of concurrency.

The smaller the data item granularity is, the more items will exist in the The smaller the data item granularity is, the more items will exist in the database, the higher is the overhead to manage locks of items, and the higher database, the higher is the overhead to manage locks of items, and the higher is the storage space that will be required to maintain the lock table.is the storage space that will be required to maintain the lock table.


30

Granularity (Cont’d)

Reading:Reading:

““Multiple Granularity Level Locking”Multiple Granularity Level Locking”

Page 679-682, “El Masry” Third EditionPage 679-682, “El Masry” Third Edition


31

Thank you

concurrency control techniques

Documents