Gray Reuter Locking Concurrency Control Jim Gray Microsoft

Gray& Reuter: Locking Concurrency Control Jim Gray Microsoft, Gray @ Microsoft. com Andreas Reuter International University, Andreas. Reuter@i-u. de Mon Tue Wed Thur Fri 9: 00 Overview TP mons Log Files &Buffers B-tree 11: 00 Faults Lock Theory Res. Mgr COM+ Access Paths 1: 30 Tolerance Lock Techniq CICS & Inet Corba Groupware 3: 30 T Models Queues Adv TM Replication Benchmark 7: 00 Party Workflow Cyberbrick Party 1

Gray& Reuter: Locking Concurrency Control: Outline • 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 2

Gray& Reuter: Locking Why Lock? • Need isolation (the "I" of ACID): • Give each transaction the illusion that there are no concurrent updates • Hide concurrency anomalies. • Do it automatically – (system does not know transaction semantics) • Goal: – Although there is concurrency in system execution is equivalent to some serial execution of the system – Not deterministic outcome, just a consistent transformation 3

Gray& Reuter: Locking The Essentials • Transactions Conflict if Reads And Writes overlap • More formally: Transaction T has Read Set: R(T) Write Set: W(T) • T 1 and T 2 conflict IFF W(T 2) & (R(T 1) U W(T 1)) ¹ Ø Or W(T 1) & (R(T 2) U W(T 2)) ¹ Ø • If they conflict, delay one until the other finishes 4

Gray& Reuter: Locking Laws Of Concurrency Control • First Law of Concurrency Control Concurrent execution should not cause application programs to malfunction. • Second Law of Concurrency Control Concurrent execution should not have lower throughput or much higher response times than serial execution. 5

Gray& Reuter: Locking Concurrency Control: Outline • 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 6

Formal Model: Transactions and Serial(izble) Histories Gray& Reuter: Locking • State is a set of name value pairs: DB = {<e, v>} • Actions are defined on state: <Ti, read, e> means Ti reads value of entity e <Ti, write, e> means Ti writes value of entity e • Each Transaction is a sequence of actions: Ti = < <Ti, a, e> | i = 1, . . . , ni> • Want to “run” a set of transactions: T = {Ti | i= 1, . . . , n} • A History is any sequence S such that each and every Ti is a subsequence of S • A history is Serial if it is of the form: Ti, Tj, . . . , Tz (i. e. one transaction at a time). • A history is serializable (isolated) if it is equivalent to a serial history. 7

Formal Mode: Execution History Equivalence Gray& Reuter: Locking • Define the following permutation subgroup: <Ti READ ei> commutes with <Tj READ ej> (for all i, j) <Ti WRITE ei> commutes with < Tj {READ|WRITE} ej> (iff i ¹ j and ei ¹ ej) • Si is Equivalent to Sj if it can be permuted to Sj • Alternative Definition (inputs and outputs): • Define the Dependency set of history S D(S): {<Ti, e, Tj> | <Ti, ai, e> , . . . , <Tj, aj, e> is a subsequence of S and ai = write or aj = write } (note: some initial and terminal dependencies are also needed) • Two Schedules are equivalent iff they have same dependencies 8

Transaction Dependency Relations Gray& Reuter: Locking • Shows data flow among transactions T 1 READ áe, 1ñ T 1 WRITE áe, 2ñ T 2 READ áe, 2ñ T 2 WRITE áe, 3ñ R <e, 1> T 1 W <e, 1> T 2 <e, 1> <e, 2> W R T 1 T 2 <e, 2> <e, 1> W W T 1 T 2 <e, 2> <e, 3> 9

The Three Bad Transaction Dependencies Gray& Reuter: Locking Lost Update T 2 READ áe, 1ñ T 1 WRITE áe, 2ñ T 2 WRITE áe, 3ñ <e, 1> <e, 2> T 2 WRITE áe, 2ñ T 1 READ áe, 1ñ T 1 READ áe, 2ñ T 2 WRITE áe, 3ñ T 1 READ áe, 2ñ <e, 1> T 2 T 1 Un. Repeatable Dirty Read T 2 T 1 <e, 2> <e, 3> <e, 1> T 1 OK T 2 T 1 <e, 1> T 2 <e, 2> Locks are often used to prevent these dependencies. 10

