CSC 453 Database Systems Lecture

1. CSC 453 Database SystemsLecture Tanu Malik College of CDM DePaul University

2. Transactions Motivated by two independent requirements • Concurrent database access • Resilience to system failures

3. Transactions Concurrent Database Access Even more software Select… Update… Create Table… Drop Index… Help… Delete… More software DBMS Data

4. Transactions Concurrent Access: Attribute-level Inconsistency Select enrollment from CollegeWhere cName=‘DePaul’ Update CollegeSet enrollment=enrollment+1000 Where cName=‘DePaul’ concurrent with … C2 Select enrollment from CollegeWhere cName=‘DePaul’ Update CollegeSet enrollment=enrollment+1500 Where cName=‘DePaul’

5. Transactions: Flights Example Flights(fltNo,fltDate,seatNo,seatStatus) select seatNo from Flights Where fltNo=123 and fltDate = ‘2008-12-25’ and seatStatus = ‘available’; Update Flights set seatStatus = ‘occuped’; Where fltNo=123 and fltDate = ‘2008-12-25’ and seatNo = ’22A’; concurrent with … C2 select * select seatNo from Flights Where fltNo=123 and fltDate = ‘2008-12-25’ and seatStatus = ‘available’; Update Flights set seatStatus = ‘occuped’; Where fltNo=123 and fltDate = ‘2008-12-25’ and seatNo = ’22A’; Both modifying the apply record for the student id = 123

6. Transactions Concurrent Access: Tuple-level Inconsistency select * from ApplyWhere sID=123 UpdateApplySetmajor=‘CS’Where sID=123 concurrent with … C2 select * from ApplyWhere sID=123 UpdateApplySetdecision=‘Y’Where sID=123 Both modifying the apply record for the student id = 123

7. Transactions Concurrent Access: Table-level Inconsistency Update ApplySet decision=‘Y’ Where sID In (Select sID From Student Where GPA>3.9) concurrent with … UpdateStudentSetGPA=(1.1)GPAWhere sizeHS>2500

8. Transactions Concurrent Access: Multi-statement inconsistency Insert Into Archive Select * From Apply Where decision=‘N’; Delete From Apply Where decision=‘N’; concurrent with … Select Count(*)From Apply; Select Count(*) From Archive;

9. Transactions Concurrency Goal Execute sequence of SQL statements so they appear to be running as a group or “in isolation” • Simple solution: execute them in isolation But want to enable concurrency whenever safe to do so Database systems are geared toward performance. They typically operate in concurrent (multi-processor/multi-threaded/asynchronous I/O) environments. Clients may work on different parts of the DBMS

10. Transactions System Failure Select… Update… Create Table… Drop Index… Help… Delete… More software DBMS Data

11. Transactions Resilience to System Failures Bulk Load DBMS Data

12. Transactions Resilience to System Failures Insert Into Archive Select * From Apply Where decision=‘N’; Delete From Apply Where decision=‘N’; DBMS Data

13. Transactions Resilience to System Failures Lots of updates buffered in memory DBMS Data

14. Transactions System-Failure Goal Guarantee all-or-nothing execution, regardless of failures DBMS Data

15. Transactions Solution for both concurrency and failures A transaction is a sequence of one or more SQL operations treated as a unit • Transactions appear to run in isolation • If the system fails, each transaction’s changes are reflected either entirely or not at all Transactions

16. Transactions Solution for both concurrency and failures A transaction is a sequence of one or more SQL operations treated as a unit. SQL standard: • Transaction begins with a Begin Transaction statement • On “commit” transaction ends and new one begins • Current transaction ends on session termination • “Autocommit” turns each statement into transaction Transactions

17. Transactions Solution for both concurrency and failures A transaction is a sequence of one or more SQL operations treated as a unit • Transactions appear to run in isolation • If the system fails, each transaction’s changes are reflected either entirely or not at all Transactions

18. Transactions Every time a DBMS encounters a transaction, the DBMS software guarantees the following ACID Properties Meaning Order A Atomicity all-or-nothing 3 C Consistency consistent DB state 4 I Isolation appear to act in isolation 1 D Durability commits are persistent 2

