Transaction Processing

1. Transaction Processing

2. Transactions • Many enterprises use databases to store information about their state • e.g., Balances of all depositors at a bank • When an event occurs in the real world that changes the state of the enterprise, a program is executed to change the database state in a corresponding way • e.g., Bank balance must be updated when deposit is made • Such a program is called a transaction

3. What Does a Transaction Do? • Update the database to reflect the occurrence of a real world event • Deposit transaction: Update customer’s balance in database • Cause the occurrence of a real world event • Withdraw transaction: Dispense cash (and update customer’s balance in database) • Return information from the database • RequestBalance transaction: Outputs customer’s balance

4. Transactions • The execution of each transaction must maintain the relationship between the database state and the enterprise state • Therefore additional requirements are placed on the execution of transactions beyond those placed on ordinary programs: • Atomicity • Consistency • Isolation • Durability ACID properties

5. ACID Properties • Atomic - Transaction should either complete or have no effect at all • Responsibility of transaction processing system • Consistent - Transaction should correctly transform the database state to reflect the effect of a real world event • Responsibility of transaction designer • Isolation - The effect of concurrently executing a set of transactions is the same as if they had executed serially (serializable) • Responsibility of transaction processing system • Durable - The effect of a transaction on the database state should not be lost once the transaction has committed • Responsibility of transaction processing system

6. Database Consistency • Enterprise (Business) Rules limit the occurrence of certain real-world events • Student cannot register for a course if the current number of registrants equals the maximum allowed • These limitations are (static) integrity constraints: assertions that must be satisfied by the database state • Database is consistent if all static integrity constraints are satisfied

7. Transaction Consistency • A consistent database state does not necessarily model the actual state of the enterprise • A deposit transaction that increments the balance by the wrong amount maintains the integrity constraint balance  0, but does not maintain the relation between the enterprise and database states • A consistent transaction maintains database consistency and the correspondence between the database state and the enterprise state (implements its specification) • Specification of deposit transaction includes balance = balance + amt_deposit , (balance is the initial value of balance)

8. Atomicity • A real-world event either happens or does not happen • Student either registers or does not register • Similarly, the system must ensure that either the corresponding transaction runs to completion or, if not, it has no effect at all • Not true of ordinary programs. A crash could leave files partially updated on recovery

9. Commit and Abort • If the transaction successfully completes it is said to commit • The system is responsible for preserving the transaction’s results in spite of subsequent failures • If the transaction does not successfully complete, it is said to abort • The system is responsible for undoing, or rolling back, any changes the transaction has made

10. Reasons for Abort • System crash • Transaction aborted by system • Execution cannot be made atomic (a site is down) • Execution did not maintain database consistency (integrity constraint is violated) • Execution was not isolated • Resources not available (deadlock) • Transaction requests to roll back

11. API for Transactions • DBMS provide commands for setting transaction boundaries. For example: • begin transaction • commit • rollback • The commit command is a request • The system might commit the transaction, or it might abort it for one of the reasons on the previous slide • The rollback command is always satisfied

12. Durability • Durability deals with failure: • Media failure • The system must ensure that once a transaction commits, its effect on the database state is not lost in spite of subsequent failures • Not true of ordinary programs. A media failure after a program successfully terminates could cause the file system to be restored to a state that preceded the program’s execution • Mechanism for dealing with failures is the log

13. Isolation • Serial Execution: The transactions execute one after the other • Each one starts after the previous one completes. • The execution of each transaction is isolated from all others. • Serial execution is inadequate from a performance perspective • Concurrent execution offers performance benefits: • A computer system has multiple resources capable of executing independently (e.g., cpu’s, I/O devices), • only concurrently executing transactions can make effective use of the system

14. Concurrent Execution

15. Isolation • Concurrent (interleaved) execution of a set of consistent transactions offers performance benefits, but might not be correct • Example: course registration; cur_reg is number of current registrants T1: r(cur_reg : 29) w(cur_reg : 30) T2: r(cur_reg : 29) w(cur_reg : 30) time  Result: Database state no longer corresponds to real-world state, integrity constraint violated (cur_reg <> |list_of_registered_students|)

16. Transaction Schedule T1: begin_transaction(); …. p1,1; …. p1,2; …. p1,3; commit(); Transaction schedule p1,3 p1,2 p1,1 • Consistent - performs correctly when executed in isolation starting in a consistent database state • Preserves database consistency • Moves database to a new state that corresponds to new real-world state To db server local variables

17. Schedule Arriving schedule (merge of transaction schedules) Schedule in which requests are serviced T1 T2 T3 Concurrency Control To database transaction schedules Database server

18. Example Schedules • Let T1 transfer $50 from A to B, and T2 transfer 10% of the balance from A to B. The following is a serial schedule, in which T1 is followed by T2. Schedule 1