Dependencies Show Dataflows Among Transactions Gray& Reuter: Locking T 3 T 1 T 5 T 2 T 4 T 6 • • • T 5 T 2 T 4 T 6 Two histories are equivalent iff they have the same dependencies We want only histories equivalent to a serial history. If T 2 depends on data from T 1, then T 2 ran after T 1. If T 4 depends on data from T 3, then T 4 ran after T 3. This is a wormhole (in time): T 4 ran after T 4. T 3 -> T 4 -> T 6 ->T 3 ->T 4 11 • Cycles in the dependency graph are bad.

Gray& Reuter: Locking Locks Cover Actions Introduce three new actions: LOCK [READ | WRITE] UNLOCK Lock <Ti, LOCK a, e > Covers <Ti, a', e> t t If <Ti, a', e> is at or after the lock step AND t No intervening unlock Not Covered t t Not Covered AND t a is WRITE OR a' IS READ t (write covers write and read) t Not Covered t t slock xlock o 1 o 2 read write read unlock o 1 o 3 o 1 o 2 o 3 12

Well Formed and Two-Phased Transactions Gray& Reuter: Locking • Transaction T is well formed if: All actions of T are covered by a lock Lock Unlock Act • 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: – Lock everything you read/write – Hold locks to end-oftransaction 13

Gray& Reuter: Locking Lock Commutativity <Ti , LOCK, ei> commutes with <Tj , LOCK, ej> (if i ¹ j, and (ei ¹ ej)) <Ti , LOCK READ, e> commutes with <Tj , LOCK READ, e> (if i ¹ j) 14

Gray& Reuter: Locking LOCKS define LEGAL HISTORIES History is legal if: Don't grant incompatible locks to two at once. If T 1 covers e with a WRITE lock, No T 2 covers e at that point in the history (if T 1 ¹ T 2). Legal & Serial T 1 T 1 T 1 T 2 T 2 T 1 T 1 T 1 SLOCK XLOCK READ WRITE UNLOCK SLOCK READ XLOCK WRITE UNLOCK Begin Slock A Xlock B Read A Write B Commit Legal & Not Serial A B A B A A B B B A B T 2 T 1 T 2 T 2 T 2 T 1 T 1 T 1 T 2 Begin T 2 Slock A T 2 Read A T 2 Xlock B T 2 Write B T 2 Rollback T 1 T 1 T 1 SLOCK READ XLOCK WRITE UNLOCK XLOCK READ WRITE UNLOCK Begin Slock A Xlock B Read A Write B Commit A A A B B B A B A B T 2 Begin T 2 Slock A T 2 Read A T 2 Xlock B T 2 Write B T 2 Rollback Not. Legal & Not Serial T 1 T 2 T 2 T 1 T 1 T T T SLOCK XLOCK SLOCK READ XLOCK WRITE UNLOCK READ WRITE UNLOCK Begin Slock A Xlock B Read A Write B Commit A B A A B B B A B A B T' Begin T' Slock A T' Read A T' Xlock B T' Write B T' Rollback 15

SERIALIZABILITY THEOREM (Wormhole Theorem) Gray& Reuter: Locking 1. If all transactions are well formed and 2Æ then all legal histories are serializable. 2. If T 1 is not well formed or not 2Æ then there is a T 2 such that T 1 and T 2 have a legal but not serializable history except for trivial cases. 16

Gray& Reuter: Locking WF & 2Æ => No Wormholes • Consider first unlock in schedule <Ti, unlock, e> • Claim: Ti is a “first” – There is no Tj >> Ti for all j ¹ I (Tj before Ti) • Suppose not (suppose Tj >> Ti): – – – Then Tj accesses some e 2 Then Tj unlocks e 2 (Tj is WF) Then Ti locks e 2 (Ti is WF) Then Ti reads or writes e 2 So Tj unlock is before Ti unlock (2Æ) =><= • Contradiction proves Ti is a first. 17

Proof of Wormhole Theorem If no wormhole, then equivalent to serial Gray& Reuter: Locking Induction: If no cycles, then there is a “last” transaction Permute it to end of history (this will preserve dependencies). So, equivalent history. By induction remaining history is equivalent to a serial history. T 1 T 2 T 2 T 1 T 3 T 3 T 3 T 4 T 4 T 4 T 3 T 1 T 3 T 2 T 1 T 2 T 1 T 4 T 4 T 4 T 3 T 1 T 3 T 2 T 3 T 4 T 2 T 3 T 2 T 1 T 4 T 2 T 4 T 4 T 1 Original T 4 Moved T 1 T 1 T 2 T 2 T 2 T 3 T 3 T 3 T 4 T 4 T 4 T 3 Moved 18 T 2 Moved = Serial