19. Internal Representation of SQL statements Create Table X ( x int, y int , check (y = x) ) Select x from X Read(x) Select * from X Read(x);Read(Y) Insert into X values (5,5) Write(x);Write(y) Insert into X values (6,6) Write(x);Write(y) Update X set x = x*2 Read(x);Write(x) Update X set y = y*2 Read(y);Write(y)

20. SQL Statements Concurrent Access: Attribute-level Inconsistency Select enrollment from College Where cName=‘DePaul’ Update College Set enrollment=enrollment+1000 Where cName=‘DePaul’ concurrent with … Select enrollment from College Where cName=‘DePaul’ Update College Set enrollment=enrollment+1500 Where cName=‘DePaul’

21. Transactions: Internal Representation of SQL statements READ(enrollment, cName) WRITE(enrollment) concurrent with … C2 READ(enrollment, cName) WRITE(enrollment)

22. Read/Write • Read implies read into memory from disk • Subsequent reads (across all transactions) are from memory • Write implies write into memory (across all txns) • Often an attribute is written from a temporary variable • Temporary variables are visible only within a transaction • Commit implies flush to disk

23. Transactions: Durability Begin Transaction READ(enrollment, cName) enrollment gets updated here WRITE(enrollment) Commit concurrent with … C2 Begin Transaction READ(enrollment, cName) WRITE(enrollment) Commit

24. Transactions: Atomicity (all or nothing) Begin Transaction READ(enrollment, cName) enrollment gets updated here WRITE(enrollment) Commit

25. Transactions: Consistency Create Table X ( x int, y int , check (y = x) ) Insert into X values (5,5) W(x);W(y) Insert into X values (6,6) W(x);W(y) Select x from X R(x) Select * from X R(x);R(Y) Update X set x = x*2 R(x);W(x) Update X set y = y*2 R(y);W(y)

26. Transactions: Consistency Create Table X ( x int, y int , check (y >= x) ) Insert into X values (5,5); W(x);W(y) Insert into X values (6,6); W(x);W(y) Select x from X R(x) Select * from X R(x);R(Y) Update X set y = x+y; Update X set x = x+y;

27. Transactions Transaction Rollback (= Abort) • Undoes partial effects of transaction • Can be system- or client-initiated Each transaction is “all-or-nothing,” never left half done BeginTransaction; <getinputfromuser> SQLcommandsbasedoninput <confirmresultswithuser> Ifans=‘ok’ThenCommit;ElseRollback;

28. Transactions (ACID Properties) Isolation Serializability Operations may be interleaved, but execution must be equivalent to some sequential (serial) order of all transactions . . . DBMS Data

29. Serializability • Basic Assumption– Each transaction preserves database consistency. • Thus, serial execution of a set of transactions preserves database consistency. • A (possibly concurrent) schedule is serializable if it is equivalent to a serial schedule.

30. Schedules • Schedules– a sequences of instructions that specify the chronological order in which instructions of concurrent transactions are executed • A schedule for a set of transactions must consist of all instructions of those transactions • Must preserve the order in which the instructions appear in each individual transaction. • A transaction that successfully completes its execution will have a commit instructions as the last statement • A transaction that fails to successfully complete its execution will have an abort instruction as the last statement

31. Schedule 1 • Let T1 transfer $50 from A to B, and T2 transfer 10% of the balance from A to B. • An example of a serialschedule in which T1 is followed by T2 :

32. Schedule 2 • A serialschedule in which T2 is followed by T1 :

33. Schedule 3 • Let T1 and T2 be the transactions defined previously. The following schedule is not a serial schedule, but it is equivalentto Schedule 1. commit Note -- In schedules 1, 2 and 3, the sum “A + B” is preserved.

34. Schedule 4 • The following concurrent schedule does not preserve the sum of “A + B” commit

35. Transactions (ACID Properties) Isolation Weaker “Isolation Levels” Read Uncommitted Read Committed Repeatable Read . . . Strongest “Isolation Levels” Serilizable order DBMS  Overhead  Concurrency Data  Consistency Guarantees

36. Transactions Set Transaction Isolation Level ReadUncommitted Begin Transaction Select GPAfrom StudentWhere sizeHS>2500 UpdateStudentSetGPA=(1.1)GPAWhere sizeHS>2500 Commit concurrent with … Set Transaction Isolation Level Repeatable Read Begin Transaction Select Avg(GPA) FromStudent Commit

