Concurrency Control

1. Concurrency Control Chapter 17

2. Conflict Serializable Schedules • Two schedules are conflict equivalent if: • Involve the same actions of the same transactions • Every pair of conflicting actions is ordered the same way • Schedule S is conflict serializable if S is conflict equivalent to some serial schedule

3. Conflict serialiable vs. serialiable • Conflict serializable -> serializable • But not vice versa, e.g. T1: R(A) W(A)Commit T2: W(A)commit T3: W(A)Commit • Equivalent to T1 -> T2 -> T3, but not conflict serializable

4. Example • A schedule that is not conflict serializable: • The cycle in the graph reveals the problem. The output of T1 depends on T2, and vice-versa. T1: R(A), W(A), R(B), W(B) T2: R(A), W(A), R(B), W(B) A T1 T2 precedence graph B

5. Precedence Graph • Precedence graph: • One node per Xact; • An arc from Ti to Tj if an action of Ti precedes and conflicts with one of Tj’s actions • Capturing all conflicts • Theorem: Schedule is conflict serializable if and only if its precedence graph is acyclic

6. Review: Strict 2PL • Strict Two-phase Locking (Strict 2PL) Protocol: • Each Xact must obtain a S (shared) lock on object before reading, and an X (exclusive) lock on object before writing. • All locks held by a transaction are released when the transaction completes • If an Xact holds an X lock on an object, no other Xact can get a lock (S or X) on that object. • If an Xact holds a lock (S or X) on an object, no other Xact can get an X lock on that object. • Strict 2PL allows only schedules whose precedence graph is acyclic

7. Two-Phase Locking (2PL) • Two-Phase Locking Protocol • Each Xact must obtain a S (shared) lock on object before reading, and an X (exclusive) lock on object before writing. • A transaction can not request additional locks once it releases any locks. • If an Xact holds an X lock on an object, no other Xact can get a lock (S or X) on that object. • If an Xact holds a lock (S or X) on an object, no other Xact can get an X lock on that object.

8. More on 2PL • Relaxation of strict 2PL • A growing phase: acquiring locks • A shrinking phase: releases locks • 2PL -> conflict serializable • Why? • An equivalent serial order of transactions is given by the order in which transactions enter their shrinking phase

9. Strict 2PL vs. 2PL • A schedule is said to be strict if a value written by a transaction T is not read or overwritten by other transactions until T either aborts or commits. • Strict schedules are recoverable, ACA • Strict 2PL -> strict + 2PL ACA + conflict serialiable • Strict 2PL most popular, 2PL no practical importance • Strict 2PL is actually only one phase • New terminology: strict 2PL (S2PL) for strictness + 2PL strong strict 2PL (SS2PL) for our strict 2PL • An example schedule allowed by S2PL but not SS2PL?

10. View Serializability • Schedules S1 and S2 are view equivalent if: • If Ti reads initial value of A in S1, then Ti also reads initial value of A in S2 • If Ti reads value of A written by Tj in S1, then Ti also reads value of A written by Tj in S2 • If Ti writes final value of A in S1, then Ti also writes final value of A in S2 • S is view serializable if it is view equivalent to some serial schedule T1: R(A) W(A) T2: W(A) T3: R(A) W(A) T1: R(A)W(A) T2: W(A) T3: R(A)W(A)

11. View serializable vs. conflict serializable • Conflict serialiable -> view serialiable • So, more general condition • The reverse is not true T1: R(A) W(A) T2: W(A) T3: W(A) T1: R(A),W(A) T2: W(A) T3: W(A)

12. Classes of schedules: Venn diagram

13. Deadlocks • Deadlock: Cycle of transactions waiting for locks to be released by each other. • Two ways of dealing with deadlocks: • Deadlock prevention • Deadlock detection

14. Deadlock Prevention • Assign priorities based on timestamps. • The oldest transaction has the highest priority • Assume Ti wants a lock that Tj holds. Two policies are possible: • Wait-Die: It Ti has higher priority, Ti waits for Tj; otherwise Ti aborts • Wound-wait: If Ti has higher priority, Tj aborts; otherwise Ti waits

15. Deadlock Prevention (cont’d) • In wait-die, lower priority transactions never wait for higher priority ones • In wound-wait, higher priority transactions never wait for lower priority ones • Either case, no deadlock cycle • In both schemes, higher priority transaction is never aborted • If a transaction re-starts, make sure it has its original timestamp

16. Deadlock Detection • Create a waits-for graph: • Nodes are transactions • There is an edge from Ti to Tj if Ti is waiting for Tj to release a lock • Periodically check for cycles in the waits-for graph

17. Deadlock Detection (Continued) Example: T1: S(A), R(A), S(B) T2: X(B),W(B) X(C) T3: S(C), R(C) X(A) T4: X(B) T1 T2 T1 T2 T4 T3 T4 T3

18. Database Tables Pages Tuples Multiple-Granularity Locking • Hard to decide what granularity to lock (tuples vs. pages vs. tables). • Locking overhead • Data “containers” are nested: contains

19. IS IX S X -- Ö Ö Ö Ö Ö -- IS Ö Ö Ö Ö IX Ö Ö Ö S Ö Ö Ö Ö X Solution: New Lock Modes, Protocol • Allow Xacts to lock at each level, but with a special protocol using new “intention” locks: • Before locking an item, Xact must set “intention locks” on all its ancestors. • For unlock, go from specific to general (i.e., bottom-up). • SIX mode: S & IX at the same time.