Gray& Reuter: Locking Proof of serializability theorem 2. 1. Not WF = > nonserial T 1 not well formed: <T 1, a, e> not covered by a lock. Construct WF & 2Æ T 2 = <<T 2, LOCK WRITE, e> , <T 2, UNLOCK, e>> Now the history H: <. . . , <T 2, LOCK WRITE, e> , <T 1, a, e> , <T 2, WRITE, e> , <T 2, UNLOCK, e> , . . . > T 2 e T 1 e e Is legal, but not equivalent to serial T 1 is "after" T 2 and also "before" T 2. 19

Gray& Reuter: Locking Proof of serializability theorem 2. 1. Not 2Æ = > nonserial T 1 NOT 2Æ means <T 1, unlock, e 1>. . . <T 1, LOCK, e 2 Construct WF & 2Æ T 2: <<T 2, LOCK READ, e 1> , <T 2, LOCK READ, e 2> , <T 2, UNLOCK, e 1> , <T 2, UNLOCK, e 2>> Now the history H: <. . . , <T 1, UNLOCK, e 1> , <T 2, LOCK READ, e 2> , <T 2, UNLOCK, e 1> , <T 2, UNLOCK, e 2>> , <T 1, LOCK, e 2 , . . . > Is legal, but not equivalent to a serial history T 1 is "after" T 2 and also "before" T 2 e 1 T 1 T 2 e 2 20

Gray& Reuter: Locking Restatement of serializability theorem • Lock everything transaction accesses • Do not lock after unlock. • Backout may have to undo a unlock (= lock). • So do not release locks prior to commit ø 1 • Keep exclusive locks (write locks) to commit ø 2 21

Gray& Reuter: Locking Serializability Theorems • Wormhole Theorem: A history is isolated if, and only if, it has no wormhole transactions. • Locking Theorem: If all transactions are well-formed and two-phase, then any legal history will be isolated. • Locking Theorem (converse): If a transaction is not well-formed or is not two-phase, then it is possible to write another transaction, such that the resulting pair is a wormhole. • Rollback Theorem: An update transaction that does an UNLOCK and then a ROLLBACK is not two-phase. 22

Gray& Reuter: Locking Concurrency Control: Outline • 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 23

Gray& Reuter: Locking Isolation Levels = Degrees of { Isolation | Consistency } 0°: 1°: 2°: 3°: transaction gets short xlocks for writes (well formed writes not 2Ø, no read locks) transaction gets no read locks (well formed and 2Ø writes, ) transaction releases read locks right after read (well formed with respect to reads but not 2Ø with respect to reads) well formed and 2Ø (= Serializable by previous theorems!!) Transaction backout prohibits 0°. 24

Gray& Reuter: Locking What Do Systems Do? Most non SQL systems support 2° Most SQL systems default to 3° and allow forms of 1°, 2° e. g. : Non. Stop SQL: 1° = BROWSE 2° ~ STABLE READ DB 2: 2° ~ CURSOR STABILITY 3° ~ REPEATABLE READ SQL Standard 1° = READ UNCOMMITTED 2° = READ COMMITTED 2. 99° = REPEATABLE READ 3° = SERIALIZABLE 25

Gray& Reuter: Locking Isolation Levels Theorem If others lock 1° or 2° and I lock 3° then I get 3° (serializable). Any other trans is before me or after me. BUT DB may be corrupted by them. 26

Comparison of Isolation Levels Gray& Reuter: Locking Issue Degree 0 Degree 1 Degree 2 Common name Chaos Browse read uncommited Cursor stability read committed Protection Provided Committed data Dirty data Lock protocol Trans structure Concurrency 1+ Lets others run 0 and No dirty reads at higher no lost updates Rollback supported isolation Same Writes visible at immediately eot 0, 1, and you You don't 0 and others do don't read dirty overwrite dirty not overwrite data your dirty data 1 and set short Set long share locks on exclusive locks data you read on data you write Well-formed wrt And Wrt writes Two-phase wrt writes Medium: Greatest: Great: hold few read only set short only wait for locks write locks Degree 3 Isolated Serializable Repeatable reads 2+ Repeatable reads Same 0, 1, 2 and others don't dirty data you read 1 and set long share locks on data you read Well-formed and Two-phase Lowest: any data touched Locked to eot 27