37. Transactions Isolation Levels • Per transaction • “In the eye of the beholder” • Affect applies to read statements . . . My transaction is ReadUncommitted My transaction is RepeatableRead DBMS Data

38. Transactions Dirty Reads “Dirty” data item: written by an uncommitted transaction Select enrollment from College Where cName=‘DePaul’ Update CollegeSet enrollment=enrollment+1000 Where cName=‘DePaul’ concurrent with … Enrollment is dirty over here. It is updated by the update stmt, but not committed by the txn. Select Avg(enrollment) From College

39. Transactions Dirty Reads “Dirty” data item: written by an uncommitted transaction UpdateStudentSetGPA=(1.1)GPAWhere sizeHS>2500 concurrent with … Select GPA FromStudentWheresID=123 concurrent with … Update Student Set sizeHS=2600 Where sID=234

40. Transactions Isolation Level ReadUncommitted • A transaction may perform dirty reads Select GPAfrom StudentWhere sizeHS>2500 UpdateStudentSetGPA=(1.1)GPAWhere sizeHS>2500 concurrent with … Set Transaction Isolation Level Read Uncommitted; Select Avg(GPA) FromStudent;

41. Transactions Isolation Level ReadCommitted • A transaction will not perform dirty reads • Only reads commited values of other transactions Still does not guarantee global serializability Select GPAFrom StudentWhere sizeHS>2500 UpdateStudentSetGPA=(1.1)GPAWhere sizeHS>2500 concurrent with … Set Transaction Isolation Level Read Committed; Select Avg(GPA) FromStudent; Select Max(GPA) From Student;

42. Transactions Isolation Level Repeatable Read • A transaction may not perform dirty reads • An item read multiple times cannot change value Still does not guarantee global serializability UpdateStudentSetGPA=(1.1)GPA; Update Student Set sizeHS=1500 Where sID=123; concurrent with … Set Transaction Isolation Level Repeatable Read; Select Avg(GPA) FromStudent; Select Avg(sizeHS) From Student;

43. Transactions Isolation Level Repeatable Read • A transaction may not perform dirty reads • An item read multiple times cannot change value But a relation can change: “phantom” tuples Insert IntoStudent[100newtuples] concurrent with … Set Transaction Isolation Level Repeatable Read; Select Avg(GPA) FromStudent; Select Max(GPA) From Student;

44. Transactions Isolation Level Repeatable Read • A transaction may not perform dirty reads • An item read multiple times cannot change value But a relation can change: “phantom” tuples Delete FromStudent[100tuples] concurrent with … Set Transaction Isolation Level Repeatable Read; Select Avg(GPA) FromStudent; Select Max(GPA) From Student;

45. Transactions Read Only transactions • Helps system optimize performance • Independent of isolation level Set Transaction Read Only; Set Transaction Isolation Level Repeatable Read; Select Avg(GPA) FromStudent; Select Max(GPA) From Student;

46. Transactions • Serializable • Strongest isolation level • SQL Default • Read Uncommitted • A data item is dirty if it is written by an uncommitted transaction. • Problem of reading dirty data written by another uncommitted transaction: what if that transaction eventually aborts?

47. Transactions • Read Committed • Cannot read dirty data written by other uncommitted transactions. • But read-committed is still not necessarily serializable • Repeatable Read • If a tuple is read once, then the same tuple must be retrieved again if query is repeated. • Still not serilizable; may see phantom tuples—tuples inserted by other concurrent transactions.

48. Transactions Isolation Levels: Summary

49. Transactions Isolation Levels: Summary • Standard default: Serializable • Weaker isolation levels • Increased concurrency + decreased overhead = increased performance • Weaker consistency guarantees • Some systems have default RepeatableRead • Isolation level per transaction and “eye of the beholder” • Each transaction’s reads must conform to its isolation level

50. Example • Consider a relation R(A) containing {(5),(6)} and two transactions: • T1: Update R set A = A+1; • T2: Update R set A = 2*A. • Suppose both transactions are submitted under the isolation and atomicity properties. Which of the following is NOT a possible final state of R? • (10,12);(11,13);(11,12);(12,14)