Concurrency Busy busy In production environments it is

Concurrency

Busy, busy. . . • In production environments, it is unlikely that we can limit our system to just one user at a time. – Consequently, it is possible for multiple queries or transactions to be submitted at approximately the same time. • If all of the queries were very small (i. e. , in terms of time), we could probably just execute them on a first come first served basis. • However, many queries are both complex and time consuming. – Executing these queries would make other queries wait a long time for a chance to execute. • So, in practice, the DBMS may be running many different transactions at about the same time.

Concurrent Transactions • Even when there is no “failure, ” several transactions can interact to turn a consistent state into an inconsistent state.

Example • Assume A = B is a constraint required for consistency. • • T 1 READ(A, t) t = t + 100 WRITE(A, t) T 2 READ(A, s) s=s*2 WRITE(A, s) READ(B, t) T = t + 100 WRITE(B, t) READ(B, s) S=s*2 WRITE(B, s) Note that we omit OUTPUT steps for succinctness; they always come at the end. We deal only with Reads and Writes in the main memory buffers. T 1 and T 2 individually preserve DB consistency.

An Acceptable Schedule S 1 • Assume initially A = B = 25. Here is one way to execute (S 1= T 1; T 2) so they do not interfere. T 1 T 2 READ(A, t) t=t+ 100 WRITE(A , t) A 25 B 25 12 5 READ(B, t) T=t+ 100 WRITE(B , t) 12 5

Another Acceptable Schedule S 2 • Here, transactions are executed as (S 2=T 2; T 1). The result is but consistency is B maintained. Tdifferent, T A 1 2 READ(A, 25 25 s) s=s*2 WRITE(A 50 , s) READ(B, s) S=s*2 WRITE(B 50 , s) READ(A, t)

Interleaving Doesn't Necessarily Hurt (S 3) T 1 T 2 READ(A, t) t=t+ 100 WRITE(A , t) A 25 12 5 READ(A, s) s=s*2 WRITE(A 25 , s) 0 READ(B, B 25

