06 07 lock

6-7: 1

Gray& Reuter: Locking

Concurrency ControlJim Gray Jim Gray

Microsoft, Gray @ Microsoft.comMicrosoft, Gray @ Microsoft.com

Andreas ReuterAndreas ReuterInternational University, [email protected] University, [email protected]

9:00

11:00

1:30

3:30

7:00

Overview

Faults

Tolerance

T Models

Party

TP mons

Lock Theory

Lock Techniq

Queues

Workflow

Log

ResMgr

CICS & Inet

Adv TM

Cyberbrick

Files &Buffers

COM+

Corba

Replication

Party

B-tree

Access Paths

Groupware

Benchmark

Mon Tue Wed Thur Fri

6-7: 2


Concurrency Control: Outline

• Why lock (isolation)Why lock (isolation)

• When to lock (2øWhen to lock (2ø locking, 1°, 2°,3° isolationlocking, 1°, 2°,3° isolation

• What to lock (granularity)What to lock (granularity)

• How to lockHow to lock

• Exotics (optimistic, field calls, escrow)Exotics (optimistic, field calls, escrow)

• DeadlockDeadlock

• PerformancePerformance

6-7: 3


Why Lock?• Need isolation (the "I" of ACID): Need isolation (the "I" of ACID): • Give each transaction the illusion that Give each transaction the illusion that

there are no concurrent updatesthere are no concurrent updates• Hide concurrency anomalies.Hide concurrency anomalies.• Do it Do it automaticallyautomatically

– (system does not know transaction semantics)(system does not know transaction semantics)• Goal: Goal:

– Although there is concurrency in systemAlthough there is concurrency in systemexecution is equivalent to some serial execution execution is equivalent to some serial execution of the systemof the system

– Not deterministic outcome, just a consistent Not deterministic outcome, just a consistent transformationtransformation

6-7: 4


The Essentials• Transactions Transactions ConflictConflict if Reads And Writes overlap if Reads And Writes overlap

• More formally:More formally:Transaction T has Transaction T has Read Set: R(T) Read Set: R(T)

Write Set: W(T)Write Set: W(T)• T1 and T2 conflict IFF T1 and T2 conflict IFF

W(T2) & (R(T1) U W(T1)) W(T2) & (R(T1) U W(T1)) ≠ ≠ ØØOrOr W(T1) & (R(T2) U W(T2)) W(T1) & (R(T2) U W(T2)) ≠ ≠ ØØ

• If they conflict, delay one until the other finishesIf they conflict, delay one until the other finishes

OK: DISJOINT RANGE AND DOMAIN

BAD: T1 READS OUTPUTS OF T2

6-7: 5


Laws Of Concurrency Control

• First Law of Concurrency ControlFirst Law of Concurrency ControlConcurrent execution should not cause Concurrent execution should not cause application programs to malfunction.application programs to malfunction.

• Second Law of Concurrency ControlSecond Law of Concurrency ControlConcurrent execution should not have lower Concurrent execution should not have lower throughput or much higher response times than throughput or much higher response times than serial execution.serial execution.

6-7: 6










6-7: 7

Gray& Reuter: LockingFormal Model: Transactions and Serial(izble) Histories

• StateState is a set of name value pairs: DB = {<e,v>}is a set of name value pairs: DB = {<e,v>}• ActionsActions are defined on state: are defined on state:

<Ti, read, e> means Ti reads value of entity e<Ti, read, e> means Ti reads value of entity e<Ti, write, e> means Ti writes value of entity e<Ti, write, e> means Ti writes value of entity e

• Each Each TransactionTransaction is a sequence of actions: is a sequence of actions:Ti = < <Ti, a, e> | i = 1,...,ni> Ti = < <Ti, a, e> | i = 1,...,ni>

• Want to “run” a set of transactions: T = {Ti | i= 1,...,n}Want to “run” a set of transactions: T = {Ti | i= 1,...,n}• A A HistoryHistory is any sequence S such that is any sequence S such that

each and every Ti is a subsequence of Seach and every Ti is a subsequence of S• A history is A history is SerialSerial if it is of the form: if it is of the form:

Ti,Tj,.....,Tz (i.e. one transaction at a time).Ti,Tj,.....,Tz (i.e. one transaction at a time).• A history is A history is serializableserializable ( (isolatedisolated)) if it is equivalent to a if it is equivalent to a

serial history.serial history.

6-7: 8

Gray& Reuter: LockingFormal Mode: Execution History Equivalence

• Define the following permutation subgroup:Define the following permutation subgroup:<Ti READ ei> commutes with <Tj READ ej> (for all i, j) <Ti READ ei> commutes with <Tj READ ej> (for all i, j) <Ti WRITE ei> commutes with < Tj {READ|WRITE} ej> <Ti WRITE ei> commutes with < Tj {READ|WRITE} ej> (iff i (iff i ≠≠ j and ei j and ei ≠ ≠ ej)ej)

• Si is Si is EquivalentEquivalent to Sj if it can be permuted to Sj to Sj if it can be permuted to Sj• Alternative DefinitionAlternative Definition (inputs and outputs): (inputs and outputs):• Define the Define the DependencyDependency set of history set of history S D(S): S D(S):

{<Ti,e,Tj> | <Ti,ai,e> ,...,<Tj,aj,e> is a subsequence of S {<Ti,e,Tj> | <Ti,ai,e> ,...,<Tj,aj,e> is a subsequence of S and ai = write or aj = write }and ai = write or aj = write }

(note: some initial and terminal dependencies are also needed) (note: some initial and terminal dependencies are also needed)

• Two Schedules are equivalentTwo Schedules are equivalent iff they have same iff they have same dependenciesdependencies

6-7: 9


<e,1>T1

<e,2>T2

W W

<e,3>

T1<e,1>

T2

R W

<e,2><e,1>

T1<e,2>

T2

R

<e,1>

W

<e,2>

Transaction Dependency Relations

• Shows data flow among transactionsShows data flow among transactionsT1 READ T1 READ ⟨⟨e,1e,1⟩⟩ T1 WRITE T1 WRITE ⟨⟨e,2e,2⟩⟩ T1 WRITE T1 WRITE ⟨⟨e,2e,2⟩⟩T2T2 WRITE WRITE ⟨⟨e,2e,2⟩⟩ T2 READ T2 READ ⟨⟨e,2e,2⟩⟩ T2 WRITE T2 WRITE ⟨⟨e,3e,3⟩⟩

6-7: 10

Gray& Reuter: Locking The Three Bad Transaction Dependencies

Locks are often used to prevent these dependencies.

T1

<e,1>

<e,2>

T2

<e,1><e,1>

<e,3><e,2>

Lost UpdateT2 READ ⟨e,1⟩ T1 WRITE ⟨e,2⟩ T2 WRITE ⟨e,3⟩

T1

<e,2>

T2

<e,2> <e,1>

<e,2><e,3>

Dirty ReadT2 WRITE ⟨e,2⟩ T1 READ ⟨e,2⟩ T2 WRITE ⟨e,3⟩

T1

T2

<e, 1>

<e,2>

<e,1>

<e,2>

UnRepeatable ReadT1 READ ⟨e,1⟩ T2 WRITE ⟨e,2⟩ T1 READ ⟨e,2⟩

T2

<e,1> <e,1>

T1

OK

6-7: 11

Gray& Reuter: LockingDependencies Show Dataflows Among Transactions

T1

T2

T3

T4

T5

T6

T1

T2

T3

T4

T5

T6

• Two histories are equivalent iff they have the same dependencies• We want only histories equivalent to a serial history.• If T2 depends on data from T1, then T2 ran after T1.• If T4 depends on data from T3, then T4 ran after T3.• This is a wormhole (in time): T4 ran after T4.

T3-> T4 -> T6 ->T3 ->T4 • Cycles in the dependency graph are bad.

6-7: 12


Locks Cover ActionsIntroduce three new actions:Introduce three new actions:

LOCK [READ | WRITE]LOCK [READ | WRITE]UNLOCKUNLOCK

Lock <Ti, LOCK a, e > Lock <Ti, LOCK a, e > CoversCovers <Ti, a', e><Ti, a', e>IfIf<Ti, a', e> is at or after the lock step<Ti, a', e> is at or after the lock stepANDANDNo intervening unlockNo intervening unlockANDANDa is WRITE OR a' IS READ a is WRITE OR a' IS READ (write covers write and read)(write covers write and read)

t slock o1 t xlock o2 t read o1 t read o3 t write o1 t write o2 t read o2 t unlock o1 t unlock o2 t unlock o3

Not CoveredNot Covered

Not Covered

6-7: 13


Well Formed and Two-Phased Transactions

• Transaction T is well formed if:All actions of T are covered by a lock

Lock Act Unlock

• Transaction T is 2-phase if:No unlock precedes a lock in T.(i.e. A T has a LOCK phase and an UNLOCK phase)

GROW Shrink

• Basic rules:Basic rules:– Lock everything you read/writeLock everything you read/write– Hold locks to end-of-Hold locks to end-of-

transactiontransaction

6-7: 14


Lock Commutativity

<Ti ,LOCK, ei> <Ti ,LOCK, ei> commutes with commutes with <Tj ,LOCK, ej><Tj ,LOCK, ej> (if i (if i ≠ ≠ j, and (ei j, and (ei ≠≠ ej)) ej))

<Ti ,LOCK READ, e> commutes with <Tj ,LOCK READ, e><Ti ,LOCK READ, e> commutes with <Tj ,LOCK READ, e> (if i (if i ≠ ≠ j) j)

C O M P A T IB IL IT Y M O D E O F L O C K S H A R E

C O M P A T IB L E C O N F L IC T

E X C L U S IV E C O N F L IC T C O N F L IC T

S H A R E E X C L U S IV E

M O D E O F R E Q U E S T

6-7: 15


LOCKS define LEGAL HISTORIES• History isHistory is legallegal if: if:

Don't grant incompatible locks to two at once.Don't grant incompatible locks to two at once.If T1 covers e with a WRITE lock, If T1 covers e with a WRITE lock, No T2 covers e at that point in the history No T2 covers e at that point in the history (if T1 (if T1 ≠≠

T2).T2). Legal & Serial

T1 SLOCK A

T1 XLOCK B

T1 READ A

T1 WRITE B

T1 UNLOCK A

T1 UNLOCK B

T2 SLOCK A

T2 READ A

T2 XLOCK B

T2 WRITE B

T2 WRITE B

T2 UNLOCK A

T2 UNLOCK B

T1 Begin T1 Slock A T1 Xlock B T1 Read A T1 Write B T1 Commit

T2 Begin T2 Slock A T2 Read A T2 Xlock B T2 Write B T2 Rollback

T2 SLOCK A

T1 SLOCK A

T2 READ A

T2 XLOCK B

T2 WRITE B

T2 WRITE B

T2 UNLOCK A

T2 UNLOCK B

T1 XLOCK B

T1 READ A

T1 WRITE B

T1 UNLOCK A

T1 UNLOCK B

T2 Begin T2 Slock A T2 Read A T2 Xlock B T2 Write B T2 Rollback

T1 Begin T1 Slock A T1 Xlock B T1 Read A T1 Write B T1 Commit

Legal & Not Serial

T1 SLOCK A

T1 XLOCK B

T2 SLOCK A

T2 READ A

T2 XLOCK B

T2 WRITE B

T2 WRITE B

T2 UNLOCK A

T2 UNLOCK B

T1 READ A

T1 WRITE B

T1 UNLOCK A

T1 UNLOCK B

T Begin T Slock A T Xlock B T Read A T Write B T Commit

T' Begin T' Slock A T' Read A T' Xlock B T' Write B T' Rollback

NotLegal & Not Serial

6-7: 16

Gray& Reuter: LockingSERIALIZABILITY THEOREM(Wormhole Theorem)

1. If all transactions are well formed and 21. If all transactions are well formed and 2∅∅then all legal histories are serializable.then all legal histories are serializable.

2. If T1 is not well formed or not 22. If T1 is not well formed or not 2∅ ∅ then there is a T2 such thatthen there is a T2 such that

T1 and T2 have a legal T1 and T2 have a legal but not serializable historybut not serializable history

except for trivial cases.except for trivial cases.

6-7: 17


WF & 2∅ => No Wormholes• Consider first unlock in scheduleConsider first unlock in schedule

<Ti, unlock, e><Ti, unlock, e>• Claim: Ti is a “first” Claim: Ti is a “first”

– There is no Tj >> Ti for all j There is no Tj >> Ti for all j ≠≠ I I (Tj before Ti)(Tj before Ti)

• Suppose not (Suppose not (suppose Tj >> Tisuppose Tj >> Ti): ): – Then Tj accesses some e2 Then Tj accesses some e2 – Then Tj unlocks e2 (Tj is WF)Then Tj unlocks e2 (Tj is WF)– Then Ti locks e2 (Ti is WF)Then Ti locks e2 (Ti is WF)– Then Ti reads or writes e2Then Ti reads or writes e2

– So Tj unlock is before Ti unlock (2So Tj unlock is before Ti unlock (2∅∅ ) =><=) =><=

• Contradiction proves Ti is a first.Contradiction proves Ti is a first.

6-7: 18

Gray& Reuter: Locking Proof of Wormhole TheoremIf no wormhole, then equivalent to serial

Induction: Induction: If no cycles, then there is a “last” transactionIf no cycles, then there is a “last” transaction

Permute it to end of history (this will preserve dependencies).Permute it to end of history (this will preserve dependencies).

So, equivalent history.So, equivalent history.

By induction remaining history is equivalent to a serial history.By induction remaining history is equivalent to a serial history.

Original T4 Moved T3 Moved T2 Moved = Serial

T1

T2

T3

T4

T1T1T1

T2T2T2T2

T3T3T3T3

T4T4T4T4T1

T2T3

T4

T1

T1

T1T2

T2

T2

T2

T3

T3

T3

T3

T4T4

T4

T4

T1

T2T3

T4

T1

T1

T1T2

T2

T2

T2

T3

T3

T3

T3

T4T4T4T4

T1

T2

T3

T4

T1

T1

T1

T2

T2

T2

T2

T3T3T3T3

T4T4T4T4

6-7: 19


Proof of serializability theorem 2.1. Not WF = > nonserial

T1 not well formed: <T1, a, e> not covered by a lock.T1 not well formed: <T1, a, e> not covered by a lock.

Construct Construct WF & 2WF & 2∅∅ T2 = T2 = <<T2,LOCK WRITE,e><<T2,LOCK WRITE,e>

,<T2, WRITE,e>,<T2, WRITE,e>

,<T2,UNLOCK,e>>,<T2,UNLOCK,e>>

Now the history H: Now the history H: < ...< ...

,<T2,LOCK WRITE,e>,<T2,LOCK WRITE,e>

,<T1,a, e>,<T1,a, e>

,<T2, WRITE,e>,<T2, WRITE,e> ,<T2,UNLOCK,e>,<T2,UNLOCK,e>

,...>,...>

Is legal, but not equivalent to serialIs legal, but not equivalent to serial

T1 is "after" T2 and also "before" T2.T1 is "after" T2 and also "before" T2.

T1T2 e

e

e

6-7: 20

Gray& Reuter: Locking Proof of serializability theorem 2.1. Not 2∅ = > nonserial

T1 NOT 2T1 NOT 2∅ ∅ meansmeans <T1,unlock, e1> ... <T1, LOCK, e2<T1,unlock, e1> ... <T1, LOCK, e2

Construct WF & 2Construct WF & 2∅∅ T2: T2: <<T2,LOCK READ, e1>,<T2, LOCK READ,e2>,<T2, UNLOCK, e1>,<T2,UNLOCK, e2>>

Now the history Now the history H: H: < ...

,<T1,UNLOCK, e1>,<T2,LOCK READ, e1>,<T2, LOCK READ,e2>

,<T2, UNLOCK, e1> ,<T2,UNLOCK, e2>>

,<T1,LOCK, e2,...>

Is legal, but not equivalent to a serial historyIs legal, but not equivalent to a serial historyT1 is "after" T2 and also "before" T2T1 is "after" T2 and also "before" T2

T2

T1e1

e1e2

e2

6-7: 21


Restatement of serializability theorem

• Lock everything transaction accessesLock everything transaction accesses

• Do not lock after unlockDo not lock after unlock..• Backout may have to undo a unlock (Backout may have to undo a unlock (== lock). lock).

• So do not release locks prior to commit So do not release locks prior to commit øø11

• Keep exclusive locks (write locks) to commit Keep exclusive locks (write locks) to commit øø22

6-7: 22


Serializability Theorems• Wormhole TheoremWormhole Theorem::

A history is isolated if, and only if, it has no wormhole A history is isolated if, and only if, it has no wormhole transactions.transactions.

• Locking TheoremLocking Theorem:: If all transactions are well-formed and two-phase, then any If all transactions are well-formed and two-phase, then any legal history will be isolated.legal history will be isolated.

• Locking Theorem (converse)Locking Theorem (converse)::If a transaction is not well-formed or is not two-phase, then If a transaction is not well-formed or is not two-phase, then it is possible to write another transaction, such that the it is possible to write another transaction, such that the resulting pair is a wormhole.resulting pair is a wormhole.

• Rollback TheoremRollback Theorem:: An update transaction that does an UNLOCK and then a An update transaction that does an UNLOCK and then a ROLLBACK is not two-phase.ROLLBACK is not two-phase.

6-7: 23










6-7: 24


Isolation Levels = Degrees of { Isolation | Consistency } 00°° :: transaction gets short xlocks for writestransaction gets short xlocks for writes

(well formed writes not 2Ø, no read locks)(well formed writes not 2Ø, no read locks)

11°° :: transaction gets no read lockstransaction gets no read locks(well formed and (well formed and 22ØØ writes,) writes,)

22°° :: transaction releases read locks right after transaction releases read locks right after read read (well formed with respect to reads (well formed with respect to reads

but not but not 22ØØ with respect to reads) with respect to reads)

33°° :: well formed and 2Øwell formed and 2Ø

(= Serializable by previous theorems!!)(= Serializable by previous theorems!!)

Transaction backout prohibits 0Transaction backout prohibits 0°°..

6-7: 25


What Do Systems Do?Most non SQL systems support 2Most non SQL systems support 2°°

Most SQL systems default to 3Most SQL systems default to 3°° and allow forms of 1 and allow forms of 1°°, , 22°°e.g.:e.g.: NonStop SQL: NonStop SQL: 11°° = BROWSE = BROWSE

22°° ~ STABLE READ ~ STABLE READ

DB2:DB2: 22°° ~ CURSOR STABILITY ~ CURSOR STABILITY

33°° ~ REPEATABLE READ ~ REPEATABLE READ

SQL Standard SQL Standard

11°° = READ UNCOMMITTED = READ UNCOMMITTED

22°° = READ COMMITTED = READ COMMITTED

2.992.99°° = REPEATABLE READ = REPEATABLE READ

33°° = SERIALIZABLE = SERIALIZABLE

6-7: 26


Isolation Levels Theorem

If others lock 1° or 2° If others lock 1° or 2°

and I lock 3° then I get 3° (serializable).and I lock 3° then I get 3° (serializable).

Any other trans is before me or after me.Any other trans is before me or after me.

BUTBUT

DB may be corrupted by them.DB may be corrupted by them.

6-7: 27

Gray& Reuter: LockingComparison of Isolation Levels

ProtectionProvided

Lets others runat higherisolation

0 and no lost updates

1+No dirty reads

2 +Repeatable

readsCommitted data Writes visible

immediatelyWrites visible at

eotSame Same

Dirty data You don'toverwrite dirty

data

0 and others donot overwrite

your dirty data

0, 1, and youdon't read dirty

data

0,1,2 and othersdon't dirty data

you readLock protocol Set short Set long 1 and set short 1 and set long

exclusive lockson data you

write

exclusive lockson data you

write

share locks ondata you read

share locks ondata you read

Trans structure Well-formedWrt writes

Well-formed wrtwrites

Two-phase wrtwrites

Well-formedAnd

Two-phase wrtwrites

Well-formed and

Two-phase

Concurrency Greatest: only set short write locks

Great: only wait for write locks

Medium: hold few read locks

Lowest: any datatouchedLocked to eot

Issue Degree 0 Degree 1 Degree 2 Degree 3Common name Chaos Isolated

SerializableRepeatable reads

Browseread uncommited

Cursor stabilityread committed

Rollback supported

6-7: 28


Comparison of Isolation Levels IssueIssue Degree 0Degree 0 Degree 1Degree 1 Degree 2Degree 2 Degree 3Degree 3

OverheadOverhead Least:Least:

short W locksshort W locksSmall:Small:

Only write locksOnly write locksMedium: Medium:

Set R&W Set R&W but short Rbut short R

Most:Most:

Set long R&WSet long R&W

RollbackRollback UNDO may UNDO may cascadecascade

Cant rollbackCant rollback

Can Undo Can Undo incomplete incomplete

transactionstransactions

samesame SameSame

System RecoverySystem Recovery Dangerous, Dangerous, Updates may Updates may be lost and be lost and violate 3violate 3°°

Apply log in 1Apply log in 1°°

orderorderSameSame samesame

DependenciesDependencies NoneNone W W →→ W W W W →→ W W

W W →→ R R

W W →→ W W

W W →→ R R

R R →→ W W

6-7: 29










6-7: 30


The Phantom Detail• If I try to read If I try to read hair = "red" and eyes = "blue"hair = "red" and eyes = "blue"

and get and get not foundnot found, what gets locked?, what gets locked?No records have been accessed so no records get lockedNo records have been accessed so no records get locked

• If I delete a record, what gets locked?(the record is gone)If I delete a record, what gets locked?(the record is gone)• These are cases of These are cases of phantomphantom records. records.• Predicate locksPredicate locks solve this problem (see below) solve this problem (see below)• Page LocksPage Locks (done right) can solve this problem (done right) can solve this problem

lock the red hair page and the blue eye page,lock the red hair page and the blue eye page,prevents others red hair and blue eye inserts & updatesprevents others red hair and blue eye inserts & updates

• High volume TP systems use esoteric locking mechanisms:High volume TP systems use esoteric locking mechanisms:

Key Range LocksKey Range Locks:: to protect b-trees to protect b-trees

Hole LocksHole Locks:: to protect space for uncommitted deletes to protect space for uncommitted deletes

6-7: 31


Predicate LocksRead and write sets can be defined by predicates Read and write sets can be defined by predicates

(e.g. Where clauses in SQL statements)(e.g. Where clauses in SQL statements)When a transaction accesses a set for the first time,When a transaction accesses a set for the first time,

1. Automatically capture the predicate1. Automatically capture the predicate2. Do set intersection with predicates of others.2. Do set intersection with predicates of others.3. Delay this transaction if conflict with others.3. Delay this transaction if conflict with others.

Problems with predicate locks:Problems with predicate locks:1.1. Set intersection = predicate satisfiability is NP complete Set intersection = predicate satisfiability is NP complete (slow).(slow).2.2. Hard to capture predicatesHard to capture predicates3. Pessimistic:3. Pessimistic: Jim locks Jim locks eye = blue eye = blue

Andreas locksAndreas locks hair=red hair=red Predicate says conflict, but DB may not have blue eyed red haired Predicate says conflict, but DB may not have blue eyed red haired person.person.

6-7: 32


Precision Locks: Lazy Predicate Locks

Precision locksPrecision locks solve problems 1 & 3: solve problems 1 & 3:

Check returned records against predicates on Check returned records against predicates on each read/writeeach read/write

Example: Example:

Andreas can't insert/read blue eyesAndreas can't insert/read blue eyes

Jim can't insert/read red hair.Jim can't insert/read red hair.

check records as they go bycheck records as they go by

6-7: 33

Gray& Reuter: Locking Granularity Of LocksAn Engineering Solution To Predicate Locks

Can lock whole DB, whole file, or just one key value.Size of lock is called granule.

DATABASE

FILE-1 FILE-2 FILE-3

KEY-A KEY-A KEY-A

Idea:Idea:

Pick a fixed set of predicatesPick a fixed set of predicates

They form a lattice under and, orThey form a lattice under and, or

This can be represented as a graphThis can be represented as a graph

Lock the nodes in this graphLock the nodes in this graph

Simple example:Simple example:

6-7: 34


Lock Granularity

Batch wants to lock whole DBBatch wants to lock whole DB

Interactive wants to lock recordsInteractive wants to lock records

How can we allow both granularities?How can we allow both granularities?

Intention modeIntention mode locks on coarse granules locks on coarse granules

Simple compatibility matrixSimple compatibility matrix

Compatibility MatrixMode I ntent S hare e X clusive

I + - -S - + -X - - -

6-7: 35


Lock Granularity: refined intent modesIntent mode locks say locks being set at finer granularityIntent mode locks say locks being set at finer granularity

If only reading at finer granularity then I compatible with S. If only reading at finer granularity then I compatible with S.

Introduce Introduce IS: intend to set fine S locksIS: intend to set fine S locks

IX: intend to set fine S or X locksIX: intend to set fine S or X locks

SIX: S + IXSIX: S + IXCompatibility Matrix

IS IX S SIX XIS + + + + -IX + + - - -S + - + - -

SIX + - - - -X - - - - -

6-7: 36


Granularity ExampleNotes:Notes:

T3 is waitingT3 is waiting

T2 has all of file 3 locked sharedT2 has all of file 3 locked shared

Most of file 2 locked shared (Fine granularity)Most of file 2 locked shared (Fine granularity)

T1 has record locks in file-1 and file-2T1 has record locks in file-1 and file-2

Rules:Rules:

Lock root to leafLock root to leaf

If set X,S below get IX or IS aboveIf set X,S below get IX or IS above

On a DAG (Directed Acyclic Graph)On a DAG (Directed Acyclic Graph)

Get Get ONEONE IS,IS,...,S path for reads IS,IS,...,S path for reads

Get Get ALLALL IX,IX,...,X paths for a write IX,IX,...,X paths for a write

DATABASE

FILE-1 FILE-2 FILE-3

KEY-A KEY-A KEY-A

T1:IX, T2:IS T3:S

T1:X T1:S, T2:S

T1:IX T1:IX, T2:IS T2:S

T1:XT2:S

T1:IX T2:IS

T1:IX

T1:X

T3:S

T1:IX

T1:S

T2:IS

T2:S

T2:ST2:ST2:S

T2:S

6-7: 37

Gray& Reuter: LockingUpdate Mode Locks (minor tangent)Most common form of deadlockMost common form of deadlock

T1 READ AT1 READ A (lock A shared)(lock A shared)

T2 READ AT2 READ A (lock A shared)(lock A shared)T1 UPDATE AT1 UPDATE A (lock A exclusive, wait for T2)(lock A exclusive, wait for T2)T2 UPDATE AT2 UPDATE A (deadlock A exclusive, wait for T1)(deadlock A exclusive, wait for T1)

So introduce update mode lock:So introduce update mode lock: Compatibility Matrix

IS IX S SIX U XIS + + + + - -IX + + - - - -

S + - + - - -SIX + - - - - -

U - - + - - -X - - - - - -

U compatible with S so updators do not hurt readers. If certain to update record then get x mode lock right away.

6-7: 38


EscalationIf transaction gets too many locks, system probably guessed If transaction gets too many locks, system probably guessed

wrong about granularitywrong about granularityConvert fine grain locks to one coarse oneConvert fine grain locks to one coarse oneExample:Example:

1000 record locks on table t becomes1000 record locks on table t becomes1 file lock on table t.1 file lock on table t.

In some contexts, DE-Escalation is best:Get course grained locks.Get course grained locks.Remember fine grain resource names.Remember fine grain resource names.On callback: On callback:

request fine-grained locks and request fine-grained locks and de-escalate coarse lockde-escalate coarse lock

6-7: 39


Lock Conversion

If requested lock already held in one If requested lock already held in one mode, mode,

new mode is: max (old, requested)new mode is: max (old, requested) X

SIX U

IX S

IS

6-7: 40


Key Range Locking (for Phantoms)

Suppose operations are:Suppose operations are:

Read(key)Read(key); ; /* return current value/* return current value */*/

Write(key, value)Write(key, value); /* set key's value; /* set key's value */*/

Delete(key)Delete(key); ; /* delete key + value /* delete key + value */*/

Read_Next(key)Read_Next(key); ; /* returns next key + val /* returns next key + val */*/

Insert between X and Y must test to see that Insert between X and Y must test to see that

No one else cares that [X,Y] was empty, but is No one else cares that [X,Y] was empty, but is now full now full

no other concurrent trans did a Read_Next("X");).no other concurrent trans did a Read_Next("X");).

