Télécharger la présentation
## CIS 720

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**CIS 720**Concurrency Control**Locking**• Atomic statement • Can be used to perform two or more updates atomically Th1: …. < x = x + 1; y = z>;……. Th2:………….<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.**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**Withdraw(amount, account) { y = db.account; if y > amount y = y - amount; db.account = y; } Deposit(amount, account) { x = db.account; x = x + amount; db.account = x; }**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**Transaction 1: Deposit $50 in Acc1 Transaction 2: Deposit $70 in Acc2 Possible interleavings T1.x = db.Acc1; T1.x = T1.x + 50 T2.x = db.Acc1; T2.x = T2.x + 70; db.Acc1 = T1.x db.Acc1 = T2.x Deposit(amount, account) { x = db.account; x = x + amount; db.account = 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 T1 and T2 are equivalent if - all read operations return the same value in both schedules - the final database state is the same in both schedules**T1: r1(x)0 w1(x)1**T2: r2(y)0 r2(x)1 w2(y)2 T1: r1(x)0 w1(x)1 T2: r2(y)0 r2(x)1 w2(y)2 T1: r1(x)0 w1(x)1 T2: r2(y)0 r2(x)1 w2(y)2**T1: r1(y)1 w1(x)1**T2: w2(y)1 r2(x)1 w2(y)2 T1: r1(y)0 w1(x)1 T2: w2(y)1 r2(x)1 w2(y)2 T1: r1(y)0 w1(x)1 T2: w2(y)1 r2(x)0 w2(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 T1,…,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)**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**T1: r1(y)1 w1(x)1**T2: w2(y)1 r2(x)1 w2(y)2 T1: rl(y) r1(y)1 ul(y)wl(x) w1(x)1 ul(x) T2: wl(y) w2(y)1 ul(y)rl(x) r2(x)1 ul(x)wl(y) w2(y)2 ul(y)**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**• a) A transaction • b) – d) The log before each statement is executed**Graph based protocols**• Impose a partial ordering on data items • If d1 d2, then any transaction accessing both d1 and d2 must first access d1 before d2.**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 T1 and T2 do not interfere then they can be executed concurrently. • Two operations op1 and op2 do not conflict if they commute (that is, op1; op2 is the same as op2; op1)**Predicate Locking**• Each transaction specifies a predicate as a lock. • A new transaction can execute if it does not interfere with existing predicate locks