CIS 720 Concurrency Control Locking Atomic statement Can

CIS 720 Concurrency Control

Locking • Atomic statement – Can be used to perform two or more updates atomically Th 1: …. < x = x + 1; y = z>; ……. Th 2: …………. <m = m + 1; …. >; …….

Transactions • A database system is a set of shared data objects • A transaction is a sequential program which accesses data objects in the database • Each transaction is a sequence of read and write operations

The Transaction Model • Examples of primitives for transactions. Primitive Description BEGIN_TRANSACTION Make the start of a transaction END_TRANSACTION Terminate the transaction and try to commit ABORT_TRANSACTION Kill the transaction and restore the old values READ Read data from a file, a table, or otherwise WRITE Write data to a file, a table, or otherwise

Transactions • Each transaction is a sequence of read and write operations • The read set of transaction T, denoted by rs(t), is a set of variables read by T. • The write set ws(T) is defined similarly

Banking System Deposit(amount, account) { x = db. account; x = x + amount; db. account = x; } Withdraw(amount, account) { y = db. account; if y > amount y = y - amount; db. account = y; }

Distributed Transactions BEGIN_TRANSACTION reserve MCI -> JFK; reserve JFK -> FRK; END_TRANSACTION

Distributed Transactions a) b) A nested transaction A distributed transaction

• A database has an invariant I (integrity constraint). • Each transaction is designed to preserve I • If transactions are executed simultaneously, then they may interfere and invalidate I. • The task of concurrency control is to preserve I.

Banking System Deposit(amount, account) { x = db. account; x = x + amount; db. account = x; } Transaction 1: Deposit $50 in Acc 1 Transaction 2: Deposit $70 in Acc 2 Possible interleavings T 1. x = db. Acc 1; T 1. x = T 1. x + 50 db. Acc 1 = T 1. x T 2. x = db. Acc 1; T 2. x = T 2. x + 70; db. Acc 1 = T 2. x

Concurrency Control • General organization of managers for handling transactions.

Concurrency Control • General organization of managers for handling distributed transactions.

• A schedule is any execution of a set of transaction operations • Two schedules T 1 and T 2 are equivalent if - all read operations return the same value in both schedules - the final database state is the same in both schedules

T 1: T 2: r 1(x)0 w 1(x)1 r 2(y)0 r 2(x)1 w 2(y)2 T 1: T 2: r 1(x)0 r 2(y)0 T 1: T 2: w 1(x)1 r 2(x)1 w 2(y)2 r 1(x)0 w 1(x)1 r 2(y)0 r 2(x)1 w 2(y)2

T 1: r 1(y)1 T 2: w 2(y)1 T 1: r 1(y)0 w 1(x)1 r 2(x)1 w 2(y)2 T 2: w 2(y)1 r 2(x)1 w 2(y)2 T 1: r 1(y)0 w 1(x)1 T 2: w 2(y)1 r 2(x)0 w 2(y)2

• A serial schedule is a schedule in which transactions execute one at a time. • We know that a serial schedule preserves IC of the database • A concurrency control algorithm can restricts the execution so that all schedules are serial.

• A CC ensures that all schedules are equivalent to some serial schedule • A schedule that is equivalent to a serial schedule is called serializable

Untyped Concurrency control • Assumes that all transactions with intersecting read and write sets interfere with one another. • How can we determine whether a schedule is serializable • Let T 1, …, Tn be a set of transactions • Define a graph G with transactions as nodes • There is an edge from Ti to Tj if - there exists a read rj(x) which reads from wi(x) - there exists a read ri(x) that occurs before wj(x) - there exists a write wi(x) that occurs before wj(x)

• A graph is serializable if the graph is acyclic

Two-phase Locking • Obtain a read or write lock before reading or writing a variable respectively. • rl(x): read lock operation • ul(x): unlock operation • wl(x): write lock operation

• Locking rules: - two read locks can be given at the same time; read and write lock must be exclusive * conflict table

• Simple locking does not ensure serializability

T 1: r 1(y)1 T 2: w 2(y)1 T 1: r 1(y)0 w 1(x)1 r 2(x)1 w 2(y)2 T 2: w 2(y)1 r 2(x)1 w 2(y)2 T 1: r 1(y)0 w 1(x)1 T 2: w 2(y)1 r 2(x)0 w 2(y)2

T 1: T 2: L(y) r 1(y)1 ul(y) L(x)w 2(y)1 UL(x) L(x( w 1(x)1 ul(x) L(x) r 2(x)1 ul(x) w 2(y)2

Two phase locking rule • • Locking phase: acquire all locks Unlocking phase: release all locks Two-phase locking ensures serializability It is prone to deadlocks

Two-Phase Locking (1) • Two-phase locking.

Two-Phase Locking (2) • Strict two-phase locking.

Writeahead Log x = 0; y = 0; BEGIN_TRANSACTION; x = x + 1; y=y+2 x = y * y; END_TRANSACTION; (a) • • Log Log [x = 0 / 1] [y = 0/2] [x = 1/4] (b) (c) (d) a) A transaction b) – d) The log before each statement is executed

Graph based protocols • Impose a partial ordering on data items • If d 1 d 2, then any transaction accessing both d 1 and d 2 must first access d 1 before d 2.

Tree protocol • Only exclusive locks are allowed • First item to be locked can be any one • Next, a data item can be locked only if the parent is already locked • Data items can be unlocked at any time • A data item cannot be relocked by a transaction.

Semantics-based concurrency control • If transactions T 1 and T 2 do not interfere then they can be executed concurrently. • Two operations op 1 and op 2 do not conflict if they commute (that is, op 1; op 2 is the same as op 2; op 1)

Predicate Locking • Each transaction specifies a predicate as a lock. • A new transaction can execute if it does not interfere with existing predicate locks