6-7: 41


Key Range Locking (a solution)Prev-Key Key-Range locking.Prev-Key Key-Range locking.

Fixed rangesFixed ranges [A,B), [B,C),...., [Z, [A,B), [B,C),...., [Z,∞∞)) (this is easy)(this is easy)Dynamic ranges:Dynamic ranges:

If X, Y, Z are in listIf X, Y, Z are in list ....[X, Y), [Y,Z), [Z,....[X, Y), [Y,Z), [Z,∞∞)) are the lock ranges. List changes as list changes.are the lock ranges. List changes as list changes.

Ranges named by first key in range.Ranges named by first key in range.Lock a range when operating on the rangeLock a range when operating on the rangeInsert and delete Lock 3 ranges [X. Y), [X, Z), [Y, Z)Insert and delete Lock 3 ranges [X. Y), [X, Z), [Y, Z)Dual Dual Is next keyIs next key locking. locking.Best solution is not published.Best solution is not published.

6-7: 42


DAG Locking

In general predicate locks and key-range locks form a DAG In general predicate locks and key-range locks form a DAG not just a tree:not just a tree:a lock can have many parents.a lock can have many parents.

Blue-eye key rangeBlue-eye key rangeBlonde-hair key range.Blonde-hair key range.

Hierarchical locks work for this.Hierarchical locks work for this.Read locks any pathRead locks any pathWrites lock all paths.Writes lock all paths.

