Transactions

1. Transactions

2. Objectives • Transaction Concept • Transaction State • Concurrent Executions • Serializability • Recoverability • Implementation of Isolation • Transaction Definition in SQL • Testing for serializability

3. Concept of Transaction • It is a unit of program execution which accesses and possibly going to updates different data items • Example of transaction that transfer $50 from account A to account B 1. read(A) 2. A := A – 50 3. write(A) 4. read(B) 5. B := B + 50 6. write(B)

4. Concept of Transaction (cont.) • Most Important issues to deal with: • Failure in different types, like hardware failure and system crashes • Concurrent Execution of multiple transaction

5. Example • Transfer $50 from A to B accounts 1. read(A) 2. A := A – 50 3. write(A) 4. read(B) 5. B := B + 50 6. write(B) • Atomicity requirement • Partial executed transaction must not affect DB • Durability Requirement • If transaction is finished DB should be update regardless of any failure afterwards.

6. Requirements • Consistency requirement • Explicitly specified integrity constraints such as primary keys and foreign keys • Implicit integrity constraints • A transaction must see a consistent database • During transaction execution the database may be temporarily inconsistent • When the transaction completes successfully the database must be consistent

7. Requirements (Cont.) • Isolation requirement if between steps 3 and 6, another transaction T2 is allowed to access the partially updated database, it will see an inconsistent database (the sum A + B will be less than it should be).T1 T2 1. read(A) 2. A := A – 50 3. write(A) read(A), read(B), print(A+B) 4. read(B) 5. B := B + 50 • write(B Isolation ensured trivially by running serially(one after another)

8. ACID Properties • Atomicity All transactions properly reflect DB • Consistency Execution with isolation preserves consistency • Isolation All multiple transactions may execute concurrently must be unaware of each others. • Durability After transaction completes , changes to DB must be persisted regardless of failures.

9. Transaction State • Active the initial state • Partially committed after final statement is done • Failed normal execution can no longer proceed • Aborted after DB rolled back to prior state to start of transaction • Committed successful completion

10. Transaction State (Cont.)

11. Concurrent Executions • Multiple transaction could run together • Advantages: • Increased processor and disk utilization (Better throughput) • Reduced average response time (good for short transactions)

12. Concurrent Executions • Concurrency control schemes mechanisms to achieve isolation • Control the interaction among the concurrent transaction in order to prevent them from destroying the consistency of the database

13. Schedules • Sequence of instructions that emphasize chronological order in concurrent transactions • Must consist all instructions on transaction • Must preserve the order of instruction • A finished transaction must have a commit instruction • If it fail it must be aborted

14. Schedule 1 • Let T1 transfer $50 from A to B and T2 transfer 10% of the balance from A to B • A serial schedule in which T1 is followed by T2

15. Schedule 2 • A serial schedule where T2 is followed by T1

16. Schedule 3 • Equivalent to schedule 1 • In schedule 1,2 and 3 the sum A+B is preserved

17. Schedule 4 • The following concurrent schedule does not preserve the value of (A+B)

18. Serializability • Basic Assumption Each transaction preserves DB consistency. • So serial execution of set of transaction preserves DB consistency. • It is serializable if it is equivalent to serial schedule. • Conflict serializability • View serializability

19. Precise perspective of transactions • Ignore all except for read and write instructions. • We assume it performs on local buffer between reads and writes. • The simplified schedule contain merely read and write instruction.

20. Conflicting Instructions • Instructions li and lj of transactions Ti and Tj respectively, conflict if and only if there exists some item Q accessed by both li and lj, and at least one of these instructions wrote Q. 1. li = read(Q), lj = read(Q). li and ljdon’t conflict. 2. li = read(Q), lj = write(Q). They conflict. 3. li = write(Q), lj = read(Q). They conflict 4. li = write(Q), lj = write(Q). They conflict

21. Conflicting Instruction (Cont.) • Intuitively, a conflict between liand lj forces a (logical) temporal order between them. • If li and lj are consecutive in a schedule and they do not conflict, their results would remain the same even if they had been interchanged in the schedule.

22. Conflict Serializability • If S can be transformed into S` by series of non-conflicting instructions , we say this two are conflict equivalent • S is conflict serializable if it is conflict equivalent to serial schedule.

23. Conflict Serializability (Cont.) • A serial schedule where T2 follows T1, by series of swaps of non-conflicting instructions. Therefore Schedule 3 is conflict serializable. Schedule 3 Schedule 6

24. Example • Non conflict serializable • We are unable to swap instructions in the above schedule to obtain either the serial schedule < T3, T4 >, or the serial schedule < T4, T3 >.

25. View Serializability • S and S` are view equivalent in following conditions. • If both S and S` read initial value of Q • If S read(Q) then produce a value then S1 must read the value Q of the same write(Q) • The transaction that perform final write(Q)in S must also the final write(Q) in S`.

26. View Serializability (Cont.) • S is View Serializabilityif it is view conflict to a serial schedule. • Every Conflict serializable schedule is view serializable. • This is not view-serializable but not conflict serializable

27. View Serializability (Cont.) • What was the equivalent schedule to previous one • Every serializable that is not conflict serializable has Blind writes.

28. Other sort of Serializability • The schedule below produces same outcome as the serial schedule < T1,T5 >, yet is not conflict equivalent or view equivalent to it. Analysis of operations other than read and write.

29. Testing for Serilialzability • Assume schedule of T1,T2,….Tn • Precedence graph is a graph that its vertices are transactions. • We draw an arc from Tito Tjif the two transaction conflict, and Tiaccessed the data item on which the conflict arose earlier.

30. Test for Conflict Serializability • A schedule is conflict serializable if and only if its precedence graph is acyclic. • Cycle-detection algorithms exist which take order n2 time, where n is the number of vertices in the graph. • If precedence graph is acyclic, the serializability order can be obtained by a topological sorting of the graph.

31. Test for View Serializability • The precedence graph for conflict serializability cannot be used for view serializibility. • The problem of checking if a schedule is view serializable falls in the class of NP-complete problems. • However practical algorithms that just check some sufficientconditions for view serializability can still be used.

32. Recoverable Schedules • address the effect of transaction failures on concurrently running transactions. • The following schedule (Schedule 11) is not recoverable if T9commits immediately after the read

33. Cascading Rollbacks • a single transaction failure leads to a series of transaction rollbacks.

34. Cascadeless Schedules • When cascading rollbacks cannot occur.(the first operation commit’s appear before second one) • Every cascadeless schedule is also recoverable • It is desirable to restrict the schedules to those that are cascadeless

35. Concurrency Control • A DB must ensure all possible schedules • Conflict or view serializable • Recoverable and cascadeless • transaction can execute at a time generates serial schedules, but provides a poor degree of concurrency • Goal – to develop concurrency control protocols that will assure serializability.

36. Concurrency Control (Cont.) • Schedules must be conflict or view serializable, and recoverable, for consistency • tradeoff between the amount of concurrency they allow and the amount of overhead that they incur.

37. Weak Levels of Consistency • Some applications are willing to live with weak levels of consistency • read-only transaction that wants to get an approximate total balance of all accounts • database statistics computed for query optimization can be approximate • Such transactions need not be serializable with respect to other transactions • Tradeoff accuracy for performance

38. Levels of Consistency in SQL-92 • SerializableDefault • Repeatable read only committed records to be read, repeated reads of same record must return same value. • Read Committed only committed records can be read, but successive reads of record may return different (but committed) values • Read uncommitted even uncommitted records may be read.

39. Transaction Definition in SQL • DML must comprise set of actions that comprise transaction • In SQL, a transaction begins implicitly. • A transaction in SQL ends by: • Commit work (commit current and start new one) • Rollback work (causes current transaction to abort)

40. Transaction Definition in SQL • In almost all database systems, by default, every SQL statement also commits implicitly if it executes successfully • Implicit commit can be turned off by a database directive • E.g. in JDBC, connection.setAutoCommit(false);

41. Questions

42. Thank you for participation