Granularity of Locks and Degrees of Consistency in a Shared Data Base John LaFontaine Haixuan Sun

1. Granularity of Locks and Degrees of Consistency in a Shared Data Base John LaFontaine Haixuan Sun

2. Agenda • Background • Overview of locking modes • Rules for acquiring locks • Dynamic lock graphs

3. Agenda • Background • Overview of locking modes • Rules for acquiring locks • Dynamic lock graphs

4. Background • Problem: What is the appropriate granularity of lockable objects in a data base? • Small lockable objects = Increased overhead, Increased concurrency • Larger = lower overhead, lower concurrency

5. Granularity of Locks • Intuitively, locking only the exact record being examined allows for maximum concurrency • However, if a lot of “lockable objects” need to be examined, there is a lot of overhead • Takes time to set/reset locks each time you need to look at a record • There is a non-zero storage overhead for representing a lock in memory • Solution: Allow for multiple granularities of locking in the same system

6. Agenda • Background • Overview of locking modes • Rules for acquiring locks • Dynamic lock graphs

7. Hierarchical Locks • Data Base → Area → File → Table → Record • Each node has a unique parent and nodes at all levels can be locked • Two types of lock modes • Exclusive (X) • Shared (S) • Explicitly locking a node in one of these two modes implicitly locks all descendants in the same mode

8. Table 1 Table 2

9. Table 1 T1 - S Table 2

10. Table 1 T1 - S T2 - X Table 2

11. Intention Modes • Intention mode (I) used to “tag” all ancestors of a locked node • Intention share mode (IS) • Intention exclusive mode (IX) • Nodes locked in IS mode can be later locked in S mode, but nodes locked in IX mode cannot • Distinguishing between IS and IX is critical in enabling concurrency

13. Result: Poor Concurrency

15. Share and Intention Exclusive Mode • Abbreviated SIX mode • Has properties of both a shared lock and an intention exclusive lock • Common case in databases is to scan a sub tree and modify a small percentage • Avoids high overhead of individually locking each record examined • Also avoids low concurrency of claiming an exclusive lock on the entire sub tree being scanned

16. Mode Summary • NL – no locks held • IS – allows requestor to lock decendants in S or IS mode, does no actual locking • IX – allows requestor to lock decenants in X, S, IX, IS, SIX mode, does no actual locking • S – grants shared access to the node and all decendants of the node without requesting any further locks

17. Mode Summary (cont.) • SIX – gives explicit shared access to the requested node and all decendants, also allows the requestor to further lock a decendant node in X, SIX, or IX mode • X – gives explicit exclusive access to the requested node and all decendant nodes

18. Compatibility Summary

19. Locking Mode Ordering • The features of the locking modes imply an ordering • The order of IX and S is not defined as they cannot be compared • X > SIX > S ~ IX > IS > NL • “Higher” locking modes have all the features of the lower modes

20. Agenda • Background • Overview of locking modes • Rules for acquiring locks • Dynamic lock graphs

21. Requesting Locks in a Tree • In general, locks must be acquired root to leaf and released leaf to root • Before requesting IS or S lock on a node, all ancestors nodes must be held in IS or IX mode • Before requesting X, SIX, or IX lock on a node, all ancestor nodes must be held in IX or SIX mode • Locks should be released in leaf to root order (or in any order when the transaction is over)

22. Directed Acyclic Graphs • The tree locking hierarchy can be generalized to all directed acyclic graphs (DAG) • To lock a node in DAG, all parents (may be multiple) must be locked in the appropriate mode • A node is implicitly locked in S mode if ANY of the parents are explicitly or implicitly locked in S, SIX, or X mode • A node is implicitly locked in X mode if ALL of its parents are locked in X mode

23. Requesting Locks in a DAG • Before requesting an S or IS lock on a node, one should request at least one parent in IS mode • Before requesting IX, SIX, or X mode access to a node, one should request all parents in IX (or greater) mode • When releasing locks, one should never hold a lower lock having released its ancestors (or it should release all locks when the transaction is complete)

