Concurrency Control 1 Concurrency Control m The main

Concurrency Control 1

Concurrency Control m The main aim of any Database Management System is to control requests for the same data, at the same time, from multiple users. m 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 m Purpose of Concurrency Control q To enforce Isolation (through mutual exclusion) among conflicting transactions. q To preserve database consistency through consistency preserving execution of transactions. q To resolve read-write and write-write conflicts. m Example: q In concurrent execution environment if T 1 conflicts with T 2 over a data item A, then the existing concurrency control decides if T 1 or T 2 should get the A and if the other transaction is rolled-back or waits. 3

Lock-Based Protocols m 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. m 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. m 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. q Generally, there is one lock for each data item in the database. m Lock requests are made to concurrency-control manager Transaction can proceed only after request is granted m 4

Lock-Based Protocols m Locking is an operation which secures permission to Read and Write a data item for a transaction. q Example: Ø Lock (X): Data item X is locked in behalf of the requesting transaction. m Unlocking is an operation which removes these permissions from the data item. q Example: Ø Unlock (X): Data item X is made available to all other transactions. m Lock and Unlock are Atomic operations. 5

Shared/Exclusive Locks m Data items can be locked in two modes : q 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. q 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. m Conflict matrix q Lock-compatibility matrix 6

Shared/Exclusive Locks A transaction may be granted a lock on an item if q the requested lock is compatible with locks already held on the item by other transactions m Any number of transactions can hold shared locks on an item, but q if any transaction holds an exclusive on the item no other transaction may hold any lock on the item m If a lock cannot be granted, q the requesting transaction is made to wait till all incompatible locks held by other transactions have been released. q The lock is then granted. m 7

Shared/Exclusive Locks m 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 m 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 m 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

m 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)-1 if no_of_reads (X) = 0 then begin LOCK (X) = “unlocked”; wake up one of the transactions, if any end; 11

Pitfalls of Lock-Based Protocols m Consider the partial schedule m Neither T 3 nor T 4 can make progress q executing lock-S(B) causes T 4 to wait for T 3 to release its lock on B, while executing lock-X(A) causes T 3 to wait for T 4 to release its lock on A. 12

Pitfalls of Lock-Based Protocols m Such a situation is called a deadlock. m To handle a deadlock, q one of T 3 or T 4 must be rolled back and its locks released. m The potential for deadlock exists in most locking protocols. m Deadlocks are a necessary evil. 13

Dealing with Deadlock m m A partial schedule of T 1 and T 2 that is in a state of deadlock. A wait-for graph for the partial schedule in (a). 14

Dealing with Starvation m Starvation is the situation in which a transaction cannot proceed for an indefinite period of time while other transactions in the system continue normally. m For example: q 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. q The same transaction is repeatedly rolled back due to deadlocks. m One solution for starvation is to have a fair scheme, such as using a first-come-first-served. 15

The Two-Phase Locking Protocol m This protocol ensures conflict-serializable schedules m If all transactions obey the 2 PL then all possible interleaved schedules are serializable. m This protocol requires that each transaction issues lock and unlock requests in two phases: q Phase 1: Growing Phase Øtransaction may obtain locks but may not release locks q Phase 2: Shrinking Phase Øtransaction may release locks but may not obtain locks 16

The Two-Phase Locking Protocol m No transaction should request a lock after it releases one of its locks. 17

The Two-Phase Locking Protocol m It can be proved that the transactions can be serialized in the order of their lock points qa point where a transaction acquired its final lock q end m of its growing phase 2 PL does not ensure freedom from deadlocks 18

Two-Phase Locking m m m Transactions that do not obey 2 PL Two transactions T 1 and T 2. Results of possible serial schedules of T 1 and T 2 19 T 1

Two-Phase Locking m m Transactions that do not obey 2 PL (c) A nonserializable schedule S that uses locks. 20

Two-Phase Locking m Transactions T 1 & T 2 , which are the same as T 1 & T 2 but which follow 2 PL. 21

Strict Two-Phase Locking (S 2 PL) m To avoid cascading rollback, follow a modified protocol called Strict 2 PL (S 2 PL). q 2 PL qa transaction must hold all its exclusive locks till it commits/aborts. m S 2 PL does not prevent deadlocks T commits 22 time

Rigorous two-phase locking (R 2 PL) m R 2 PL is even stricter q all locks are held till commit/abort. q In this protocol, transactions can be serialized in the order in which they commit. m S 2 PL permits higher degree of concurrency than R 2 PL but less than 2 PL. 23

Lock Conversions m 2 PL with lock conversions: q 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 q 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) m This protocol assures serializability 24

Timestamp-Based Protocols Each transaction is issued a timestamp when it starts m CC techniques based on timestamp ordering do no use locks, and thus deadlocks cannot occur (no transaction ever waits) q may not be (cascadeless and recoverable) m The protocol manages concurrent execution such that the time-stamps determine the serializability order. m 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) m 25

Timestamp-Ordering Protocol m In order to assure such behavior, the protocol maintains for each data Q two timestamp values: q 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. q 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. m These TSs are updated whenever a new read(Q) or write(Q) is executed 26

