Exam 3 Cont.

View Serializability

A schedule is view serializable if it is view equivalent to a serial schedule. §Schedules S1 and S2 are view equivalent if they satisfy the following conditions: For every data item A, •First read should be performed by same transaction oIf T1 reads initial value of A in S1, then T1 also reads initial value of A in S2 •Write-Read sequences should be maintained oIf T1 reads value of A written by Tj in S1, then T1 also reads value of A written by Tj in S2 •Last write should be performed by same transaction oFor all data object A, if T1 writes final value of A in S1, then T1 also writes final value of A in S2 §S is view serializable, if it is view equivalent to some serial schedule

Recoverable Schedule

A schedule where, for each pair of transactions Ti and Tj, if Tj reads a data item previously written by Ti, then the commit operation of Ti precedes the commit operation of Tj. §A recoverable schedule is one in which transactions commit only after all transactions whose changes they read commit. •Avoid cascading aborts §Note that aborted transaction' effects are not supposed to be visible to other transaction. §A committed transaction should never be rolled back. §

Schedules with Aborts

§ Actions of aborted transactions have to be undone completely •May be impossible in some situations oSay T2 reads the fund from an account and adds interest oT1 aims to deposit money but aborts •If T2 has not committed, we can "cascade" aborts by aborting T2 as well • • •If T2 has committed, we have an "unrecoverable schedule" oExample of unrecoverable schedule: Note: A serializable schedule is a schedule whose effect on any consistent database instance is guaranteed to be identical to that of some complete serial schedule over S • T1: R(A), W(A), Abort T2: W(A), W(B), Commit T1: R(A), W(A), Abort T2: W(A), W(B), Commit

PL vs Strict 2PL