19. Example Schedule (Cont.) • Let T1 and T2 be the transactions defined previously. The following schedule is not a serial schedule, but it is equivalent to the previous Schedule. Schedule 2 In both Schedule 1 and 2, the sum A + B is preserved.

20. Example Schedules (Cont.) • The following concurrent schedule does not preserve the value of the sum A + B. Schedule 3

21. Correct Schedules • Interleaved schedules equivalent to serial schedules are the only ones guaranteed to be correct for all applications • Equivalence based on commutativity of operations • Definition: Database operations p1and p2commute if, for all initial database states, they return the same results and leave the database in the same final state when executed in either order.

22. Commutativity of Read and Write Operations • p1commutes with p2 if • They operate on different data items • w1(x) commutes with w2(y) and r2(y) • Both are reads • r1(x) commutes with r2(x) • Operations that do not commute conflict • w1(x) conflicts with w2(x) • w1(x) conflicts with r2(x)

23. Equivalence of Schedules • An interchange of adjacent operations of different transactions in a schedule creates an equivalent schedule if the operations commute S1 : S1,1, pi,j, pk,l, S1,2 where i < > k S2 : S1,1, pk,l, pi,j, S1,2 • Equivalence is transitive: If S1 can be derived from S2 by a series of such interchanges, S1 is equivalent to S2

24. Example of Equivalence conflict S1: r1(x) r2(x) w2(x) r1(y) w1(y) S2: r1(x) r2(x) r1(y) w2(x) w1(y) S3: r1(x) r1(y) r2(x) w2(x) w1(y) S4: r1(x) r1(y) r2(x) w1(y) w2(x) S5: r1(x) r1(y) w1(y) r2(x) w2(x) conflicting operations ordered in same way S1 is equivalent to S5 S5 is the serial schedule T1, T2 S1 is serializable S1 is not equivalent to the serial schedule T2, T1

25. Example of Equivalence T1: begin transaction read (x, X); X = X+4; write (x, X); commit; T2: begin transaction read (x,Y); write (y,Y); commit; r1(x)r2(x) w2(y) w1(x) x=1, y=3 x=5, y=1 x=5, y=1 r2(x) w2(y) r1(x)w1(x) T2 T1 Interchange commuting operations r1(x)r2(x) w2(y) w1(x) x=1, y=3 x=5, y=1 x=5, y=5 r1(x) w1(x) r2(x)w2(y) T1 T2 Interchange conflicting operations

26. Serializable Schedules • S is serializable if it is equivalent to a serial schedule • Transactions are totally isolated in a serializable schedule • A schedule is correct for any application if it is a serializable schedule of consistent transactions • The schedule : r1(x) r2(y) w2(x) w1(y) is not serializable

27. Serializability • Different forms of schedule equivalence give rise to the notions of: 1. Conflict serializability 2. View serializability

28. Conflict Serializability • Two schedules are conflict equivalentif: • they have the same sets of actions, and • each pair of conflicting actions is ordered in the same way. • A schedule is conflict serializableif it is conflict equivalent to a serial schedule. • Note: Some serializable schedules are not conflict serializable!

29. Conflict Serializability (Cont.) • Schedule 2 below can be transformed into Schedule 1, a serial schedule where T2 follows T1, by series of swaps of non-conflicting instructions. Therefore Schedule 2 is conflict serializable.

30. Conflict Serializability (Cont.) • Example of a schedule that is not conflict serializable: T3T4read(Q)write(Q)write(Q)We are unable to swap instructions in the above schedule to obtain either the serial schedule < T3, T4 >, or the serial schedule < T4, T3 >.

31. T1 T2 Precedence Graph • A Precedence (or Serializability) graph: • Node for each Xact. • Arc from Ti to Tj if an action of Ti precedes and conflicts with an action of Tj. • T1 transfers $100 from A to B, T2 adds 6% • R1(A), W1(A), R2(A), W2(A), R2(B), W2(B), R1(B), W1(B)

32. Precedence Graph of a Schedule, S • Theorem - A schedule is conflict serializable if and only if its precedence graph has no cycles • If precedence graph is acyclic, the serializability order can be obtained by a topological sorting of the graph. This is a linear order consistent with the partial order of the graph.

33. Example Conflict (*) S: … p1,i, …, p2,j, ... T2 T4 S is serializable in order T1 T2 T3 T4 T5 T6 T7 * T1 T5 T6 T7 T3 S is not serializable due to cycle T2 T6 T7 T2 T2 T4 T1 T5 T6 T7 T3

34. Example Schedule (Schedule A) T1 T2 T3 T4 T5 read(X)read(Y)read(Z) read(V) read(W) read(W) read(Y) write(Y) write(Z)read(U) read(Y) write(Y) read(Z) write(Z) read(U)write(U)

35. Precedence Graph for Schedule A T1 T2 T4 T3 A serializability order for Schedule A would beT5 T1 T3 T2 T4 .

