Granularity of Locks and Degrees of Consistency in a Shared Data Base

1. Granularity of Locks and Degrees of Consistency in a Shared Data Base David Wang

2. Granularity of Locks (Review of 432) • Locks present two tradeoff issues: • Concurrency • What we want • Overhead • What we don’t want CS 632 Presentation by David Wang

3. Hierarchical Locks • Database -> Areas -> Files -> Records • Exclusive (X) access to particular node • Requester has exclusive access to that node and its descendants (for writes) • Shared (S) access to particular node • Requester has shared access to that node and its descendants (for reads) • Intention lock tags all ancestors of a node to be locked with X or S CS 632 Presentation by David Wang

4. Access modes continued • IS • Gives intention share access to the requested node and allows the requestor to lock descendant nodes in S or IS mode. • IX • Gives intention exclusive access to the requested node and allows the requestor to explicitly lock descendants in X, S, SIX, IX, or IS mode. • SIX • good for reading an entire subtree but update only a small portion CS 632 Presentation by David Wang

5. Access mode Compatibilities CS 632 Presentation by David Wang

6. To lock record R for read • Lock data-base with mode = IS • Lock area containing R with mode = IS • Lock file containing R with mode = IS • Lock record R with mode = S CS 632 Presentation by David Wang

7. To lock a file for read & write • Lock data-base with mode = IX • Lock area containing F with mode = IX • Lock file F with mode = X CS 632 Presentation by David Wang

8. To lock a file F for complete scan and occasional update • Lock data-base with mode = IX • Lock area containing F with mode = IX • Lock file F with mode = SIX CS 632 Presentation by David Wang

9. Question • Locks are requested root to leaf, and released leaf to root, why? CS 632 Presentation by David Wang

10. Directed Acyclic Graph Database Areas Files Indices Records A non-hierarchical lock graph CS 632 Presentation by David Wang

11. To request S or IS lock • At least a parent should be in IS mode • Therefore, there is a path to the root in IS mode. • Therefore, none of the ancestors in this path can be granted to another transaction in IS-incompatible-mode CS 632 Presentation by David Wang

12. To request IX, SIX, or X mode • Request all parents of the node in IX mode or greater mode. • Therefore, all ancestors will be held in IX or greater mode. • Therefore, no ancestor can be held by others in IX-incompatible mode CS 632 Presentation by David Wang

13. Dynamic Lock Graphs • Problem: • When an indexed field is updated, it and its parent record move from one index interval to another. • Solution: • Before moving a node in the lock graph, the node must be implicitly or explicitly granted in X mode in both its old and its new position in the graph. Further, the node must not be moved in such a way as to create a cycle in the graph. CS 632 Presentation by David Wang

14. II. Degrees of consistency • Problem: • database sometimes becomes temporarily inconsistent in order to transform it to a new consistent state. • Solution: • TRANSACTIONS CS 632 Presentation by David Wang

15. Degree 0 • Implementation • T does not overwrite dirty data of other transactions. • Result • T sets an (possibly short) exclusive lock on any data it dirties. CS 632 Presentation by David Wang

16. Degree 1: • Implementation • T does not overwrite dirty data of other transactions. • T does not commit any writes before EOT • Result • T sets a long exclusive lock on any data it dirties. CS 632 Presentation by David Wang

17. Degree 2: • Implementation • T does not overwrite dirty data of other transactions. • T does not commit any writes before EOT • T does not read dirty data of other transactions. • Result  • T sets a long exclusive lock on any data it dirties. • T sets a (possibly short) share lock on data it reads CS 632 Presentation by David Wang

18. Degree 3: • Implementation • T does not overwrite dirty data of other transactions. • T does not commit any writes before EOT • T does not read dirty data of other transactions. • Other transactions do not dirty any data read by T before T completes. • Result • T sets a long exclusive lock on any data it dirties. • T sets a long share lock on any data it reads. CS 632 Presentation by David Wang

19. Increasing consistency • Degree 0: • prevents updating uncommitted updates of others. • Degree 1: • guarantees that all transactions are recoverable. • Degree 2: • isolates a transaction from the uncommitted data of other transactions. • Degree 3: • performs as if the transaction is alone. CS 632 Presentation by David Wang

