CONCURRENCY CONTROL 18.1 Serial and Serializable Schedule

1. CONCURRENCY CONTROL18.1 Serial and Serializable Schedule By: Nitin Mathur Id: 110 CS: 257

2. What Is Concurrency Control & Who controls it? • A process of assuming that the transactions preserve the consistency when executing simultaneously is called Concurrency Control. • This consistency is taken care by Scheduler.

3. Flow How Transaction is Executed INPUT READ & WRITE Disk Buffer OUTPUT

4. Transaction Manager Read / Write Requests Scheduler Read & Writes Buffer

5. Correctness Principle • It’s a principle that states that a transaction starts in a correct database state and ends in a correct database state. • Does the system really follow the correctness principal all the time?

6. Basic Example Schedule T1 READ (A,t) t := t+100 WRITE (A,t) READ (B,t) t := t+100 WRITE (B,t) T2 READ (A,s) s := s*2 WRITE (A,s) READ (B,s) s := s*2 WRITE (B,s) A=B=50 To be consistent the final state should be A=B

7. Serial Schedule T1T2AB 50 50 READ (A,t) t := t+100 WRITE (A,t) 150 READ (B,t) t := t+100 WRITE (B,t) 150 READ (A,s) s := s*2 WRITE (A,s) 300 READ (B,s) s := s*2 WRITE (B,s) 300 (T1,T2) A := 2*(A+100)

8. Does the order really matter? T1T2AB 50 50 READ (A,s) s := s*2 WRITE (A,s) 100 READ (B,s) s := s*2 WRITE (B,s) 100 READ (A,t) t := t+100 WRITE (A,t) 200 READ (B,t) t := t+100 WRITE (B,t) 200 (T2,T1) The final state of a database is not independent of the order of transaction.

9. Serializable Schedule T1T2AB 50 50 READ (A,t) t := t+100 WRITE (A,t) 150 READ (A,s) s := s*2 WRITE (A,s) 300 READ (B,t) t := t+100 WRITE (B,t) 150 READ (B,s) s := s*2 WRITE (B,s) 300 Serializable but not Serial Schedule

10. Non-Serializable Schedule T1T2AB 50 50 READ (A,t) t := t+100 WRITE (A,t) 150 READ (A,s) s := s*2 WRITE (A,s) 300 READ (B,s) s := s*2 WRITE (B,s) 100 READ (B,t) t := t+100 WRITE (B,t) 200 A := 2*(A+100) B := 2*B + 100

11. A Serializable Schedule with details T1T2AB 50 50 READ (A,t) t := t+100 WRITE (A,t) 150 READ (A,s) s := s*1 WRITE (A,s) 150 READ (B,s) s := s*1 WRITE (B,s) 50 READ (B,t) t := t+100 WRITE (B,t) 150 A := 1*(A+100) B := 1*B + 100

12. Notations for Transaction • Action : An expression of the form ri(X) or wi(X) meaning that transaction Ti reads or writes, respectively, the database X. • Transaction : A transaction Ti is a sequence of actions with subscript. • Schedule : A schedule S of a transactions T is a sequence of actions, in which for each transaction Ti in T, the action of Ti appear in the definition of Ti itself.

13. Notational Example T1 READ (A,t) t := t+100 WRITE (A,t) READ (B,t) t := t+100 WRITE (B,t) T2 READ (A,s) s := s*2 WRITE (A,s) READ (B,s) s := s*2 WRITE (B,s) Notation: T1 : r1(A); w1(A); r1(B); w1(B) T2 : r2(A); w2(A); r2(B); w2(B)

14. Concurrency Control18.1 – 18.2 Chiu Luk CS257 Database Systems Principles Spring 2009

15. Concurrency Control • Concurrency control in database management systems (DBMS) ensures that database transactions are performed concurrently without the concurrency violating the data integrity of a database. • Executed transactions should follow the ACID rules. The DBMS must guarantee that only serializable (unless Serializability is intentionally relaxed), recoverable schedules are generated. • It also guarantees that no effect of committed transactions is lost, and no effect of aborted (rolled back) transactions remains in the related database.

16. Transaction ACID rules Atomicity - Either the effects of all or none of its operations remain when a transaction is completed - in other words, to the outside world the transaction appears to be indivisible, atomic. Consistency - Every transaction must leave the database in a consistent state. Isolation - Transactions cannot interfere with each other. Providing isolation is the main goal of concurrency control. Durability - Successful transactions must persist through crashes.