Hair IndexAuburn Black Blonde Brunette Platinum Red White Yellow

Eye Index Black Blue

Brown Green Gray Red

Famous People T : IX, T' : IXT : IX T’ : IX

T : XT' : S

Blonde Hair

Red Hair

Blue EyesDon

Marilyn

MonroeT : X

6-7: 43






• How to lock




6-7: 44

Gray& Reuter: LockingBasic Synchronization Primitives (hardware)

boolean CompareAndSwap(long * cell, long old, long new){ if ( *cell == old) { *cell = *new; return TRUE:}

{ *old = * cell; return FALSE} }

value = Load Locked(long * cell); other stuff;

boolean = StoreConditional(*cell, value); /* store conditional fails if cell has changed */

Can use these operators to implement:Shared storage managersQueuesSemaphores (latches)

6-7: 45


Lock Interface (API)LOCK (name,(name, - name of resource- name of resource

,mode,mode - S, X, SIX, IX, IS, U- S, X, SIX, IX, IS, U,duration,duration - instant, short, long- instant, short, long,wait,wait )) - no, timeout,yes - no, timeout,yes

UNLOCK (name (name - name of resource - name of resource ,clear,clear )- decrement count to zero )- decrement count to zero

or not.or not.Locks must count: if lock twice and unlock once, lock keptLocks must count: if lock twice and unlock once, lock keptLock state must be saved (somehow) at Lock state must be saved (somehow) at