Gray& Reuter: Locking Comparison of Isolation Levels Issue Degree 0 Degree 1 Degree 2 Medium: Set R&W but short R Degree 3 Overhead Least: Small: short W locks Only write locks Most: Set long R&W Rollback UNDO may cascade Cant rollback Can Undo incomplete transactions same System Recovery Dangerous, Updates may be lost and violate 3° Apply log in 1° order Same same Dependencies None W W W R R W 28

Gray& Reuter: Locking Concurrency Control: Outline • 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 29

Gray& Reuter: Locking The Phantom Detail • If I try to read hair = "red" and eyes = "blue" and get not found, what gets locked? No records have been accessed so no records get locked • If I delete a record, what gets locked? (the record is gone) • These are cases of phantom records. • Predicate locks solve this problem (see below) • Page Locks (done right) can solve this problem lock the red hair page and the blue eye page, prevents others red hair and blue eye inserts & updates • High volume TP systems use esoteric locking mechanisms: Key Range Locks: to protect b-trees Hole Locks: to protect space for uncommitted deletes 30

Gray& Reuter: Locking Predicate Locks Read and write sets can be defined by predicates (e. g. Where clauses in SQL statements) When a transaction accesses a set for the first time, 1. Automatically capture the predicate 2. Do set intersection with predicates of others. 3. Delay this transaction if conflict with others. Problems with predicate locks: 1. Set intersection = predicate satisfiability is NP complete (slow). 2. Hard to capture predicates 3. Pessimistic: Jim locks eye = blue Andreas locks hair=red Predicate says conflict, but DB may not have blue eyed red haired person. 31

Precision Locks: Lazy Predicate Locks Gray& Reuter: Locking Precision locks solve problems 1 & 3: Check returned records against predicates on each read/write Example: Andreas can't insert/read blue eyes Jim can't insert/read red hair. check records as they go by 32

Granularity Of Locks An Engineering Solution To Predicate Locks Gray& Reuter: Locking Idea: Pick a fixed set of predicates They form a lattice under and, or This can be represented as a graph Lock the nodes in this graph Simple example: Can lock whole DB, whole file, or just one key value. Size of lock is called granule. 33

Gray& Reuter: Locking Lock Granularity Batch wants to lock whole DB Interactive wants to lock records How can we allow both granularities? Intention mode locks on coarse granules Simple compatibility matrix Mode I S X Compatibility Matrix S hare I ntent + + - e X clusive 34

Gray& Reuter: Locking Lock Granularity: refined intent modes Intent mode locks say locks being set at finer granularity If only reading at finer granularity then I compatible with S. Introduce IS: intend to set fine S locks IX: intend to set fine S or X locks SIX: S + IX IS IX S SIX X Compatibility Matrix S SIX IS IX + + + + + - X - 35

Gray& Reuter: Locking Notes: Granularity Example T 3 is waiting T 2 has all of file 3 locked shared Most of file 2 locked shared (Fine granularity) T 1 has record locks in file-1 and file-2 DATABASE FILE-1 T 1: IX, T 2: IS T 1: IX T 2: IST 3: S FILE-2 FILE-3 T 1: IX, T 2: IS T 1: IX T 2: S T 2: S T 1: X T 2: S Rules: KEY-A T 1: X Lock root to leaf If set X, S below get IX or IS above On a DAG (Directed Acyclic Graph) Get ONE IS, . . . , S path for reads Get ALL IX, . . . , X paths for a write T 1: X KEY-A T 1: S, T 2: S T 1: S T 2: S 36

Update Mode Locks (minor tangent) Gray& Reuter: Locking Most common form of deadlock T 1 READ A T 2 READ A T 1 UPDATE A T 2 UPDATE A (lock A shared) (lock A exclusive, wait for T 2) (deadlock A exclusive, wait for T 1) So introduce update mode lock: IS IX S SIX U X IS + + - Compatibility Matrix SIX IX S + + + - U - X - U compatible with S so updators do not hurt readers. If certain to update record then get x mode lock right away. 37