24. Agenda • Background • Overview of locking modes • Rules for acquiring locks • Dynamic lock graphs

25. Dynamic Lock Graph • So far we have assumed a static database • This is not useful, because if the database were static, locks would be unnecessary • It is often convenient to lock one particular value of an indexed attribute • Index Interval locks can be used to do this • Assumes that the indexed fields are stored separately from the unindexed fields • Can read the indexed values directly (without touching the actual record)

28. Index Value Intervals • Normal Locking protocol for DAG is extended • When a indexed field is changed, it must “leave” the index value interval it was in and “join” a new one • Before moving a node, the node must be locked in X mode in both its old and new position on the lock graph • Example: To move an account from the NY branch to the BOS branch, both the NY and BOS index value intervals would need to be locked

29. Outline • Informal definition of consistency degrees with respect to dirty data in transaction • Lock protocol definition of consistency degrees • Definition of schedule consistency degrees • Assertion of consistency wrt to dependency • Transaction backup and system recovery

30. Outline • Informal definition of consistency degrees with respect to dirty data in transaction • Lock protocol definition of consistency degrees • Definition of schedule consistency degrees • Assertion of consistency wrt to dependency • Transaction backup and system recovery

31. Consistency and Transactions • The data base is said to be consistent if it satisfies all its assertions. • Transactions preserve consistency. • Transactions are units of' consistency & recovery. • An output of a transaction is committed when the transaction abdicates the right to undo the write. • Outputs are said to be uncommitted or dirty if they are not yet committed by the writer. • Concurrent execution raises the problem that reading or writing other transactions’ dirty data may yield inconsistent data.

32. Definition of Consistency wrt Dirty Data Degree 3 consistency: Transaction T sees degree 3 consistency if: • T does not overwrite dirty data of other transactions. • T does not commit any writes until it completes all its writes ( ie. until the end of transaction (EOT)). • T does not read dirty data from other transactions. • Other transactions do not dirty any data read by T before T completes. Degree 2 consistency: Transaction T sees degree 2 consistency if: • T does not overwrite dirty data of other transactions. • T does not commit any writes until the end of transaction. • T does not read dirty data from other transactions.

33. Definition of Consistency wrt Dirty Data Degree 1 consistency: Transaction T sees degree 1 consistency if: • T does not overwrite dirty data of other transactions. • T does not commit any writes until the end of transaction. Degree 0 consistency: Transaction T sees degree 0 consistency if: • T does not overwrite dirty data of other transactions. • Note that if a transaction sees a high degree of consistency then it also sees all the lower degrees.

34. Recoverability concerning Degrees of Consistency • Recoverable transactions can be undone without affecting other transaction, unrecoverable transactions cannot. • Degree 0 consistent transactions are unrecoverable, they commit outputs before the end of transaction. • Degree 1, 2 &3 consistent is recoverable, they do not commit any writes until the end of transaction.

35. Isolation concerning Degrees of Consistency • Degree 2 consistent transaction isolates itself from the uncommitted data from other transactions (which can be updated or undone later). • Degree 3 consistent transaction isolates itself from dirty dirty relationship among entities, other transactions do not dirty any data read by it. • Degree 3 completely guarantees consistency with regards to concurrency.

36. Outline • Informal definition of consistency degrees with respect to dirty data in transaction • Lock protocol definition of consistency degrees • Definition of schedule consistency degrees • Assertion of consistency wrt to dependency • Transaction backup and system recovery

37. Types of Lock • Share mode locks allow multiple readers of the same entity. • Exclusive mode locksreserve exclusive access to an entity. • Short duration locks are held for the duration of a single action. • Long duration locks are held to the end of the transaction.