transaction save points (allow rollback)transaction save points (allow rollback)commit (allow restart to reacquire)commit (allow restart to reacquire)generally, save is implicit (recompute them)generally, save is implicit (recompute them)

6-7: 46


LOCK NAME HASH LINK SEMAPHORE MODE WAITS? QUEUE

HEADERFROM HASH TABLE

NEXT IN HASH CHAIN

NEXT IN QUEUE HEADER MODE HELD MODE DESIRED GRANTED? DURATION COUNT TRANSACTION

GRANTED GROUP

WAITING GROUP

list of locks of transaction T (from Trans control block) next lock of T

Most locks are free most of the timeMost locks are free most of the timeso only allocate space for a lock if busyso only allocate space for a lock if busyhash table points at lock nameshash table points at lock names

Lock queue:Lock queue:

Notes: this is a very busy one, most queues = 1 holderNotes: this is a very busy one, most queues = 1 holdertransaction lock queue helps at commit (want to free all locks of T)transaction lock queue helps at commit (want to free all locks of T)semaphores cover hash chain, lock queue (not shown below)semaphores cover hash chain, lock queue (not shown below)

6-7: 47

Gray& Reuter: Locking LOCK Control Flow

Hash Name & Search for NameHash Name & Search for NameNot Found: Not Found: Allocate & Format Allocate & Format