Gray& Reuter: Locking Escalation If transaction gets too many locks, system probably guessed wrong about granularity Convert fine grain locks to one coarse one Example: 1000 record locks on table t becomes 1 file lock on table t. In some contexts, DE-Escalation is best: Get course grained locks. Remember fine grain resource names. On callback: request fine-grained locks and de-escalate coarse lock 38

Gray& Reuter: Locking Lock Conversion If requested lock already held in one mode, new mode is: max (old, requested) X SIX U IX S IS 39

Gray& Reuter: Locking Key Range Locking (for Phantoms) Suppose operations are: Read(key); /* return current value Write(key, value); /* set key's value Delete(key); /* delete key + value Read_Next(key); /* returns next key + val */ */ Insert between X and Y must test to see that No one else cares that [X, Y] was empty, but is now full no other concurrent trans did a Read_Next("X"); ). 40

Gray& Reuter: Locking Key Range Locking (a solution) Prev-Key Key-Range locking. Fixed ranges [A, B), [B, C), . . , [Z, ¥) (this is easy) Dynamic ranges: If X, Y, Z are in list. . [X, Y), [Y, Z), [Z, ¥) are the lock ranges. List changes as list changes. Ranges named by first key in range. Lock a range when operating on the range Insert and delete Lock 3 ranges [X. Y), [X, Z), [Y, Z) Dual Is next key locking. Best solution is not published. 41

Gray& Reuter: Locking DAG Locking In general predicate locks and key-range locks form a DAG not just a tree: a lock can have many parents. Blue-eye key range Blonde-hair key range. Hierarchical locks work for this. Read locks any path Writes lock all paths. Famous People Hair Index Eye Index T: X Black Blue Brown Green Gray Red TT : IX IX : IX, T’ T' : : IX Blonde Hair Marilyn Monroe Blue Eyes Don Red Hair Auburn Black Blonde Brunette Platinum Red White Yellow T' : S T: X 42

Gray& Reuter: Locking Concurrency Control: Outline • 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 43

