Concurrency Control

Concurrency Control Mon Tue Wed Thur Fri 9:00 Overview TP mons Log Files &Buffers B-tree Jim Gray Microsoft, Gray @ Microsoft.com Andreas Reuter International University, Andreas.Reuter@i-u.de 11:00 Faults Lock Theory ResMgr 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

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

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 systemexecution is equivalent to some serial execution of the system • Not deterministic outcome, just a consistent transformation

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

Laws Of Concurrency Control • First Law of Concurrency ControlConcurrent execution should not cause application programs to malfunction. • Second Law of Concurrency ControlConcurrent execution should not have lower throughput or much higher response times than serial execution.

Formal Model: Transactions and Serial(izble) Histories • Stateis a set of name value pairs: DB = {<e,v>} • Actionsare 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 Historyis 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.

Formal Mode: Execution History Equivalence • 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

Transaction Dependency Relations T2 T1 • Shows data flow among transactionsT1 READ áe,1ñ T1 WRITE áe,2ñ T1 WRITE áe,2ñT2 WRITE áe,2ñ T2 READ áe,2ñ T2 WRITE áe,3ñ W R R W T2 T1 W W <e,1> <e,2> <e,2> T1 T2 <e,1> <e,2> <e,1> <e,1> <e,2> <e,3>

The Three Bad Transaction Dependencies UnRepeatable Read T1 READ áe,1ñT2 WRITE áe,2ñT1 READ áe,2ñ Dirty Read T2 WRITE áe,2ñT1 READ áe,2ñT2 WRITE áe,3ñ Lost Update T2 READ áe,1ñT1 WRITE áe,2ñT2 WRITE áe,3ñ OK <e,1> <e,1> <e,1> <e,1> <e,1> <e,1> <e,2> <e, 1> <e,1> T2 T1 T2 T1 T2 T2 T1 T1 <e,2> <e,2> <e,2> <e,2> <e,2> <e,3> <e,2> <e,3> Locks are often used to prevent these dependencies.

Dependencies Show Dataflows Among Transactions T3 T3 T1 T1 T5 T5 T2 T4 T2 T4 T6 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.

Locks Cover Actions Introduce three new actions:LOCK [READ | WRITE]UNLOCK Lock <Ti, LOCK a, e > Covers<Ti, a', e> If<Ti, a', e> is at or after the lock step ANDNo intervening unlock ANDa is WRITE OR a' IS READ (write covers write and read) t slock o1 t xlock o2 t read o1 Not Covered t read o3 Not Covered t write o1 t write o2 t read o2 t unlock o1 t unlock o2 Not Covered t unlock o3

Well Formed and Two-Phased Transactions • Transaction T is well formed if: All actions of T are covered by a lock • Basic rules: • Lock everything you read/write • Hold locks to end-of-transaction 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

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)

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

SERIALIZABILITY THEOREM(Wormhole Theorem) 1. If all transactions are well formed and 2Æ then all legal histories are serializable. 2. If T1 is not well formed or not 2Æ then there is a T2 such that T1 and T2 have a legal but not serializable history except for trivial cases.

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 e2 • Then Tj unlocks e2 (Tj is WF) • Then Ti locks e2 (Ti is WF) • Then Ti reads or writes e2 • So Tj unlock is before Ti unlock (2Æ) =><= • Contradiction proves Ti is a first.

Proof of Wormhole TheoremIf no wormhole, then equivalent to serial T1 T1 T3 T3 T2 T1 T1 T1 T1 T1 T3 T3 T2 T1 T2 T2 T2 T2 T1 T1 T2 T2 T3 T3 T1 T2 T2 T2 T2 T2 T3 T3 T1 T2 T4 T2 T3 T3 T2 T3 T3 T3 T3 T2 T3 T3 T2 T1 T1 T3 T3 T2 T4 T3 T3 T1 T4 T4 T2 T4 T4 T4 T4 T4 T4 T4 T4 T4 T4 T4 T4 T4 T1 T4 T4 T4 T3 Moved = Serial Original T4 Moved T2 Moved 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.

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

Proof of serializability theorem2.1. Not 2Æ = > nonserial T1 NOT 2Æ means<T1,unlock, e1> ... <T1, LOCK, e2 Construct WF & 2Æ T2:<<T2,LOCK READ, e1> ,<T2, LOCK READ,e2> ,<T2, UNLOCK, e1> ,<T2,UNLOCK, e2>> Now the history 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 history T1 is "after" T2 and also "before" T2 e1 T1 T2 e1 e2 e2

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

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.

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

What Do Systems Do? Most non SQL systems support 2° Most SQL systems default to 3° and allow forms of 1°, 2° e.g.: NonStop SQL: 1° = BROWSE 2° ~ STABLE READ DB2: 2° ~ CURSOR STABILITY 3° ~ REPEATABLE READ SQL Standard 1° = READ UNCOMMITTED 2° = READ COMMITTED 2.99° = REPEATABLE READ 3° = SERIALIZABLE

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.

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

Comparison of Isolation Levels

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

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 predicate2. 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.

Precision Locks: Lazy Predicate Locks 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

Granularity Of LocksAn Engineering Solution To Predicate Locks 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.

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 Compatibility Matrix Mode S hare e X clusive I ntent - - I + + - S - - - X -

Lock Granularity: refined intent modes Compatibility Matrix S SIX X IS IX + + - IS + + - - - IX + + + - - S + - - - - SIX + - - - - X - - 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

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

Update Mode Locks (minor tangent) Most common form of deadlock T1 READ A (lock A shared) T2 READ A (lock A shared) T1 UPDATE A (lock A exclusive, wait for T2) T2 UPDATE A (deadlock A exclusive, wait for T1) So introduce update mode lock: Compatibility Matrix SIX U X IS IX S + - - IS + + + - - - 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.

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

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

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");).

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.

DAG Locking T : IX T’ : IX T : X T' : S T : X 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 T : IX, T' : IX Hair Index Eye Index Auburn Black Black Blonde Blue Blonde Hair Brown Marilyn Brunette Green Platinum Monroe Gray Red Blue Eyes Red White Don Red Hair Yellow

Basic 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 managers Queues Semaphores (latches)

Lock Interface (API) LOCK(name, - name of resource ,mode - S, X, SIX, IX, IS, U ,duration - instant, short, long ,wait ) - no, timeout,yes UNLOCK(name - name of resource ,clear )- decrement count to zero or not. 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)

Most locks are free most of the time so only allocate space for a lock if busy hash table points at lock names Lock queue: 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) HEADER FROM LOCK NAME HASH NEXT IN HASH LINK TABLE HASH CHAIN SEMAPHORE MODE WAITS? QUEUE GRANTED GROUP NEXT IN QUEUE HEADER MODE HELD MODE DESIRED list of locks GRANTED? of transaction DURATION T (from Trans next lock of T COUNT control block) TRANSACTION WAITING GROUP

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

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 Exit

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

Concurrency Control