Header and BodyHeader and BodyExitExit

Requestor Already Granted To Requestor?Requestor Already Granted To Requestor?Yes: Requested Mode Compatible With Other Granted?Yes: Requested Mode Compatible With Other Granted?

Yes: Yes: Grant, Bump Count, ExitGrant, Bump Count, ExitNo:No: Bump Count, Set Desire, Wait Bump Count, Set Desire, Wait

No:No: Allocate Queue Element at EndAllocate Queue Element at EndAnyone Waiting?Anyone Waiting?

Yes: Mark Waiting, WaitYes: Mark Waiting, WaitNo: Compatible With Grantees?No: Compatible With Grantees?

Yes: GrantYes: GrantNo: WaitNo: Wait

ExitExit

6-7: 48


UNLOCK Control FlowHash Name & Find NameHash Name & Find NameFind Requestor In QueueFind Requestor In QueueDecrement CountDecrement CountIf Count > 0 Then Exit -- No ChangeIf Count > 0 Then Exit -- No ChangeDequeue Requestor Dequeue Requestor If Queue Empty ThenIf Queue Empty Then Deallocate HeaderDeallocate Header

ExitExitFor Each Waiting Conversion For Each Waiting Conversion

If Compatible With Granted GroupIf Compatible With Granted GroupThen Mark Granted & WakeupThen Mark Granted & Wakeup

