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

David Wang

I.

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

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

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

Access mode Compatibilities

NL IS IX S SIX X NL YES YES YES YES YES YES IS YES YES YES YES YES NO IX YES YES YES NO NO NO S YES YES NO YES NO NO SIX YES YES NO NO NO NO X YES NO NO NO NO NO CS 632 Presentation by David Wang

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

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

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

Question

 Locks are requested root to leaf, and released leaf to root, why?

CS 632 Presentation by David Wang

Directed Acyclic Graph

Database Files A non-hierarchical lock graph Areas Records Indices CS 632 Presentation by David Wang

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

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

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

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

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

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

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

 

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

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

On Optimistic Methods for Concurrency Control

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

The Optimistic Approach

  Reads  Unrestricted Writes   Read phase Validation phase  Starvation problem  Write phase  Swap two pointers CS 632 Presentation by David Wang

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

Kung-Robinson Model

modified objects old new ROOT

CS 632 Presentation by David Wang

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

Test 1

R

 For all i and j such that Ti < Tj, check that Ti completes before Tj begins.

Ti V W Tj R V W

CS 632 Presentation by David Wang

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

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

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

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

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

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

``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

Summary

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