17. Serial and Serializable Schedules • In the field of databases, a schedule is a list of actions, (i.e. reading, writing, aborting, committing), from a set of transactions. • In this example, Schedule D is the set of 3 transactions T1, T2, T3. The schedule describes the actions of the transactions as seen by the DBMS. T1 Reads and writes to object X, and then T2 Reads and writes to object Y, and finally T3 Reads and writes to object Z. This is an example of a serial schedule, because the actions of the 3 transactions are not interleaved.

18. Serial and Serializable Schedules • A schedule that is equivalent to a serial schedule has the serializability property. • In schedule E, the order in which the actions of the transactions are executed is not the same as in D, but in the end, E gives the same result as D.

19. A B 25 25 125 125 250 250 250 250 Serial Schedule TI precedes T2 T1 T2 Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B);

20. A B 25 25 50 50 150 150 150 150 Serial Schedule T2 precedes Tl T1 T2 Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B);

21. A B 25 25 125 250 125 250 250 250 serializable, but not serial, schedule T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B); B  B+100; Write(B); Read(B);B  B2; Write(B); r1(A); w1 (A): r2(A); w2(A); r1 (B); w1 (B); r2(B); w2(B);

22. A B 25 25 125 250 50 150 250 150 nonserializable schedule T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(B); B  B+100; Write(B);

23. A B 25 25 125 125 25 125 125 125 schedule that is serializable only because of the detailed behavior of the transactions T1 T2’ Read(A); A  A+100 Write(A); Read(A);A  A1; Write(A); Read(B);B  B1; Write(B); Read(B); B  B+100; Write(B); • regardless of the consistent initial state: the final state will be consistent.

24. Non-Conflicting Actions Two actions are non-conflicting if whenever theyoccur consecutively in a schedule, swapping themdoes not affect the final state produced by theschedule. Otherwise, they are conflicting.

25. Conflicting Actions: General Rules • Two actions of the same transaction conflict: • r1(A) w1(B) • Two actions over the same database element conflict, if one of them is a write • r1(A) w2(A) • w1(A) w2(A)

26. Conflict actions • Two or more actions are said to be in conflict if: • The actions belong to different transactions. • At least one of the actions is a write operation. • The actions access the same object (read or write). • The following set of actions is conflicting: • T1:R(X), T2:W(X), T3:W(X) • While the following sets of actions are not: • T1:R(X), T2:R(X), T3:R(X) • T1:R(X), T2:W(Y), T3:R(X)

27. Conflict Serializable • We may take any schedule and make as many nonconflicting swaps as we wish. • With the goal of turning the schedule into a serial schedule. • If we can do so, then the original schedule is serializable, because its effect on the database state remains the same as we perform each of the nonconflicting • swaps.

28. Conflict Serializable • A schedule is said to be conflict-serializable when the schedule is conflict-equivalent to one or more serial schedules. • Another definition for conflict-serializability is that a schedule is conflict-serializable if and only if there exists an acyclic precedence graph/serializability graph for the schedule. • Which is conflict-equivalent to the serial schedule <T1,T2>, but not <T2,T1>.

29. Conflict equivalent / conflict-serializable • Let Ai and Aj are consecutive non-conflicting actions that belongs to different transactions. We can swap Ai and Aj without changing the result. • Two schedules are conflict equivalent if they can be turned one into the other by a sequence of non-conflicting swaps of adjacent actions. • We shall call a schedule conflict-serializable if it is conflict-equivalent to a serial schedule.

30. conflict-serializable

31. conflict-serializable

32. conflict-serializable

33. conflict-serializable Serial Schedule

34. Conflict-Serializability (section 18.2 of Concurrency Control) - Amith KC Student ID – 006498310

35. Overview • One of the sufficient conditions to assure that a schedule is serializable is “conflict-serializability”. • Idea of Conflicts. • Conflicting and non-conflicting actions. • Conflict-equivalent and conflict-serializability schedules. • Precedence Graphs • Definition. • Test for conflict-serializability.

36. Conflicts • Definition • is a pair of consecutive actions in a schedule such that, if their order is interchanged, then the behavior of at least one of the transactions involved can change. • Non-conflicting actions: Let Ti and Tjbe two different transactions (i ≠ j), then: • ri(X); rj(Y) is never a conflict, even if X = Y. • ri(X); wj(Y) is not a conflict provided X ≠ Y. • wi(X); rj(Y) is not a conflict provided X ≠ Y. • Similarly, wi(X); wj(Y) is also not a conflict, provided X ≠ Y.