If No More Conversions Waiting Then If No More Conversions Waiting Then For Each Waiter (in FIFO order)For Each Waiter (in FIFO order)

If Compatible With GrantedIf Compatible With GrantedThen Mark Granted & WakeupThen Mark Granted & Wakeup

Else ExitElse ExitExitExit

6-7: 49







• Exotics (optimistic, field calls, escrow)



6-7: 50


Optimistic LockingIdea: hope no one else changes dataIdea: hope no one else changes data

Timestamp: Timestamp: keep timestamp with each objectkeep timestamp with each objectdefer all updatesdefer all updatesat ø1 commit:at ø1 commit: lock objectslock objects

if timestamps original then abortif timestamps original then abortelse release read lockselse release read locks

at ø2 commit: apply deferred updates at ø2 commit: apply deferred updates

Value:Value: keep original value of each objectkeep original value of each objectat ø1 commit:at ø1 commit: lock objectslock objects

if values original then abortif values original then abortelse release read lockselse release read locks

at ø2 commit: apply deferred updatesat ø2 commit: apply deferred updates

6-7: 51


Optimistic Locking: Field Calls Field call: if predicate then update predicates: single variable query updates: SQL update clause

(single variable update)

Assumption: if predicate true, transform appliesgood for hot spotsoriginal idea in IMS/FP MSDBS.