20. On Optimistic Methods for Concurrency Control

21. Two concurrency control mechanisms • Locking • Transactions wait • Solves the starvation problem • Backup (optimistic approach) • Ideal for query-dominant systems • Solves the deadlock problem • Less overhead than locking CS 632 Presentation by David Wang

22. The Optimistic Approach • Reads • Unrestricted • Writes • Read phase • Validation phase • Starvation problem • Write phase • Swap two pointers CS 632 Presentation by David Wang

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. • WRITE: Make local copies of changes public. CS 632 Presentation by David Wang

24. Kung-Robinson Model old modified objects ROOT new CS 632 Presentation by David Wang

25. Validation • Test conditions that are sufficient to ensure that no conflict occurred. • Each Xact is assigned a numeric id. • Just use a timestamp. • Validation of Xact “n” does not need knowledge of Xact “n+1” • Xact ids assigned at end of READ phase, just before validation begins. (Why then?) • ReadSet(Ti): Set of objects read by Xact Ti. • WriteSet(Ti): Set of objects modified by Ti. CS 632 Presentation by David Wang

26. Test 1 • For all i and j such that Ti < Tj, check that Ti completes before Tj begins. Ti Tj R V W R V W CS 632 Presentation by David Wang

27. Test 2 • For all i and j such that Ti < Tj, check that: • Ti completes before Tj begins its Write phase • WriteSet(Ti)  ReadSet(Tj) is empty. Ti R V W Tj R V W Does Tj read dirty data? Does Ti overwrite Tj’s writes? CS 632 Presentation by David Wang

28. Test 3 • For all i and j such that Ti < Tj, check that: • Ti completes Read phase before Tj does + • WriteSet(Ti)  ReadSet(Tj) is empty + • WriteSet(Ti)  WriteSet(Tj) is empty. Ti R V W Tj R V W Does Tj read dirty data? Does Ti overwrite Tj’s writes? CS 632 Presentation by David Wang

29. Applying Tests 1 & 2: Serial Validation • To validate Xact T: valid = true; // S = set of Xacts that committed after Begin(T) <foreach Ts in S do { if ReadSet(Ts) intersects WriteSet(T) then valid = false; } if valid then { install updates; // Write phase Commit T } > else Restart T end of critical section CS 632 Presentation by David Wang

30. Comments on Serial Validation • Applies Test 2, with T playing the role of Tj and each Xact in Ts (in turn) being Ti. • Assignment of Xact id, validation, and the Write phase are inside a critical section! • I.e., Nothing else goes on concurrently. • If Write phase is long, major drawback. • Optimization for Read-only Xacts: • Don’t need critical section (because there is no Write phase). CS 632 Presentation by David Wang

31. Serial Validation (Contd.) • Starvation: Run starving Xact in a critical section (!!) • Space for WriteSets: To validate Tj, must have WriteSets for all Ti where Ti < Tj and Ti was active when Tj began. If Tj has a long read phase. There may be many such Xacts, and we may run out of space. • Tj’s validation fails if it requires a missing WriteSet. • No problem if Xact ids assigned at start of Read phase? CS 632 Presentation by David Wang

32. Overheads in Optimistic CC • Must record read/write activity in ReadSet and WriteSet per Xact. • Must create and destroy these sets as needed. • Must check for conflicts during validation, and must make validated writes ``global’’. • Critical section can reduce concurrency. • Scheme for making writes global can reduce clustering of objects. • Optimistic CC restarts Xacts that fail validation. • Work done so far is wasted; requires clean-up. CS 632 Presentation by David Wang

33. ``Optimistic’’ 2PL • If desired, we can do the following: • Set S locks as usual. • Make changes to private copies of objects. • Obtain all X locks at end of Xact, make writes global, then release all locks. • In contrast to Optimistic CC as in Kung-Robinson, this scheme results in Xacts being blocked, waiting for locks. • However, no validation phase, no restarts (modulo deadlocks). CS 632 Presentation by David Wang

34. Summary • Locks • Granularity of Locks • Access Modes • Applications: 4 degrees of consistency • Optimistic Approach (backup, no locks) CS 632 Presentation by David Wang