granularity of locks and degrees of consistency in a shared data base

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

David Wang

CS 632 Presentation by 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

Areas Files Indices

Records

A non-hierarchical lock graph

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.

On Optimistic Methods for Concurrency Control

CS 632 Presentation by David

Wang

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

ROOT

old

new

modifiedobjects

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

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

Ti

TjR V W

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

TjR V W

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

TjR V W

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)