Timestamp-Ordering Protocol m The timestamp ordering protocol ensures that any conflicting read and write operations are executed in timestamp order m Suppose a transaction T issues read(Q) 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. q 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 m Suppose that transaction T issues write(Q). 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. q If q Otherwise, the write operation is executed, and W-TS(Q) = TS(T) 28

Example Use of the Protocol m TS(T 1) = 1, TS(T 2) = 2, TS(T 3) = 3 T 1 read(A) T 2 T 3 read(A) R-TS(A) 0 1 3 3 W-TS(A) 0 0 write(A) rejected 29

m Transactions timestamps are 1, 2, 3, 4, 5 T 1 T 2 T 3 T 4 R-TS (X, Y, Z) W-TS (X, Y, Z) T 5 (0, 0, 0) read(X) (5, 0, 0) (5, 2, 0) (5, 2, 5) (0, 0, 0) (0, 3, 3) (0, 3, 3) read(Y) write(Y) write(Z) read(X) write(Z) reject & roll back Example Use of the Protocol write(Y) write(Z) 30

Correctness of Timestamp-Ordering Protocol m The timestamp-ordering protocol guarantees serializability since all the arcs in the precedence graph are of the form: transaction with smaller timestamp m transaction with larger timestamp Thus, there will be no cycles in the precedence graph 31

Recoverability and Cascade Freedom m Problem with timestamp-ordering protocol: q Suppose Ti aborts, but Tj has read a data item written by Ti, then Tj must abort q If Tj had been allowed to commit earlier, the schedule is not recoverable. q Further, any transaction that has read a data item written by Tj must abort q This can lead to cascading rollback Ø a chain of rollbacks 32

Recoverability and Cascade Freedom m Solution: q A transaction is structured such that its writes are all performed at the end of its processing q All writes of a transaction form an atomic action; no transaction may execute while a transaction is being written q. A transaction that aborts is restarted with a new timestamp 33

Thomas’ Write Rule m m 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. m Otherwise this protocol is the same as the timestamp ordering protocol. m Thomas' Write Rule allows greater potential concurrency. 34

Deadlock Handling Consider the following two transactions: T 1: write (A) T 2: write(B) write(A) m Schedule with deadlock m 35

Deadlock Prevention m Deadlock prevention protocols ensure that the system will never enter into a deadlock state. m Some prevention strategies : q Requires that each transaction locks all its data items before it begins execution. Ø Low degree of concurrency 36

Deadlock Prevention m Conservative 2 PL (static & deadlock-free): q requires a transaction T to pre-declare all the read & write set of items; and lock all these items before T begins execution. q 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 m m Assume that Ti requests a data item currently held by Tj. wait-die scheme: q If Ti is older than Tj (i. e. , TS(Ti) TS(Tj)) q Then wait(Ti) q Else die(Ti) Ø Ti is aborted and restarted with its old starting time. m Younger transactions never wait for older ones; they are rolled back instead. m A transaction may die several times before acquiring needed data item 38

Wait-Die: Example T 1 (ts =10) wait T 2 wait? (ts =20) T 3 wait (ts =25) 39

Deadlock Prevention m wound-wait scheme q If Ti is older than Tj (i. e. , TS(Ti) TS(Tj)) q then wound(Tj) // Tj is wounded by Ti Ø Tj is aborted and restart it with its old starting time. q else (Ti is younger than Tj) wait(Ti) m Older transaction wounds (forces rollback) of younger transaction instead of waiting for it. m Younger transactions may wait for older ones. m May be fewer rollbacks than wait-die scheme. 40

Deadlock Prevention Older transactions thus have precedence over newer ones, and starvation is hence avoided. m Example: q T 1: W(X) W(Y) q T 2: W(Y) W(X) q T 1 is older. m m wait-die: q m X-Lock 2(Y) wait(T 1, Y) X-Lock 2(Y) abort(T 2) … wound-wait: q X-Lock 1(X) 41 X-Lock 1(Y) …

Wound-Wait: Example T 1 (ts =25) wait T 2 wait (ts =20) T 3 wait (ts =10) 42

Timeout-Based Schemes m If a transaction waits for a lock more than a specified amount of time, the transaction is rolled back. q deadlocks are not possible q simple to implement q starvation is possible q difficult to select a good timeout value 43

Deadlock Detection m Deadlocks can be described as a wait-for graph, which consists of a pair G = (V, E), q V is a set of vertices (all the transactions) q 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. q When Ti requests a data item currently being held by Tj, then the edge Ti Tj is inserted in the waitfor graph. q This edge is removed only when Tj is no longer holding a data item needed by Ti. q If 44

Deadlock Detection The system is in a deadlock state if and only if the wait-for graph has a cycle. m Must invoke a deadlock-detection algorithm periodically to look for cycles. m Wait-for graph without a cycle Wait-for graph with a cycle 45

Deadlock Recovery When deadlock is detected : q Some transaction will have to rolled back (made a victim) to break deadlock. q Select that transaction as victim that will incur minimum cost. m Factors in selecting a victim transaction: q The amount of effort already made in the transaction. q The cost of aborting the transaction. m ØIt may cause cascading aborts. q How close the transaction is to complete? q The number of deadlocks that can be broken when the transaction is aborted. 46

Deadlock Recovery m When deadlock is detected: q 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. q Starvation happens if same transaction is always chosen as victim. ØInclude the number of rollbacks in the cost factor to avoid starvation 47