37. continued… • Three situations of conflicting actions (where we may not swap their order) • Two actions of the same transaction. • e.g., ri(X);wi(Y) • Two writes of the same database element by different transactions. • e.g., wi(X);wj(X) • A read and a write of the same database element by different transactions. • e.g., ri(X);wj(X) • To summarize, any two actions of different transactions may be swapped unless: • They involve the same database element, and • At least one of them is a write operation.

38. Converting conflict-serializable schedule to a serial schedule • S: r1(A); w1(A); r2(A); w2(A); r1(B); w1(B); r2(B); w2(B); • r1(A); w1(A); r2(A); w2(A); r1(B); w1(B); r2(B); w2(B); • r1(A); w1(A); r2(A); r1(B); w2(A); w1(B); r2(B); w2(B); • r1(A); w1(A); r1(B); r2(A); w2(A); w1(B); r2(B); w2(B); • r1(A); w1(A); r1(B); r2(A); w1(B); w2(A); r2(B); w2(B); • r1(A); w1(A); r1(B); w1(B); r2(A); w2(A); r2(B); w2(B);

39. continued… • Conflict-equivalent schedules: • Two schedules are called conflict-equivalent schedules if they can be turned one into the other by a sequence of non-conflicting swaps of adjacent actions. • Conflict-serializability schedule: • A schedule is conflict-serializable if it is conflict-equivalent to a serial schedule.

40. Precedence Graphs • Conflicting pairs of actions (of a schedule S) put constraints on the order of transactions in the hypothetical, conflict-equivalent serial schedule. • For a schedule S, involving transactions T1 and T2(among other transactions), we say that T1 takes precedence over T2 (written as T1 <s T2)if there are actions A1 of T1 and A2 of T2, such that: • A1 is ahead of A2 in S, • Both A1 and A2 involve the same database element, and • At least one of them is a write operation.

41. continued… • The precedences mentioned in the previous slide can be depicted in a “precedence graph”. • The nodes in this graph are the transactions of the schedule S. • Example of a precedence graph: • Consider a schedule S which involves three transactions T1, T2 and T3, i.e., S: r2(A); r1(B); w2(A); r3(A); w1(B); w3(A); r2(B); w2(B); The precedence graph for this as is shown below: 1 2 3 Figure 1

42. Test for conflict-serializability • Construct the precedence graph for S and observe if there are any cycles. • If yes, then S is not conflict-serializable • Else, it is a conflict-serializable schedule. • Example of a cyclic precedence graph: • Consider the below schedule S1: r2(A); r1(B); w2(A); r2(B); r3(A); w1(B); w3(A); w2(B); The precedence graph for this as shown below: 1 2 3 Figure 2

43. continued… • Observing the actions of A in the previous example (figure 2), we can find that T2 <s1 T3. • But when we observe B, we get both T1 <s1 T2 and T2 <s1 T1. Thus the graph has a cycle between 1 and 2. So, based on this fact we can conclude that S1 is not conflict-serializable.

44. Why the Precedence-Graph test works • A cycle in the graph puts too many constraints on the order of transactions in a hypothetical conflict-equivalent serial schedule. • If there is a cycle involving n transactions T1 T2 ..Tn T1 • Then in the hypothetical serial order, the actions of T1 must precede those of T2 which would precede those of T3... up to n. • But actions of Tn are also required to precede those of T1. • So, if there is a cycle in the graph, then we can conclude that the schedule is not conflict-serializable.

45. Concurrency Control By Donavon Norwood Ankit Patel Aniket Mulye

46. INTRODUCTION • Enforcing serializability by locks • Locks • Locking scheduler • Two phase locking • Locking systems with several lock modes • Shared and exclusive locks • Compatibility matrices • Upgrading/updating locks • Incrementing locks

47. Locks • It works like as follows : • A request from transaction • Scheduler checks in the lock table • Generates a serializable schedule of actions.

48. Consistency of transactions • Actions and locks must relate each other • Transactions can only read & write only if has a lock and has not released the lock. • Unlocking an element is compulsory. • Legality of schedules • No two transactions can aquire the lock on same element without the prior one releasing it.

49. Locking scheduler • Grants lock requests only if it is in a legal schedule. • Lock table stores the information about current locks on the elements.

50. The locking scheduler (contd.) • A legal schedule of consistent transactions but unfortunately it is not a serializable.