Database System Implementation CSE 507 Concurrency Control Some
Database System Implementation CSE 507 Concurrency Control Some slides adapted from Navathe et. Al. , Silberchatz et. Al and Hector Garcia-Molina
Lock Based Protocols § A lock is a mechanism to control concurrent access to a data item § Data items can be locked in two modes : 1. exclusive (X) mode. Data item can be both read as well as written. X-lock is requested using lock-X instruction. 2. shared (S) mode. Data item can only be read. S-lock is requested using lock-S instruction. § Lock requests are made to the concurrency-control manager by the programmer. Transaction can proceed only after request is granted.
Lock Compatibility Matrix § Lock-compatibility matrix § Lock granted if the requested lock is compatible with locks already held on the item by other transactions § Multiple shared locks on an item § But only one exclusive on an item § If a lock cannot be granted, then need to wait.
Lock Compatibility Matrix § Example of a transaction performing locking: T 2: lock-S(A); read (A); unlock(A); lock-S(B); read (B); unlock(B); display(A+B) T 3: lock-X(B); read (B); B = B – 50; write(B); unlock(B); lock-X(A); read(A); A = A + 50; write(A); unlock(A); If T 2 was executed here in midst
Lock Compatibility Matrix § Locking as done in previous example is not sufficient to guarantee serializability. § What if T 2 was executed in the middle of T 3? § Locking protocol is a set of rules followed by all transactions while requesting and releasing locks. Locking protocols restrict the set of possible schedules.
2 Phase Locking Protocol § Phase 1: Growing Phase § Transaction may obtain locks § Transaction may not release locks § Phase 2: Shrinking Phase § Transaction may release locks § Transaction may not obtain locks
2 Phase Locking Protocol: example T 2: lock-S(A); read (A); unlock(A); lock-S(B); read (B); unlock (A) unlock(B); display(A+B) T 3: lock-X(B); read (B); B = B – 50; unlock(B); Lock-X(A) read(A); A = A + 50; write(A); unlock(B) unlock(A); Does this guarantee serializability?
2 Phase Locking Protocol § This protocol ensures conflict-serializable schedules. § It can be proved that the transactions can be serialized in the order of their lock points (i. e. , the point where a transaction acquired its final lock).
§ Does 2 Phase Locking support recoverability T 2: lock-X(B); read (B); B = B – 50; Lock-X(A) read(A); A = A + 50; write(A); unlock(B); unlock(A); § And cascadeless? Time T 3: lock-S(A); read (A); lock-S(B); read (B); unlock (A) unlock(B); display(A+B); Commit; Abort;
§ Strict 2 Phase Locking T 2: lock-X(B); read (B); B = B – 50; Lock-X(A) read(A); A = A + 50; write(A); unlock(B); unlock(A); § All exclusive locks to be held until transactions commits; § Guarantees “Strict” schedules (recall discussion on recovery) Time T 3: lock-S(A); read (A); lock-S(B); read (B); unlock (A) unlock(B); display(A+B); Commit; Abort;
§ Rigorous 2 Phase Locking T 2: lock-X(B); read (B); B = B – 50; Lock-X(A) read(A); A = A + 50; write(A); unlock(B); unlock(A); § All locks to be held until transactions commits; § The serializability order === the commit order § More intuitive behavior for the users Time T 3: lock-S(A); read (A); lock-S(B); read (B); unlock (A) unlock(B); display(A+B); Commit; Abort;
Lock Conversions § Two-phase locking with lock conversions: – First Phase: § can acquire a lock-S on item § can acquire a lock-X on item § can convert a lock-S to a lock-X (upgrade) – Second Phase: § can release a lock-S § can release a lock-X § can convert a lock-X to a lock-S (downgrade) T 1: Read (A) read (B); Read(C); Read(D); Write(B)
Automatic Acquisition of Locks § A transaction Ti issues the standard read/write instruction, without explicit locking calls. § The operation read(D) is processed as: if Ti has a lock on D then read(D) else begin if necessary wait until no other transaction has a lock-X on D grant Ti a lock-S on D; read(D) end
Automatic Acquisition of Locks § write(D) is processed as: if Ti has a lock-X on D then write(D) else begin if necessary wait until no other transaction has any lock on D, if Ti has a lock-S on D then upgrade lock on D to lock-X else grant Ti a lock-X on D write(D) end; § All locks are released after commit or abort
Show application of 2 PL on following: (a) r 1 (X); r 2 (X); w 1(X); r 3(X); w 2(X) (b) r 2 (X); r 3 (X); w 3(X); w 1(X); w 2(X) (c) r 3 (X); r 1 (X); w 3(X); r 2(X); w 1(X) (d) r 3 (X); r 2 (X); r 1(X); w 3(X); w 1(X)
Multiple granularity of Locking § Allow data items to be of various sizes and define a hierarchy of data granularities, § Small granularities are nested within larger ones § Represented graphically as a tree. § When a transaction locks a node in the tree explicitly, it implicitly locks all the node's descendents in the same mode.
Multiple granularity of Locking § Granularity of locking (level in tree where locking is done): § fine granularity (lower in tree): high concurrency, high locking overhead § coarse granularity (higher in tree): low locking overhead, low concurrency
Multiple granularity of Locking Granularities § Entire database § Entire file § A disk block § A database record
Multiple granularity of Locking To manage such hierarchy, in addition to read and write, three additional locking modes are defined: § Intention-shared (IS): indicates that a shared lock(s) will be requested on some descendent nodes(s). § Intention-exclusive (IX): indicates that an exclusive lock(s) will be requested on some descendent node(s). § Shared-intention-exclusive (SIX): indicates that the current node is locked in shared mode but an exclusive lock(s) will be requested on some descendent nodes(s).
Multiple granularity of Locking SIX Implicitly S You can consider SIX as a combination of S and IX lock modes. It is the stricter of the two. A transaction can get the affect of SIX by getting S and IX modes.
Multiple granularity of Locking § These locks are applied using the following compatibility matrix: IS -- Intentionshared IX -- Intentionexclusive SIX -- Sharedintention-exclusive
Multiple granularity of Locking --- Rules 1. The lock compatibility must adhered to. 2. The root of the tree must be locked first, in any mode. . 3. A node N can be locked by a transaction T in S or IX mode only if the parent node is already locked by T in either IS or IX mode. 4. A node N can be locked by T in X, IX, or SIX mode only if the parent of N is already locked by T in either IX or SIX mode. 5. T can lock a node only if it has not unlocked any node (to enforce 2 PL policy). 6. T can unlock a node, N, only if none of the children of N are currently locked by T. 7. locks need to released bottom first.
Some Examples on Multi-granular Locking Query 1: Read the entire Emp table to compute average salary § Lock the root (database) in IS mode, then Emp table file in S mode. § You can also lock the Emp table in IS mode; then pages in IS mode and then keep requesting for S lock on the records. Query 2: Modify the head Aadhar number of CSE Dept. § Lock the root (database) in SIX mode, then Dept table file in SIX § Now we can search through the pages till we get the record corresponding to CSE dept. On that page we can get an X lock. Or we can get an IX lock on that page and get an X lock on the record. § If we had a B+ tree on Dept file, then we would first get the record address of CSE dept. Now we can get IX locks from root the corresponding page, and get an X lock on the record.
Some Examples on Multi-granular Locking Query 3: Insert a record into Dept table at the end of file § Lock the root (database) in IX mode, § Then Dept table file in X mode. § One can also lock the Dept table file in IX and then get an X lock on the page where record is being inserted but that may lead to incorrect summary problem.
Deadlocks in 2 phase locking § Neither T 3 nor T 4 can make progress. § Such a situation is called a deadlock. § To handle a deadlock one of T 3 or T 4 must be rolled back and its locks released.
Deadlocks in 2 phase locking § 2 PL does not ensure freedom from deadlocks. § In addition, there is a possibility of starvation. Starvation examples: § 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. § The same transaction is repeatedly rolled back due to deadlocks.
Deadlock Handling Deadlock prevention protocols ensure that the system will never enter into a deadlock state. § Some prevention strategies : § Transaction locks all its data items before it begins execution (predeclaration). § Impose partial ordering of all data items and require that a transaction can lock data items only in the order specified by the partial order.
Deadlock Handling – Prevention Schemes Following schemes use transaction timestamps. § wait-die scheme — non-preemptive § older transaction may wait for younger one to release data item. (older means smaller timestamp) § Younger transactions never wait for older ones; they are rolled back instead. § a transaction may die several times before acquiring needed data item
Deadlock Handling – Prevention Schemes § wound-wait scheme — preemptive § Oder transaction wounds (forces rollback) of younger transaction instead of waiting for it. § Younger transactions may wait for older ones. § may be fewer rollbacks than wait-die scheme.
Deadlock Handling – Prevention Schemes § Both in wait-die and in wound-wait schemes, a rolled back transactions is restarted with its original timestamp. § Older transactions thus have precedence over newer ones § Thus starvation is avoided. Timeout-Based Schemes: § Transaction waits for a lock only for a specified amount of time.
Deadlock Detection § Deadlocks can be described as a wait-for graph, § If Ti Tj is in E, then there is a directed edge from Ti to Tj, implying that Ti is waiting for Tj to release a data item. Wait-for graph without a cycle Wait-for graph with a cycle
Deadlock Recovery § When deadlock is detected : § Some transaction will have to rolled back. § Select that transaction as victim that will incur minimum cost. § Rollback -- determine how far to roll back transaction § Total rollback: Abort the transaction and then restart it. § More effective to roll back transaction only as far as necessary to break deadlock. § Starvation happens if same transaction is always chosen as victim. § Include #rollbacks in the cost factor to avoid starvation
Time Stamp Ordering Algorithm § Each transaction is issued a timestamp. § Time stamp of an old transaction Ti TS(Ti) < TS(Tj) of a newer transaction. § The protocol manages concurrent execution such that the timestamps determine the serializability order.
Time Stamp Ordering Algorithm § In order to assure such behavior, the protocol maintains for each data Q two timestamp values: § W-timestamp(Q) is the largest time-stamp of any transaction that executed write(Q) successfully. § R-timestamp(Q) is the largest time-stamp of any transaction that executed read(Q) successfully.
Time Stamp Ordering Algorithm § This protocol ensures that any conflicting read and write operations are executed in timestamp order. § Suppose a transaction Ti issues a read(Q) 1. If TS(Ti) < W-timestamp(Q). 2. If TS(Ti) W-timestamp(Q)
Time Stamp Ordering Algorithm § This protocol ensures that any conflicting read and write operations are executed in timestamp order. § Suppose a transaction Ti issues a read(Q) 1. If TS(Ti) < W-timestamp(Q). § Read operation is rejected, and Ti is rolled back. 2. If TS(Ti) W-timestamp(Q), § Read operation is executed § R-timestamp(Q) is set to max(R-timestamp(Q), TS(Ti)).
Time Stamp Ordering: Write Operation by Ti 1. If TS(Ti) < R-timestamp(Q) 2. If TS(Ti) < W-timestamp(Q) 3. Otherwise, the write operation is executed
Time Stamp Ordering: Write Operation by Ti 1. If TS(Ti) < R-timestamp(Q) § Write operation is rejected, and Ti is rolled back. 2. If TS(Ti) < W-timestamp(Q) § Write operation is rejected, and Ti is rolled back. 3. Otherwise, the write operation is executed § W-timestamp(Q) is set to TS(Ti).
Apply TSO Algorithm on following Schedule Transaction T 1 Transaction T 2 Read_item(Y) Write_item(Y) Read_item(X) T 1 arrives at time t=3 Write_item(X) Time Assume Read_item(X) Read_item(Y) Write_item(X) T 2 arrives at time t=1
TSO Algorithm Recoverability and Cascadeless § Problem with timestamp-ordering protocol: § Suppose Ti aborts, but Tj has read a data item written by Ti § Then Tj must abort; if Tj had been allowed to commit earlier, the schedule is not recoverable. § Further, any transaction that has read a data item written by Tj must abort § This can lead to cascading rollback --- that is, a chain of rollbacks
TSO Algorithm Recoverability and Cascadeless Solution 1: § A transaction is structured such that its writes are all performed at the end of its processing § All writes of a transaction form an atomic action; no transaction may execute while a transaction is being written § A transaction that aborts is restarted with a new timestamp Solution 2: § Limited form of locking: wait for data to be committed before reading it Solution 3: § Track uncommitted writes to atleast ensure recoverability
TSO Algorithm Thomas Write Rule § Modified version of the timestamp-ordering protocol. § Obsolete write operations may be ignored in some cases. § When Ti attempts to write data item Q, if TS(Ti) < W-timestamp(Q), then Ti is attempting to write an obsolete value of {Q}. § Rather than rolling back Ti (as TSO would do), this {write} operation can be ignored. § Otherwise this protocol is the same as the TSO algorithm.
TSO Algorithm Thomas Write Rule § Thomas' Write Rule allows greater potential concurrency. § Allows some view-serializable schedules that are not conflictserializable.
Quick note on View Serializability § View equivalence: § A less restrictive definition of equivalence of schedules § View serializability: § Definition of serializability based on view equivalence. § A schedule is view serializable if it is view equivalent to a serial schedule.
Quick note on View Serializability § Let S and S´ be two schedules. Following three conditions are met, for each data item Q, 1. Transaction Ti reads the initial value of Q in both schedule S and S’. 2. Transaction Ti should consume (read(Q)) the same output (write(Q)) of Tj in both S and S’. 3. The transaction (if any) that performs the final write(Q) operation must be same in both S and S’.
Quick note on View Serializability § A schedule S is view serializable if it is view equivalent to a serial schedule. § Every conflict serializable schedule is also view serializable. § But not vice versa. § What serial schedule is above equivalent to? § Every view serializable schedule that is not conflict serializable has blind writes.
Multiversion Schemes § Multiversion schemes keep old versions of data item to increase concurrency. § Multiversion Timestamp Ordering § Multiversion Two-Phase Locking § Each successful write results in the creation of a new version of the data item written. § Use timestamps to label versions.
Multiversion Schemes § Use timestamps to label versions. § When a read(Q) operation is issued, § Select an appropriate version of Q based on the timestamp of the transaction § Reads never have to wait as an appropriate version is returned immediately.
Multiversion Time Stamp Ordering § Each data item Q has a sequence of versions <Q 1, . . . , Qm>. § Each version Qk contains three data fields: § Content -- the value of version Qk. § W-timestamp(Qk) -- timestamp of the transaction that created (wrote) version Qk § R-timestamp(Qk) -- largest timestamp of a transaction that successfully read version Qk
Multiversion Time Stamp Ordering § When a transaction Ti creates a new version Qk of Q, § Qk's W-timestamp is initialized to TS(Ti) § Qk's R-timestamp is initialized to TS(Ti). § R-timestamp of Qk is updated whenever a transaction Tj reads Qk, and TS(Tj) > R-timestamp(Qk).
Multiversion Time Stamp Ordering § Suppose that transaction Ti issues a read(Q) or write(Q) operation. § Let Qk denote the version of Q whose write timestamp is the largest write timestamp less than or equal to TS(Ti). 1. If transaction Ti issues a read(Q), then the value returned is the content of version Qk. 2. If transaction Ti issues a write(Q) 1. if TS(Ti) < R-timestamp(Qk), then …… 2. if TS(Ti) = W-timestamp(Qk), then ……. 3. else a new version of Q is created.
Multiversion Time Stamp Ordering § Suppose that transaction Ti issues a read(Q) or write(Q) operation. § Let Qk denote the version of Q whose write timestamp is the largest write timestamp less than or equal to TS(Ti). 1. If transaction Ti issues a read(Q), then the value returned is the content of version Qk. 2. If transaction Ti issues a write(Q) 1. if TS(Ti) < R-timestamp(Qk), then transaction Ti is rolled back. 2. if TS(Ti) = W-timestamp(Qk), the contents of Qk are overwritten 3. else a new version of Q is created.
Multiversion Time Stamp Ordering § Observe that § Reads always succeed § A write by Ti is rejected if: Some other transaction Tj that (in the serialization order defined by the timestamp values) should read Ti's write, has already read a version created by a transaction older than Ti. § Protocol guarantees serializability
Multiversion Two Phase Locking Main Idea: § Allow a transaction T’ to read a data item X while it is write locked by a conflicting transaction T. § Accomplished by maintaining two versions of each data item X. § Here, one version must always have been written by some committed transaction. § This means a write operation always creates a new version of X.
Multiversion Two Phase Locking Steps: 1. 2. 3. 4. 5. 6. X is the committed version of a data item. T creates a second version X’ after obtaining a write lock on X. Other transactions continue to read X. T is ready to commit so it obtains a certify lock on X’. The committed version X becomes X’. T releases its certify lock on X’, which is X now. read/write locking scheme read/write/certify locking scheme
Multiversion Two Phase Locking § In multiversion 2 PL read and write operations from conflicting transactions can be processed concurrently. § This improves concurrency. § But it may delay transaction commit because of obtaining certify locks on all its writes. § It avoids cascading abort but like strict two phase locking scheme conflicting transactions may get deadlocked.
Validation Based Concurrency Control § In this technique only at the time of commit serializability is checked § Transactions are aborted in case of non-serializable schedules. § Each Transaction has the following three phases: 1. Read phase 2. Validation phase 3. Write phase
Validation Based Concurrency Control Read Phase: § A transaction can read values of committed data items. However, updates are applied only to local copies (versions) of the data items (in database cache). Validation Phase: § Serializability is checked before transactions write their updates to the database. Write Phase: § On a successful validation transactions’ updates are applied to the database; otherwise, transactions are restarted.
Validation Based Concurrency Control Key Idea: § Make validation atomic § If T 1, T 2, T 3, … is validation order, then resulting schedule will be conflict equivalent to Ss = T 1 T 2 T 3. . . § Aim: get a schedule which is conflict equivalent to a serial schedule where transactions were executed according to the validation point.
Validation Based Concurrency Control To implement validation, system keeps three sets: § START = transactions that have started, but not yet completed validation. START(T): time at which T started. § VAL = transactions that have successfully finished phase 2 (validation). VAL(T): time at which T is validated. § FIN = transactions that have finished phase 3 (and are all done). FIN(T): time at which T finished. § FIN set is periodically purged!
Validation Based Concurrency Control Example of what validation must stop: RS(T 2)={B} RS(T 3)={A, B} = WS(T 2)={B, D} WS(T 3)={C} T 2 start T 3 start T 2 T 3 is validated validating time T 2 would be writing in this time
Validation Based Concurrency Control Example of what validation must stop: RS(T 2)={B} RS(T 3)={A, B} = WS(T 2)={B, D} WS(T 3)={C} T 2 start T 3 start Should T 3 pass ? Does it follow the our definition of equivalent serial schedule? T 2 T 3 is validated validating time T 2 would be writing in this time
Validation Based Concurrency Control Example of what validation must pass: RS(T 2)={B} RS(T 3)={A, B} = WS(T 2)={B, D} WS(T 3)={C} T 2 start T 3 start T 2 T 3 validated T 2 finish phase 3 T 3 start time
Validation Based Concurrency Control Another thing validation must prevent: RS(T 2)={A} WS(T 2)={D, E} RS(T 3)={A, B} WS(T 3)={C, D} T 2 T 3 validated Validating? finish T 2 time
Validation Based Concurrency Control Another thing validation must prevent: RS(T 2)={A} WS(T 2)={D, E} RS(T 3)={A, B} WS(T 3)={C, D} T 2 T 3 validated Validating? finish BAD? ? w 3(D) w 2(D) T 2 time
Validation Based Concurrency Control Another thing validation must allow: RS(T 2)={A} WS(T 2)={D, E} RS(T 3)={A, B} WS(T 3)={C, D} T 2 T 3 validated finish T 2 time
Validation Based Concurrency Control Validation rules for Tj: (1) When Tj starts phase 1: ignore(Tj) FIN (2) at Tj Validation: if check (Tj) then [ VAL U {Tj}; do write phase; FIN U {Tj} ]
Validation Based Concurrency Control Check (Tj): For Ti VAL - IGNORE (Tj) DO IF [ WS(Ti) RS(Tj) OR Ti FIN ] THEN RETURN false; RETURN true;
Validation Based Concurrency Control Improving Check(Tj) For Ti VAL - IGNORE (Tj) DO IF [ WS(Ti) RS(Tj) OR (Ti FIN AND WS(Ti) WS(Tj) )] THEN RETURN false; RETURN true;
- Slides: 69