§2PL: •First, acquire all locks, release none (a.k.a. Growing phase) •Second, release locks, cannot acquire any other lock ((a.k.a. Shrinking phase) §Strict 2PL: •Release write (X) lock, only after it has ended (committed or aborted) • §Strict 2PL allows only serializable schedules. •Additionally, it simplifies transaction aborts •Two transactions can acquire locks on different objects independently • §(Non-Strict) 2PL also allows only serializable schedules, but involves more complex abort processing • §

Serializable Schedule

§A schedule S is Serializable iff the effect of the execution of S is identical as some serial schedule S' for every database state. •If the effect on any consistent database instance is guaranteed to be identical that of "some" complete serial schedule for a set of "committed transactions" §However, no guarantee on T1à T2 or T2 à T1 Note that two schedules shown in the right figures represent an interleaved execution of the two transactions. Schedule S2 is equivalent to the Serial schedule S1 Ensuring transaction isolation while permitting such concurrent execution is difficult but necessary for performance reasons.

Anomalies with Interleaved Execution

§A schedule involving two consistency-preserving transactions could run against a consistent database and might leave it in inconsistent state. Two actions on the same data object conflict if at least one of them is a write. §Three anomalous situations can be described in terms of when the actions of two transactions T1 and T2 conflict with each other. •Write-Read (WR) Conflict •Read-Write(RW) Conflict •Write-Write(WW) Conflict • §No conflict with RR if no write is involved

Lock-Based Concurrency Control

§DBMS should ensure that only serializable and recoverable schedules are allowed •No actions of committed transactions are lost • §Uses a locking protocol § §Lock: associated with each "object" •Different granularity §Locking protocol: •A set of rules to be followed by each transaction and enforced by the DBMS §

Dependency Graph

§Dependency graph: •Also called conflict graph, or serializability graph •One node per transaction •Directed edge from Ti to Tj if an action of Ti precedes and conflicts with one of Tj actions oWi(A) --- RJ(A), or oRi(A) --- WJ(A), or oWi(A) --- WJ(A), or •Ti must precede Tj in any serial schedule §Theorem: A schedule is conflict serializable if and only if its dependency graph is acyclic § i.e.§A schedule that is not conflict serializable: § § § § § § §The cycle in the graph reveals the problem. The output of T1 depends on T2, and vice-versa.

Serial Schedule Part II

§If transactions execute one at a time, serially, with each transaction doing a commit before the next one begins, there is no problem of interference with one another's transaction. §However, users often need to access data simultaneously. •If all users are only reading data, there is no way they can interfere with one another and there is no need for concurrency control. •If user are accessing different parts of the database, there transactions can run concurrently without a problem. •However, when two users try to make updates simultaneously to the same data or one update while another read the same, there may be conflict. §We need to plan a schedule that can process multiple transactions at the same time to maximize throughput while preserving database consistency. èNeed for Concurrency Control §

Anomalies with Interleaved Execution: WW Conflict

§Overwriting Uncommitted Data (WW Conflicts. "lost update"): •T2 overwrites the value of A, which has been modified by T1, still in progress •Suppose we need the sale price of two products (A and B) to be the same oT1 sets them to $1,000 oT2 sets them to $2,000 oThe result is not identical to the result of either of the two possible serial executions, and the interleaved schedule is therefore not serializable. ─It violates the desired consistency criterion that the two price must be equal.* T1: W(A), W(B), C T2: W(A), W(B), C

Anomalies with Interleaved Execution: WR Conflict

§Reading Uncommitted Data (WR Conflicts, "dirty reads"): •Transaction T2 reads an object that has been modified by T1 but not yet committed •Or T2 reads an object from an inconsistent database state (like fund is being transferred between two account) • §Suppose two transactions T1 and T2, each of which, run alone, preserves database consistency: T1 transfers $100 from A to B, and T2 increments both A and B by 6%. •The problem is that value of A written by T1 is read by T2 before T1 has completed all its changes. • §Note that although a transaction must leave a database in a consistent state after it completes, it is not required to keep the database consistent while it is still in progress. For example, to transfer money from one account to another, a transaction must debit one account, temporarily leaving the database inconsistent, and then credit the second account, restoring consistency, •T1: R(A), W(A), R(B), W(B), Abort T2: R(A), W(A), Commit §pp 21

Conflict Serializable Schedule

§Serial schedules have less resource utilization and low throughout. To improve it, two or more transactions are run concurrently. However, concurrency of transactions may lead to inconsistency in database. To avoid this, we need to check whether theses concurrent schedules are serializable or not. §Two schedules are conflict equivalent if: •Involve the same actions of the transactions •Every pair of conflicting actions is ordered the same way §Schedule S is called conflict serializable if S is conflict equivalent to some serial schedule. In other words, it can be transformed into a serial schedule by swapping non-conflicting operations. §

Strict 2PL and Conflict Serailizability

§Strict 2PL allows only schedules whose precedence graph is acyclic §Can never allow cycles as the X locks are being held by one transaction §However, it is sufficient but not necessary for serializability §Relaxed solution: View Serializability §

Strict 2PL(cont'd)

§Strict 2PL is too strict for serializability § § §Strict schedules are recoverable, no need cascading aborts §A DBMS must be able to ensure that only serializable, recoverable schedules are allowed, and that no actions of committed transactions are lost while undoing aborted transactions.

Strick two-phase locking (Strict 2PL)

§Strict Two-phase Locking (Strict 2PL) Protocol: 1.Each transaction must obtain oa S (shared) lock on object before reading, and an X (exclusive) lock on object before writing. ─Exclusive locks also allow reading an object, additional shared lock is not required. ─ If an transaction holds an X lock on an object, no other transaction can get a lock (S or X) on that object. ─Transaction is suspended until it acquires the required lock 2.All locks held by a transaction are released when the transaction completes §Strict 2PL allows only serializable schedules.

Serial Schedule

§Transactions are executed from start to finish one by one •There is no interference between transactions, since only one is executing at any given time. •There is no guarantee that the results of all serial schedules (ex. T1 à T2 or T2 àT1) of a given transactions will be identical. •Every serial schedule is considered to be correct, even though different results may be produce by different orders. §A serial schedule executes each transaction in isolation so every serial schedule will preserve the consistency (correctness ) of the database. Hence, we can use a serial schedule as the basis for the definition of correctness for concurrent execution of transactions. §Note that a schedule that contains either an abort or a commit for each transaction whose actions are listed in it is called a complete schedule.

Two-Phase Locking (2PL)

§Two-Phase Locking Protocol •Each transaction must obtain a S (shared) lock on object before reading, and an X (exclusive) lock on object before writing. •A transaction cannot request additional locks once it releases any locks. • If an transaction holds an X lock on an object, no other transaction can get a lock (S or X) on that object.

Anomalies with Interleaved Execution: RW Conflict

§Unrepeatable Reads (RW Conflicts): •T2 changes the value of an object A that has been read by transaction T1, which is still in progress •If T1 tries to read A again, it will get a different result •Suppose two customers are trying to buy the last copy of a book simultaneously. §T1: R(A), R(A), W(A), Commit T2: R(A), W(A), Commit

Example of Strict2PL

T1: R(A), W(A), R(B), W(B), Commit T2: R(A), W(A), R(B), W(B), Commit §WR conflict (dirty read) §Strict 2PL does not allow this T1: X(A), R(A), W(A), T2: Has to wait for Lock on A T1: X(A), R(A), W(A), X(B), R(B), W(B), Commit, U(A), U(B) T2: X(A), R(A), W(A), X(B), R(B), W(B), Commit, U(A), U(B)

Scheduling transcations

A schedule S of n transactions T1, T2, ..., Tn is an ordering for the operations of the transactions. •Serial schedule: Transactions are performed one after another, without any interleaving of operations. •Equivalent schedules: For any database state, the effect (on the set of objects in the database) of executing the first schedule is identical to the effect of executing the second schedule. •Serializable schedule: If a set of transactions executes concurrently, the schedule is serializable if it is produce the same results as some serial schedule. oNote: If each transaction preserves consistency, every serializable schedule preserves consistency.