Granularity of Locks and Degrees of Consistency in a

Shared Data BaseJim Gray, et al. 1976.

DB Reading Group 2nd MeetingHideaki Kimura, July 8, 2009

Intent Locks (IS/IX/SIX)

S

X

S X

Y N

N N

IS

S

IX

SIX

X

IS S IX SIX X

Y Y Y Y N

Y Y N N N

Y N Y N N

Y N N N N

N N N N N

Table

PageTuple

ISIS S

Hie

rarc

hy

Intent Lock Compatibility (IS)

S

S

IS

(X)

(X)

(X)

X XIX

(So far) Okay.(Can collide indescendents)

AbsolutelyCollide

S

S

IS

AbsolutelyOkay

Intent Lock Compatibility (IX)

X

X

IX

X XIX

S

S

IS(So far) Okay.(Can collide indescendents)

(X)

(X)

(X)

AbsolutelyCollide

Intent Lock Compatibility (SIX)

(S)

(S)

X

(S)

SIX

X XIX

S

S

IS

(So far) Okay.(Can collide indescendents)

(X)

(X)

(X)

AbsolutelyCollide

Degree of Consistency (Isolation Level)

CommittedRead

RepeatableRead

Serializable

UncommittedRead

SQL Standard

Auto Commit

Degree 2

Degree 3(?)

Degree 1

In this paper

Degree 0 No Transaction

2PL for Writes

2PL for Writes+ Tentative Read Lock

2PL for Reads/Writes

2PL for Reads/Writesand Special Locks

Typical Implementation

Isolation Level and Anomalies

CommittedRead

RepeatableRead

Serializable

UncommittedRead

LevelDirtyRead

O

O

O

X

Lost update/IR

X

O

O

X

Phantom

X

X

O

X

Lost Update

ReadX=A

Transaction1 ReadX=A

Transaction2

WriteA=X+1

WriteA=X+3

A=10

A=10

A=13

A=11 Serialized ResultA=14

Dirty Read

ReadX=A

Transaction1 WriteA=20

Transaction2

WriteA=X+5

Rollback

A=10

A=20

A=10

A=25 Serialized ResultA=15

Inconsistent Read (IR)

WriteA2=4

Transaction1 Sum=A1

Transaction2

WriteA1=5

Sum+=A2

A1=1,A2=3Sum=0

A1=1,A2=4Sum=1

A1=1,A2=4Sum=5

A1=5,A2=4Sum=5 Serialized Result

Sum= 4 or 9

Phantom

InsertA2=5

COMMIT

Transaction1

SumA1-A3

Transaction2

AveA1-A3

A1=1,A3=3Sum=0,Ave=0

A1=1,A3=3Sum=4,Ave=0

A1=1,A2=5,A3=3Sum=4,Ave=0

A1=1,A2=5,A3=3Sum=4,Ave=3

Serialized ResultSum=4,Ave=2

OrSum=9,Ave=3

Locks in Uncommitted Read

X

X

IX UPDATE

Transaction1

INSERT

Transaction2

SELECT

Transaction3

No Read Lock!

Dirty Read!

(X-X Collides)

Locks in Committed Read

XIX UPDATE

Transaction1

INSERT

SELECT

Transaction3

Tentative Read Locks

No Dirty ReadsInconsistent Reads

Transaction2

S

IS S

SELECT

CommitXIX

Lock in Repeatable Read

S

S

IS UPDATE

Transaction1

INSERT

Transaction2

SELECT

Transaction3

2PL Read Locks

Still See Phantoms

No InconsistentReads

SELECT

Lock in Serializable

Same Locks as Repeatable Read .. and Special Locks to Prevent

Phantoms Predicate Lock (Semantic Lock) Range Lock

SQL Server: Key Range Lock

A_B: A for Range, B for Tuple

S

X

S

Mode DescriptionTupleRange

S_UShare Range-Update Resource Lock

Serializable Level Update ScanU

X_XX Range-X Resource Lock

Used to Update Some Keys in RangeX

S_SShare Range-Share Resource Lock

Serializable Level Range ScanS

I I_NInsert Range-NULL Resource Lock

Used to Insert A New TupleNull

SQL Server : Serializable

1

5

7

9

IS

S_S

INSERT2

SELECT7-9

INSERT8

I_NOK!

2(X)

I_Nwait

SELECT 1

SELECT 5

S

S

S_S

SELECT20

RangeScan

:SerializableTransaction

No-ResultFetch

DB2: Lock Type

IS

NS

S

IX

SIX

U

IN

NW

X

IS NS S IX SIXUIN NWX

W

Z

W Z

Y Y Y Y YYY NN

Y Y Y N NYY YN

Y Y Y N NYY NN

Y Y Y Y YYY YY

Y N N Y NNY NN

Y N N N NNY NN

Y Y Y N NNY NN

N N N N NNY NN

N Y N N NNY NN

N N N N NNY YN

N N N N NNN NN

N

N

N

Y

N

N

N

N

Y

N

N

N

N

N

N

N

N

N

N

N

N

N

DB2(Next Key Lock: NS, W, NW) Reads in Serializable: S-Lock on the

tuple and next tuple Reads in Lower Levels: NS-Lock Tentative NW-Lock on INSERT

NS

S

IS NS S IX SIXUIN X W ZNW

NS is a Read Lock Compatible to NW-Lock=Collide with INSERT only in Serializable Level

Y Y Y N NYY N

Y Y Y N NYY N

N

N

N

N

Y

N

DB2(Next Key Lock: NS, W, NW) W-Lock on new tuples

W-Lock is compatible to NW-Lock to allow inserting to next

W-Lock is not compatible to NS

NW

W

IS S IX SIXUIN X Z

W NW

W W

W NWW

W NW

W ・・・

NW WNS

S/NS

N N N NNY N

N N N NNY N

N

N

N

Y

Y

N

Y

N

DB2(Next Key Lock for Deletion) Leave X-Lock on Next Key for

uncommitted deleted tuple Happens in all Levels

1347

DELETE 3 (Uncommitted)

9

13479

1

479

X

X-> ->

INSERT 2

2 W

NWNext Key Lock for DeletionTo Inform transactions in

Serializable Level of uncommitteddeleted keys

X

Lower Isolation LevelLower Isolation Level

Pseudo-delete NW-Lock on the tuple next to deleted tuple Uncommitted deleted tuple retains X-Lock Ignore committed deleted tuple which is

asynchronously cleaned up

DB2(Type2 Index:V8.1-)

13479

1

479

1

479

-> ->

INSERT 2

2 X

NW3 3X

Lower Isolation Level Lower Isolation Level

X

After Commit

1

479

Clean upon REORG

DELETE 3 (Pre-commit)

DB2(Deferred Locking:V8.1.4-) Table-Scan causes Table-Lock E

ven Though Only a few tuples are SELECTed

13479

2010405030

ID Val (No Index)

X

DELETE 4UPDATE 4

SELECTWHERE VAL<=30

Lock and then Readto apply predicates

NS

NS

NS

Wait!

Read and applypredicates

and then LockNS