at call: test predicate if false give diagnosticelse record call in log

at commit ø1: locktest predicateif false then abort (restart)

at commit ø2: apply updateunlock

Examples:Examples:

no predicate, just an update , just an update ,

no contention:no contention:updateupdate transactions transactions set count := count + 1set count := count + 1where branch = where branch =

:home_branch;:home_branch;

typical predicate:update update inventoryinventoryset quantity = quantity - set quantity = quantity -

:delta:deltawhere part_no = :partwhere part_no = :partand quantity > :delta;and quantity > :delta;

6-7: 52


Escrow LockingSchemes that hold locks at end of Ø1 fail to solve hot spot Schemes that hold locks at end of Ø1 fail to solve hot spot

problem for distributed system since ø1 to Ø2 transition problem for distributed system since ø1 to Ø2 transition may take timemay take time

Idea: Idea: leave value fuzzy, leave value fuzzy, store minimum and maximum valuesstore minimum and maximum values

Poly-values of (Warren Montgomery's MIT thesis)Poly-values of (Warren Montgomery's MIT thesis)Escrow locking of (Pat O’Neil)Escrow locking of (Pat O’Neil)Example: I order 100 widgets:Example: I order 100 widgets:

quantity quantity was:was: [1000,1000][1000,1000]now is:now is: [900, 1000][900, 1000]

