Transcript Granularity of Locks and Degrees of Consistency in a

Granularity of Locks and
Degrees of Consistency in a
Shared Data Base
Jim Gray, et al. 1976.
DB Reading Group 2nd Meeting
Hideaki Kimura, July 8, 2009
Hierarchy
Intent Locks (IS/IX/SIX)
S
X
Table
IS Page
IS
S
Y
N
X
N
N
S
Tuple
IS
S
IX
SIX
X
IS
Y
Y
Y
Y
N
S
Y
Y
N
N
N
IX
Y
N
Y
N
N
SIX
Y
N
N
N
N
X
N
N
N
N
N
Intent Lock Compatibility (IS)
Absolutely
Okay
S
IS
IS
S
S
Absolutely
Collide (X)
X
(X)
(X)
S
(So far) Okay.
(Can collide in
descendents)
IX
X
Intent Lock Compatibility (IX)
X
IX
X
S
IS
(So far) Okay.
(Can collide in
descendents)
S
Absolutely
Collide (X)
X
(X)
(X)
IX
X
Intent Lock Compatibility (SIX)
(S)
(So far) Okay.
(Can collide in
descendents)
(S)
S
SIX
IS
X
(S)
(X)
X
(X)
(X)
S
Absolutely
Collide
IX
X
Degree of Consistency (Isolation Level)
In this paper
SQL Standard Typical Implementation
Degree 0
Auto Commit
No Transaction
Degree 1
Uncommitted
Read
2PL for Writes
Degree 2
Committed
Read
2PL for Writes
+ Tentative Read Lock
Repeatable
Read
2PL for Reads/Writes
Serializable
2PL for Reads/Writes
and Special Locks
Degree 3
(?)
Isolation Level and Anomalies
Level
Uncommitted
Read
Committed
Read
Repeatable
Read
Serializable
Dirty
Read
Lost update
Phantom
/IR
X
X
X
O
X
X
O
O
X
O
O
O
Lost Update
Transaction2
Transaction1
Read
X=A
A=10
A=10
A=13
Write
A=X+1
Read
X=A
A=11
Write
A=X+3
Serialized Result
A=14
Dirty Read
Transaction2
Transaction1
Read
X=A
A=10
A=20
A=10
Write
A=X+5
Write
A=20
A=25
Rollback
Serialized Result
A=15
Inconsistent Read (IR)
Transaction2
Transaction1
Write
A2=4
A1=1,A2=3
Sum=0
A1=1,A2=4
Sum=1
A1=1,A2=4
Sum=5
Write
A1=5
Sum=A1
A1=5,A2=4
Sum=5
Sum+=A2
Serialized Result
Sum= 4 or 9
Phantom
Transaction2
A1=1,A3=3
Sum=0,Ave=0
Transaction1
Insert
A2=5
COMMIT
Sum
A1-A3
A1=1,A3=3
Sum=4,Ave=0
A1=1,A2=5,A3=3
Sum=4,Ave=0
Serialized Result
A1=1,A2=5,A3=3
Sum=4,Ave=2
Sum=4,Ave=3
Or
Sum=9,Ave=3
Ave
A1-A3
Locks in Uncommitted Read
No Read Lock!
Transaction1
X
UPDATE
IX
(X-X Collides)
Transaction2
X
INSERT
Transaction3
SELECT
Dirty Read!
Locks in Committed Read
Tentative Read Locks
Transaction1
S X
UPDATE
IX
IX
IS
X
S
Commit
INSERT
Transaction3
SELECT
Transaction2
SELECT
No Dirty Reads
Inconsistent Reads
Lock in Repeatable Read
2PL Read Locks
No Inconsistent Transaction1
Reads
UPDATE
IS
S
Transaction2
S
INSERT
Transaction3
SELECT
SELECT
Still See Phantoms
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
RangeTuple Mode
S
S
S_S
S
U
S_U
I
Null
I_N
X
X
X_X
Description
Share Range-Share Resource Lock
Serializable Level Range Scan
Share Range-Update Resource Lock
Serializable Level Update Scan
Insert Range-NULL Resource Lock
Used to Insert A New Tuple
X Range-X Resource Lock
Used to Update Some Keys in Range
SQL Server : Serializable
:Serializable
Transaction
1 S
SELECT 1
SELECT 5
2(X)
5 S
IS
7
I_N
OK!
INSERT
2
SELECT Range
Scan
7-9
S_S
9
S_S
SELECT
No-Result
20
Fetch
I_N
wait
INSERT
8
DB2: Lock Type
IN
IN Y
IS Y
NS Y
S
Y
U
Y
IX Y
SIX Y
X
Y
NW Y
W Y
Z
N
IS NS
Y Y
Y Y
Y Y
Y Y
Y Y
Y N
Y N
N N
N Y
N N
N N
S
Y
Y
Y
Y
Y
N
N
N
N
N
N
U
Y
Y
Y
Y
N
N
N
N
N
N
N
IX
Y
Y
N
N
N
Y
N
N
N
N
N
SIX
Y
Y
N
N
N
N
N
N
N
N
N
X
Y
N
N
N
N
N
N
N
N
N
N
NW
Y
N
Y
N
N
N
N
N
N
Y
N
W
Y
N
N
N
N
N
N
N
Y
N
N
Z
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 is a Read Lock Compatible to NW-Lock
=Collide with INSERT only in Serializable Level
NS
S
IN
Y
Y
IS NS
Y Y
Y Y
S
Y
Y
U
Y
Y
IX
N
N
SIX
N
N
X
N
N
NW W Z
Y N N
N N 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

W
S/NS
NW
W
W
IN
NW Y
W Y
W
NW
NW
W W
W
IS NS
N Y
N N
S
N
N
U
N
N
IX
N
N
SIX
N
N
X
N
N
・・・
NW W Z
N Y N
Y N N
DB2(Next Key Lock for Deletion)
Leave X-Lock on Next Key for
uncommitted deleted tuple
 Happens in all Levels

Lower Isolation Level
Lower Isolation Level
DELETE 3 (Uncommitted)
INSERT 2
2 W
1
1
3
3X
4 -> 4X ->
7
7
9
9
1
4X
7
9
NW
Next Key Lock for Deletion
To Inform transactions in
Serializable Level of uncommitted
deleted keys
DB2(Type2 Index:V8.1-)




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
Lower Isolation Level
DELETE 3 (Pre-commit)
1
1
3
3X
4 -> 4 ->
7
7
9
9
1
3X
4
7
9
Lower Isolation Level
INSERT 2
2X
NW
After Commit
1
4
7
9
Clean up
on REORG
DB2(Deferred Locking:V8.1.4-)
 Table-Scan
causes Table-Lock
Even Though Only a few tuples
are SELECTed
DELETE 4
UPDATE 4
ID Val (No Index)
1
20
3
10
4
40 X
7
50
9
30
NS
NS
NS
Wait!
NS
SELECT
WHERE VAL<=30
Read and apply
Lock
and then Read
predicates
toand
apply
predicates
then
Lock