Basic Synchronization Primitives (hardware) Gray& Reuter: Locking boolean Compare. And. Swap(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 = Store. Conditional(*cell, value); /* store conditional fails if cell has changed */ Can use these operators to implement: Shared storage managers Queues Semaphores (latches) 44

Gray& Reuter: Locking LOCK Lock Interface (API) (name, , mode , duration , wait ) UNLOCK (name , clear or not. - name of resource - S, X, SIX, IS, U - instant, short, long - no, timeout, yes - name of resource )- decrement count to zero Locks must count: if lock twice and unlock once, lock kept Lock state must be saved (somehow) at transaction save points (allow rollback) commit (allow restart to reacquire) generally, save is implicit (recompute them) 45

Gray& Reuter: Locking Most locks are free most of the time so only allocate space for a lock if busy hash table points at lock names HEADER FROM Lock queue: LOCK NAME HASH TABLE HASH LINK SEMAPHORE MODE WAITS? QUEUE NEXT IN HASH CHAIN GRANTED GROUP list of locks of transaction T (from Trans control block) NEXT IN QUEUE HEADER MODE HELD MODE DESIRED GRANTED? DURATION COUNT TRANSACTION next lock of T WAITING GROUP Notes: this is a very busy one, most queues = 1 holder transaction lock queue helps at commit (want to free all locks of T) semaphores cover hash chain, lock queue (not shown below) 46

Gray& Reuter: Locking LOCK Control Flow Hash Name & Search for Name Not Found: Allocate & Format Header and Body Exit Requestor Already Granted To Requestor? Yes: Requested Mode Compatible With Other Granted? Yes: Grant, Bump Count, Exit No: Bump Count, Set Desire, Wait No: Allocate Queue Element at End Anyone Waiting? Yes: Mark Waiting, Wait No: Compatible With Grantees? Yes: Grant No: Wait Exit 47

Gray& Reuter: Locking UNLOCK Control Flow Hash Name & Find Name Find Requestor In Queue Decrement Count If Count > 0 Then Exit -- No Change Dequeue Requestor If Queue Empty Then Deallocate Header Exit For Each Waiting Conversion If Compatible With Granted Group Then Mark Granted & Wakeup If No More Conversions Waiting Then For Each Waiter (in FIFO order) If Compatible With Granted Then Mark Granted & Wakeup Else Exit 48

Gray& Reuter: Locking Concurrency Control: Outline • 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 49

Gray& Reuter: Locking Optimistic Locking Idea: hope no one else changes data Timestamp: keep timestamp with each object defer all updates at ø 1 commit: lock objects if timestamps original then abort else release read locks at ø 2 commit: apply deferred updates Value: keep original value of each object at ø 1 commit: lock objects if values original then abort else release read locks at ø 2 commit: apply deferred updates 50

Gray& Reuter: Locking Optimistic Locking: Field Calls Field call: if predicate then update predicates: updates: Assumption: single variable query Examples: SQL update clause no predicate, (single variable update) no contention: if predicate true, transform applies good for hot spots original idea in IMS/FP MSDBS. at call: test predicate if false give diagnostic else record call in log at commit ø 1: lock test predicate if false then abort (restart) at commit ø 2: apply update unlock just an update , update transactions set count : = count + 1 where branch = : home_branch; typical predicate: update inventory set quantity = quantity : delta where part_no = : part and quantity > : delta; 51

Gray& Reuter: Locking Escrow Locking Schemes that hold locks at end of Ø 1 fail to solve hot spot problem for distributed system since ø 1 to Ø 2 transition may take time Idea: leave value fuzzy, store minimum and maximum values Poly-values of (Warren Montgomery's MIT thesis) Escrow locking of (Pat O’Neil) Example: I order 100 widgets: quantity was: [1000, 1000] now is: [900, 1000] I commit now is: [900, 900] No locks are held Works only for commutative ops 52

Gray& Reuter: Locking Versioning vs Locking (Oracle’s Approach) Records & Objects have values over time: R: [T 0. . T 1): V 0, [T 1…T 2) V 1, [T 2…. now) V 3. Value time Ti is transaction commit timestamp. Writes set conventional locks and transaction sees its own updates Transaction makes read request at time Tx, return Value as of that time Value 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. 53

Gray& Reuter: Locking Tradeoffs • Deferred updates are confusing for user (application). • Value is good if object small (record) • Timestamp is good if object large (file) • Field calls more general than either • Field calls great for SQL systems (automatic) • Field call commutativity assumption is prone to error 54

Gray& Reuter: Locking Weird Lock Options Available To Application Read thru locks: Read past locks: wants to see all data, caller wants to skip locked even uncommitted updates items example: ad hoc query e. g. , : get next free item in a Adaptive locking: queue, holder of lock notified that others wait Notify locks: holder releases/changes caller waits for object to change lock access e. g. : wait for nonempty queue or trigger on data change Bounce locks: caller never waits (timeout = 0) example: want any free record. 55

Gray& Reuter: Locking Concurrency Control: Outline • 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 56

Gray& Reuter: Locking Deadlock Detection Deadlock: a cycle in the wait-for graph Kinds of waits: database locks terminal/device storage session server Correct detection must get complete graph Not likely, so always fall back on timeout Model of deadlock shows: waits are rare deadlocks are rare 2 (very rare) virtually all cycles are length 2 so do depth-first search either as soon as you wait 57 OR after a timeout

Gray& Reuter: Locking How To Find a Deadlock Call each object manager getting his part of wait-for graph Do depth-first search (transitive closure) of graph If find cycle: pick minimum cost victim Reference: Obermarck and Beri 58

Gray& Reuter: Locking Model of Deadlock (1) R: number of objects (locks) r: objects locked per transaction N+1: Concurrent Transactions ASSUME r Transaction is: LOCK lock steps, then commit Uniform distribution exclusive locks only Nr << R r R Nxr/2 Probability a request waits: Prob a transaction waits: 59

Gray& Reuter: Locking Model of Deadlock (2) R: number of objects (locks) r: objects locked per transaction N+1: Concurrent Transactions Probability of cycle length 2 + length 3 +. . . Prob transaction deadlocks PD: assumes all cycles of length 2 System deadlock rate, N+1 times higher Conclusions: control transaction size and duration limit multiprogramming 60

Gray& Reuter: Locking Common Performance Bugs Convoys on semaphores or high-traffic locks Log semaphore is hotspot Sequential insert is hotspot Lock manager costs too much A good number: 300 instructions for lock+unlock (no wait case)) = 1500 clocks on a P 6/200 file or page granularity locking causes hotspot for small files 61

Gray& Reuter: Locking Concurrency Control: Outline • 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 62