But Then Again, It Might! T 1 T 2 READ(A, t) t=t+ 100 WRITE(A , t) A 25 12 5 READ(A, s) s=s*2 WRITE(A 25 , s) 0 READ(B, B 25

Semantics of transactions is also important.

We Need a Simpler Model • Coincidence never happens • Focus on reads and writes only. – r. T(X) denotes T reads X – w. T(X) denotes T writes X – Transaction is a sequence of r and w actions on database elements. – If transactions are T 1, …, Tk, then we use ri and wi, instead of r. Ti and w. Ti • Schedule is a sequence of r and w actions performed by a collection of transactions. – Serial Schedule: All actions for each transaction are consecutive. r 1(A); w 1(A); r 1(B); w 1(B); r 2(A); w 2(A); r 2(B); w 2(B); … – Serializable Schedule: A schedule whose “effect” is equivalent to that of some serial schedule.

Conflicts • Suppose for fixed DB elements X and Y, ri(X); rj(Y) is part of a schedule, and we flip the order of these operations. Then ri(X); rj(Y) ≡ rj(Y); ri(X) This holds always (even when X=Y) • We can flip ri(X); wj(Y) as long as X≠Y However, ri(X); wj (X) wj(X); ri (X) In the RHS, Ti reads the value of X written by Tj, whereas it is not so in the LHS. • We can flip wi(X); wj(Y); provided X≠Y However, wi(X); wj(X); wi(X); The final value of X may be different depending on which write occurs last.

Conflicts (Cont’d) There is a conflict if one of these two conditions hold. 1. A read and a write of the same X, or 2. Two writes of the same X • Such actions conflict in general and may not be swapped in order. • All other events (reads/writes) may be swapped without changing the effect of the schedule (on the DB). Definitions • Two scheduless are conflict-equivalent if they can be converted into the other by a series of non conflicting swaps of adjacent elements • A schedule is conflict-serializable if it can be converted into a serializable schedule in the same way

Example r 1(A); w 1(A); r 2(A); w 2(A); r 1(B); w 1(B); r 2(B); w 2(B) r 1(A); w 1(A); r 2(A); r 1(B); w 2(A); w 1(B); r 2(B); w 2(B) r 1(A); w 1(A); r 1(B); r 2(A); w 1(B); w 2(A); r 2(B); w 2(B) r 1(A); w 1(A); r 1(B); w 1(B); r 2(A)w 2(A); r 2(B); w 2(B)

Conflict serializability • Sufficient condition for serializability but not necessary. Example S 1: w 1(Y); w 1(X); w 2(Y); w 2(X); w 3(X); This is serial S 2: w 1(Y); w 2(X); w 1(X); w 3(X); • S 2 isn’t conflict serializable, but it is serializable. It has the same effect as S 1. – Intuitively, the values of X written by T 1 and T 2 have no effect, since T 3 overwrites them.

Serializability/precedence Graphs • Non swappable pairs of actions represent potential conflicts between transactions. • The existence of non swappable actions enforces an ordering on the transactions that house these actions. • Nodes: transactions {T 1, …, Tk} • Arcs: There is an arc from Ti to Tj if they have conflict access to the same database element X and Ti is first; in written Ti <S Tj.

Precedence graphs r 2(A); r 1(B); w 2(A); r 3(A); w 1(B); w 3(A); r 2(B); w 2(B) Note the following: §w 1(B) <S r 2(B) §r 2(A) <S w 3(A) ØThese are conflicts since they contain a read/write on the same element ØThey cannot be swapped. Therefore T 1 < T 2 < T 3 r 2(A); r 1(B); w 2(A); r 2(B); r 3(A); w 1(B); w 3(A); w 2(B) Note the following: §r 1(B) <S w 2(B) §w 2(A) <S w 3(A) §r 2(B) <S w 1(B) ØHere, we have T 1 < T 2 < T 3, but we also have T 2 < T 1 16

• If there is a cycle in the graph – Then, there is no serial schedule which is conflict equivalent to S. • Each arc represents a requirement on the order of transactions in a conflict equivalentserial schedule. • A cycle puts too many requirements on any linear order of transactions. • If there is no cycle in the graph – Then any topological order of the graph suggests a conflict equivalent schedule.

Why the Precedence Graph Test Works? Idea: if the precedence graph is acyclic, then we can swap actions to form a serial schedule. Proof: By induction on n, number of transactions. Basis: n = 1. That is, S={T 1}; then S is already serial. Induction: S={T 1, T 2, …, Tn}. Given that the precedence graph is acyclic, there exists Ti in S such that there is no Tj in S that Ti depends on. – We swap all actions of Ti to the front (of S). – (Actions of Ti)(Actions of the other n 1 transactions) – The tail is a precedence graph that is the same as the original without Ti, i. e. it has n 1 nodes. By the induction hypothesis, we can reorder the actions of the other transactions to turn it into a serial schedule

Schedulers • A scheduler takes requests from transactions for reads and writes, and decides if it is “OK” to allow them to operate on DB or defer them until it is safe to do so. • Ideal: a scheduler forwards a request iff it cannot result in a violation of serializability. – Too hard to decide this in real time. • Real: a scheduler forwards a request if it cannot result in a violation of conflict serializability.

Lock Actions • Before reading or writing an element X, a transaction Ti requests a lock on X from the scheduler. • The scheduler can either grant the lock to Ti or make Ti wait for the lock. • If granted, Ti should eventually unlock (release) the lock on X. • Shorthands: – li(X) = “transaction Ti requests a lock on X” – ui(X) = “Ti unlocks/releases the lock on X”

Validity of Locks • The use of locks must be proper in 2 senses: – Consistency of Transactions: • Read or write X only when hold a lock on X. – ri(X) or wi(X) must be preceded by some li(X) with no intervening ui(X). • If Ti locks X, Ti must eventually unlock X. – Every li(X) must be followed by ui(X). – Legality of Schedules: • Two transactions may not have locked the same element X without one having first released the lock. – A schedule with li(X) cannot have another lj(X) until ui(X) appears in between.

Legal Schedule Doesn’t Mean Serializable T 1 T 2 A 25 l 1(A); r 1(A) A=A+ 100 w 1(A); u 1( A) B 25 Consistency constraint required for this example: A=B 12 5 l 2(A); r 2(A) A=A*2 w 2(A); u 2( 25 A) 0 l 2(B); r 2(B)

Two Phase Locking There is a simple condition, which guarantees conflict serializability: In every transaction, all lock requests (phase 1) precede all unlock requests (phase 2). T 1 T 2 A B 25 25 l 1(A); r 1(A) A = A + 100 w 1(A); l 1(B)u 1(A) 125 l 2(A); r 2(A) A=A*2 w 2(A) 250 l 2(B) Denied r 1(B) B = B + 100 125 w 1(B); u 1(B) l 2(B); u 2(A); r 2(B) B=B*2 w 2(B); u 2(B) 250

Why 2 PL Works? • Precisely: a legal schedule S of 2 PL transactions is conflict serializable. • Proof is an induction on n, the number of transactions. • Remember, conflicts involve only read/write actions, not locks, but the legality of the transaction requires that the r/w's be consistent with the l/u's.

Why 2 PL Works (Cont’d) • Basis: if n=1, then S={T 1}, and hence S is conflict serializable. • Induction: S={T 1, …, Tn}. Find the first transaction, say Ti, to perform an unlock action, say ui(X). We show that the r/w actions of Ti can be moved to the front of the other transactions without conflict. Consider some action such as wi(Y). Can it be preceded by some conflicting action wj(Y) or rj(Y)? In such a case we cannot swap them. • • – If so, then uj(Y) and li(Y) must intervene, as wj(Y). . . uj(Y). . . li(Y). . . wi(Y). – Since Ti is the first to unlock, ui(X) appears before uj(Y). – But then li(Y) appears after ui(X), contradicting 2 PL. • Conclusion: wi(Y) can slide forward in the schedule without conflict; similar argument for a ri(Y) action.