36. Concurrency Control vs. Serializability Tests • Testing a schedule for serializability after it has executed is a little too late! • Goal – to develop concurrency control protocols that will assure serializability. They will generally not examine the precedence graph as it is being created; instead a protocol will impose a discipline that avoids nonseralizable schedules. • Tests for serializability help understand why a concurrency control protocol is correct.

37. Concurrency Control Serializable schedule Arriving schedule Concurrency Control • Concurrency control cannot see entire schedule: • It sees one request at a time and must decide whether to allow it to be serviced • Strategy: Do not service a request if: • It violates strictness or serializability, or • There is a possibility that a subsequent arrival might cause a violation of serializability (from transactions) (to processing engine)

38. Locking: A Technique for C. C. • A transaction can read a database item if it holds a read (shared) lock on the item • It can read or update the item if it holds a write (exclusive) lock • If the transaction does not already hold the required lock, a lock request is automatically made as part of the access

39. S X -- Ö Ö Ö -- S Ö Ö X Ö Locking • Concurrency control usually done via locking. • Lock info maintained by a “lock manager”: • Stores (XID, RID, Mode) triples. • This is a simplistic view; suffices for now. • Mode Î {S,X} • Lock compatibility table: • If a Xact can’t get a lock, it is suspended on a wait queue.

40. lock point # of locks shrinking phase growing phase Time Two-Phase Locking (2PL) • 2PL: • If T wants to read an object, first obtains an S lock. • If T wants to modify an object, first obtains X lock. • If T releases any lock, it can acquire no new locks! • Locks are automatically obtained by DBMS. • Guarantees serializability! • Why?

41. # of locks Time Strict 2PL • Strict 2PL: • If T wants to read an object, first obtains an S lock. • If T wants to modify an object, first obtains X lock. • Hold all locks until end of transaction. • Guarantees serializability, and recoverable schedule, too!

42. Lock Management • Lock and unlock requests are handled by the lock manager • Lock table entry: • Number of transactions currently holding a lock • Type of lock held (shared or exclusive) • Pointer to queue of lock requests • Locking and unlocking have to be atomic operations • Lock upgrade: transaction that holds a shared lock can be upgraded to hold an exclusive lock

43. Lock Manager Implementation • Question 1: What are we locking? • Tuples, pages, or tables? • Finer granularity increases concurrency, but also increases locking overhead. • Question 2: How do you “lock” something?? • Lock Table: A hash table of Lock Entries. • Lock Entry: • OID • Mode • List: Xacts holding lock • List: Wait Queue

44. Handling a Lock Request Lock Request (XID, OID, Mode) Mode==S Mode==X Currently Locked? Empty Wait Queue? Yes No Yes Currently X-locked? Yes No Put on Queue No Grant Lock

45. More Lock Manager Logic • On lock release (OID, XID): • Update list of Xacts holding lock. • Examine head of wait queue. • If Xact there can run, add it to list of Xacts holding lock (change mode as needed). • Repeat until head of wait queue cannot be run. • Note: Lock request handled atomically! • via latches (i.e. semaphores/mutex; OS stuff).

46. Lock Upgrades • Think about this scenario: • T1 locks A in S mode, T2 requests X lock on A, T3 requests S lock on A. What should we do? • In contrast: • T1 locks A in S mode, T2 requests X lock on A, T1 requests X lock on A. What should we do? • Allow such upgrades to supersede lock requests. • Consider this scenario: • S1(A), X2(A), X1(A): DEADLOCK! • BTW: Deadlock can occur even w/o upgrades: • X1(A), X2(B), S1(B), S2(A)

47. Deadlocks • Deadlock: Cycle of transactions waiting for locks to be released by each other. • Two ways of dealing with deadlocks: • Deadlock prevention • Deadlock detection

48. Deadlock Prevention X1(A), X2(B), S1(B), S2(A) • Assign a timestamp to each Xact as it enters the system. “Older” Xacts have priority. • Assume Ti requests a lock, but Tj holds a conflicting lock. • Wait-Die: If Ti has higher priority, it waits; else Ti aborts. (non-preemptive) • Wound-Wait: If Ti has higher priority, abort Tj; else Ti waits. (preemptive) • Note: After abort, restart with original timestamp! • Both guarantee deadlock-free behavior! Pros and cons of each?

49. Deadlock Detection • Create a waits-for graph: • Nodes are transactions • There is an edge from Ti to Tj if Ti is waiting for Tj to release a lock • Periodically check for cycles in the waits-for graph. • “Shoot” some Xact to break the cycle. • Simpler hack: time-outs. • T1 made no progress for a while? Shoot it.

50. Deadlock Detection (Continued) Example: T1: S(A), R(A), S(B) T2: X(B),W(B) X(C) T3: S(C), R(C) X(A) T4: X(B) T1 T2 T1 T2 T4 T3 T3 T3