I commitI commit now is:now is: [900, 900][900, 900]No locks are heldNo locks are heldWorks only for commutative opsWorks only for commutative ops

6-7: 53


Versioning vs Locking (Oracle’s Approach)

Records & Objects have values over time: R: [T0..T1): V0, [T1…T2) V1, [T2….now) V3.

Value time Ti is transaction commit timestamp.

Writes set conventional locks and transaction sees its own updates

Transaction makes read request at time Tx, returnValue as of that timeValue as of time statement began (Oracle Consistent Read)

Value as of time transaction began (Snapshot Isolation)(= Oracle Serializable)

There is a whole neat theory here waiting to be worked out:see “A Critique of SQL Isolation Levels”, SIGMOD 95.

6-7: 54


Tradeoffs

• Deferred updates are confusing for user (application).Deferred updates are confusing for user (application).

• Value is good if object small (record)Value is good if object small (record)

• Timestamp is good if object large (file)Timestamp is good if object large (file)

• Field calls more general than eitherField calls more general than either

• Field calls great for SQL systems (automatic)Field calls great for SQL systems (automatic)

• Field call commutativity assumption is prone to errorField call commutativity assumption is prone to error

6-7: 55


Weird Lock OptionsAvailable To Application

Read past locks: caller wants to skip locked itemse.g.,: get next free item in a queue,

Notify locks: caller waits for object to changee.g.: wait for nonempty queue or trigger on data change

Bounce locks:caller never waits (timeout = 0)example: want any free record.

Read thru locks: wants to see all data, even uncommitted updatesexample: ad hoc query

Adaptive locking:holder of lock notified that others wait holder releases/changes lock access

6-7: 56








• Deadlock


6-7: 57


Deadlock DetectionDeadlock: Deadlock: a cycle in the wait-for grapha cycle in the wait-for graphKinds of waits:Kinds of waits: database locksdatabase locks

terminal/device terminal/device storagestoragesessionsessionserverserver

Correct detection must get complete graphCorrect detection must get complete graphNot likely, so always fall back on timeoutNot likely, so always fall back on timeoutModel of deadlock shows:Model of deadlock shows:

waits are rarewaits are raredeadlocks are raredeadlocks are rare2 2 (very very rare)(very very rare)virtually all cycles are length 2virtually all cycles are length 2so do depth-first search eitherso do depth-first search either as soon as you waitas soon as you wait

OROR after a timeoutafter a timeout

6-7: 58


How To Find a Deadlock

Call each object manager getting his part of Call each object manager getting his part of wait-for graphwait-for graph

Do depth-first search (transitive closure) of Do depth-first search (transitive closure) of graphgraph

If find cycle: pick minimum cost victimIf find cycle: pick minimum cost victim

Reference: Obermarck and BeriReference: Obermarck and Beri

6-7: 59


Model of Deadlock (1)R:R:number of objects (locks)number of objects (locks)r:r: objects locked per transactionobjects locked per transactionN+1: Concurrent TransactionsN+1: Concurrent TransactionsASSUMEASSUME

Transaction is: LOCKTransaction is: LOCKr r lock lock

steps, then commitsteps, then commitUniform distributionUniform distributionexclusive locks onlyexclusive locks onlyNr << RNr << R

r

R

Nxr/2

Probability a request waits: PW requestr N

R( ) ≈

××2

Prob a transaction waits: PW Transactionr N

R( ) ≈

××

2

2

6-7: 60


Model of Deadlock (2)R:R: number of objects (locks)number of objects (locks)

r:r: objects locked per transactionobjects locked per transaction

N+1: Concurrent TransactionsN+1: Concurrent Transactions

Probability of cycle Probability of cycle length 2 + length 3 +...length 2 + length 3 +...

PWN

PWN

2 31 2

+

+...

≈ ≈PW

N

2

≈r N

R

4

24

Prob transaction deadlocks PD:assumes all cycles of length 2 ≈

r N

R

4 2

24System deadlock rate, N+1 times higher

Conclusions: control transaction size and duration

limit multiprogramming

6-7: 61


Common Performance Bugs

Convoys on semaphores or high-traffic locksConvoys on semaphores or high-traffic locksLog semaphore is hotspot Log semaphore is hotspot Sequential insert is hotspotSequential insert is hotspotLock manager costs too much Lock manager costs too much

A good number: 300 instructions for lock+unlock A good number: 300 instructions for lock+unlock (no wait case))(no wait case)) = 1500 clocks on a P6/200 = 1500 clocks on a P6/200

file or page granularity locking causes hotspot for file or page granularity locking causes hotspot for small filessmall files

6-7: 62



• Why lock (isolation)

• When to lock (2ø locking, 1°, 2°,3° isolation

• What to lock (granularity)

• How to lock

• Exotics (optimistic, field calls, escrow)

• Deadlock

• Performance

06 07 lock

Education

gray reuter

lockingwhy lock

serial execution execution

concurrency anomalies

locking concurrency

lower concurrent execution

field calls

transaction t