20. Multiple Granularity Lock Protocol • Each Xact starts from the root of the hierarchy. • To get S or X lock on a node, must hold IS or IX on parent node. • To get IX or SIX on a node, must hold IX or SIX on parent node. • Must release locks in bottom-up order.

21. IS IX S X -- Ö Ö Ö Ö Ö -- IS Ö Ö Ö Ö IX Ö Ö Ö Ö S Ö Ö Ö X Examples • T1 scans R, and updates a few tuples: • T1 gets an SIX lock on R, then repeatedly gets an S lock on tuples of R, and occasionally upgrades to X on the tuples. • T2 uses an index to read only part of R: • T2 gets an IS lock on R, and repeatedly gets an S lock on tuples of R. • T3 reads all of R: • T3 gets an S lock on R. • Or, T3 could behave like T2; can use lock escalation to decide which. • Lock escalation dynamically asks for coarser-grained locks when too many low level locks acquired

22. Optimistic CC • Locking is a conservative/pessimistic approach in which conflicts are prevented. • Disadvantages: • Lock management overhead. • Deadlock detection/resolution. • Lock contention for heavily used objects. • If conflicts are rare, we might be able to gain concurrency by not locking, and instead checking for conflicts before Xacts commit.

23. Kung-Robinson Model • Xacts have three phases: • READ: Xacts read from the database, but make changes to private copies of objects. • VALIDATE: Check for conflicts. If there’s a possible conflict, abort and restart • WRITE: Make local copies of changes public. • If lots of conflicts, cost of repeatedly restarting transactions hurts performance

24. Validation • Test conditions that are sufficient to ensure that no conflict occurred. • Each Xact is assigned a numeric id. • Just use a timestamp. • To validate Tj, need to check all committed Ti with Ti < Tj • One of the following 3 validation conditions must hold

25. Validating Tj: Test 1 • Ti completes before Tj begins. Ti Tj R V W R V W

26. Validating Tj: Test 2 • Ti completes before Tj begins its Write phase • Ti does not write any db object read by Tj Ti R V W Tj R V W

27. Validating Tj: Test 3 • Ti completes Read phase before Tj does • Ti does not write any db object read by Tj • Ti does not write any db object written by Tj Ti R V W Tj R V W

28. Timestamp CC • Idea: Determine an equivalent serial order of Xacts in advance, e.g., by submission time, called the timestamp order • At execution time, ensure that every pair of conflicting actions follows the timestamp ordering. • If this is violated by the next action from Xact T, T is aborted and restarted with a new, larger timestamp. • If restarted with same TS, T will fail again! Contrast use of timestamps in 2PL for ddlk prevention

29. Timestamp CC • TS(T): the timestamp assigned to Xact T when starts (enters the system) • RTS(O): the read timestamp for object O, set to the largest time-stamp of any transaction that has executed read(O) successfully. • WTS(O): the write timestamp for object O, set to the largest time-stamp of any transaction that has executed write(O) successfully.

30. When Xact T wants to read Object O • If TS(T) < WTS(O) • read(O) of T comes too late, someone with larger TS already wrote O • this is a WR conflict violating the predefined timestamp order • So, abort T and restart it with a new, larger TS • If TS(T) > WTS(O): • Allow T to read O. • Reset RTS(O) to max(RTS(O), TS(T))

31. When Xact T wants to Write Object O • If TS(T) < RTS(O) • write(O) of T comes too late, someone with larger TS already read O • this is a RW conflict violating the predefined timestamp order • abort and restart T. • If TS(T) < WTS(O) • write(O) of T comes too late, someone with larger TS already write O • this is a WW conflict violating the predefined timestamp order • Naïve approach: abort T • However, write(O) of T should be overwritten anyway, so … • Thomas Write Rule: We can safely ignore such outdated writes; need not restart T! • Else, allow T to write O and set WTS(O) to TS(T) • why not max(WTS(O), TS(T))?

32. Thomas write rule (watch out text) • Naïve: allows only conflict serializable schedules • Thomas write rule: some schedules are allowed, which are seriliable but not conflict serializable • The Thomas Write Rule relies on the fact that T1's write on object A is never seen by any transaction • T1’s write is ignored T1 T2 R(A) W(A) Commit W(A) Commit T1 T2 R(A) W(A) Commit W(A) Commit

33. Timestamp CC and Recoverability • Unfortunately, unrecoverable schedules are allowed • Timestamp CC can be modified to allow only recoverable schedules T1 T2 W(A) R(A) W(B) Commit

34. Summary • There are several lock-based concurrency control schemes (Strict 2PL, 2PL). Conflicts between transactions can be detected in the dependency graph • The lock manager keeps track of the locks issued. Deadlocks can either be prevented or detected.

35. Summary (Contd.) • Multiple granularity locking reduces the overhead involved in setting locks for nested collections of objects (e.g., a file of pages) • Optimistic CC aims to minimize CC overheads in an ``optimistic’’ environment where reads are common and writes are rare. • Optimistic CC has its own overheads however; most real systems use locking.

36. Summary (Contd.) • Timestamp CC is another alternative to 2PL; allows some serializable schedules that 2PL does not • converse is also true • Ensuring recoverability with Timestamp CC requires ability to block Xacts, which is similar to locking.