38. Lock Protocol Definition of Consistency Degree 3 consistency: Transaction T sees degree 3 consistencyif: • T sets a long exclusive lock on any data it dirties. • T sets a long share lock on any data itreads. Degree 2 consistency: Transaction T sees degree 2 consistency if: • T sets a long exclusive lock on any data it dirties. • T sets a (possibly short) share lock on any data it reads.

39. Lock Protocol Definition of Consistency Degree 1 consistency: Transaction T sees degree 1 consistency if: • T sets a long exclusive lock on any data it dirties. Degree 0 consistency: Transaction T sees degree 0 consistency if: • T sets a (possibly short) exclusive lock on any data it dirties.

40. Well Formed and Two Phase Transaction • A transaction is well formedwith respect to writes (reads) if it always locks an entity in exclusive (shared or exclusive) mode before writing (reading) it. • The transaction is well formed if it is well formed with respect to reads and writes. • A transaction is two phase (with respect to reads or updates) if it does not (share or exclusive) lock an entity after unlocking some entity.

41. Definition wrt Well Formed and Two Phase Degree 3 consistency: Transaction T sees degree 3 consistency if: • T is well formed. • T is two phase. Degree 2 consistency: Transaction T sees degree 2 consistency if: • T is well formed. • T is two phase with respect to writes.

42. Definition wrt Well formed and Two Phase Degree 1 consistency: Transaction T sees degree 1 consistency if: • T is well formed with respect to writes. • T is two phase with respect to writes. Degree 0 consistency: Transaction T sees degree 0 consistency if: • T is well formed with respect to writes.

43. Outline • Informal definition of consistency degrees with respect to dirty data in transaction • Lock protocol definition of consistency degrees • Definition of schedule consistency degrees • Assertion of consistency wrt to dependency • Transaction backup and system recovery

44. Actions and Transaction • Types of Actions: • begin, end • share lock, exclusive lock, unlock • read, write • An end action is presumed to unlock any lock. • A transaction is any sequence of actions beginning with a begin action and ending with an end action and not containing other begin or end actions.

45. Definition of Schedule • Any sequence preserving merging of the actions of a set of transactions into a single sequence is called a schedulefor the set of transactions. • A schedule is legal only if it does not schedule a lock action on an entity for one transaction when that entity is already locked by some other transaction in a conflicting mode.

46. Consistency Degrees of Schedules • A transaction runs at degree 0 (1,2, or 3) consistency in schedule S if if T sees degree 0 (1, 2 or 3) consistency in S. • If all transactions run at degree 0 (1, 2 or 3) consistency in schedule S then S is said to be a degree 0 (1,2 or 3) consistent schedule.

47. Assertions Assertion 1: • If each transaction observes the degree 0 (1, 2 or 3) lock protocol then any legal schedule is degree 0 (1, 2 or 3) consistent (ie , each transaction sees degree 0 (1, 2 or 3) consistency). • Unless transaction T observes the degree 1 (2 or 3) lock protocol then it is possible to define another transaction T’ which does observe the degree 1 (2 o r 3) lock protocol such that T and T’ have a legal schedule S but T does not run at degree 1 (2 or 3) consistency in S.

48. Assertions Assertion 2: If each transaction in a set of transactions at least observes the degree 3 lock protocol and if transaction T observes the degree 1 (2 or 3) lock protocol then T runs at degree 1 (2 or 3) consistency in any legal schedule for the set of transactions.

49. Outline • Informal definition of consistency degrees with respect to dirty data in transaction • Lock protocol definition of consistency degrees • Definition of schedule consistency degrees • Assertion of consistency wrt to dependency • Transaction backup and system recovery

50. Dependencies among Transactions Dependency relations: (Suppose transaction T performs action a on entity e, transaction T’ performs action a’ on e later, T’!= T) • T<<<T’ • if a is a write action and a' is a write action • or a is a write action and a' is a read action • or a is a read action and a' is a write action • T<<T’ • if a is a write action and a' is a write action • or a is a write action and a' is a read action • T<T’ • if a is a write action and a' is a write action