CSC 453 Database Systems Lecture Tanu Malik College

CSC 453 Database Systems Lecture Tanu Malik College of CDM De. Paul University

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

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

Transactions Concurrent Access: Attribute-level Inconsistency Select enrollment from College Where c. Name = ‘De. Paul’ Update College Set enrollment = enrollment + 1000 Where c. Name = ‘De. Paul’ concurrent with … C 2 Select enrollment from College Where c. Name = ‘De. Paul’ Update College Set enrollment = enrollment + 1500 Where c. Name = ‘De. Paul’

Transactions: Flights Example Flights(flt. No, flt. Date, seat. No, seat. Status) select seat. No from Flights Where flt. No= 123 and flt. Date = ‘ 2008 -1225’ and seat. Status = ‘available’; Update Flights set seat. Status = ‘occuped’; Where flt. No= 123 and flt. Date = ‘ 2008 -12 -25’ and seat. No = ’ 22 A’; concurrent with … C 2 select * select seat. No from Flights Where flt. No= 123 and flt. Date = ‘ 2008 -12 -25’ and seat. Status = ‘available’; Update Flights set seat. Status = ‘occuped’; Where flt. No= 123 and flt. Date = ‘ 2008 -12 -25’ and seat. No = ’ 22 A’; Both modifying the apply record for the student id = 123

Transactions Concurrent Access: Tuple-level Inconsistency select * from Apply Where s. ID = 123 Update Apply Set major = ‘CS’ Where s. ID = 123 concurrent with … C 2 select * from Apply Where s. ID = 123 Update Apply Set decision = ‘Y’ Where s. ID = 123 Both modifying the apply record for the student id = 123

Transactions Concurrent Access: Table-level Inconsistency Update Apply Set decision = ‘Y’ Where s. ID In (Select s. ID From Student Where GPA > 3. 9) concurrent with … Update Student Set GPA = (1. 1) GPA Where size. HS > 2500

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;

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 (multiprocessor/multi-threaded/asynchronous I/O) environments. Clients may work on different parts of the DBMS

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

Transactions Resilience to System Failures Bulk Load DBMS Data

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

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

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

Transactions Solution for both concurrency and failures Transactions 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 Solution for both concurrency and failures Transactions 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 Solution for both concurrency and failures Transactions 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 Every time a DBMS encounters a transaction, the DBMS software guarantees the following ACID Properties Meaning A Atomicity all-or-nothing Order 3 C Consistency consistent DB state 4 I Isolation appear to act in isolation 1 D Durability commits are persistent 2

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

SQL Statements Concurrent Access: Attribute-level Inconsistency Select enrollment from College Where c. Name = ‘De. Paul’ Update College Set enrollment = enrollment + 1000 Where c. Name = ‘De. Paul’ concurrent with … Select enrollment from College Where c. Name = ‘De. Paul’ Update College Set enrollment = enrollment + 1500 Where c. Name = ‘De. Paul’

Transactions: Internal Representation of SQL statements READ(enrollment, c. Name) WRITE(enrollment) concurrent with … C 2 READ(enrollment, c. Name) WRITE(enrollment)

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

Transactions: Durability Begin Transaction READ(enrollment, c. Name) enrollment gets updated here WRITE(enrollment) Commit concurrent with … C 2 Begin Transaction READ(enrollment, c. Name) WRITE(enrollment) Commit

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

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

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

Transactions Transaction Rollback (= Abort) • • Each transaction is Undoes partial effects of transaction “all-or-nothing, ” never left half done Can be system- or client-initiated Begin Transaction; <get input from user> SQL commands based on input <confirm results with user> If ans=‘ok’ Then Commit; Else Rollback;

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

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.

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

• • Schedule 1 Let T 1 transfer $50 from A to B, and T 2 transfer 10% of the balance from A to B. An example of a serial schedule in which T 1 is followed by T 2 :

• Schedule 2 A serial schedule in which T 2 is followed by T 1 :

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

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

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

Transactions Set Transaction Isolation Level Read Uncommitted Begin Transaction Select GPA from Student Where size. HS > 2500 Update Student Set GPA = (1. 1) GPA Where size. HS > 2500 Commit concurrent with … Set Transaction Isolation Level Repeatable Read Begin Transaction Select Avg(GPA) From Student Commit

Transactions Isolation Levels § Per transaction § “In the eye of the beholder” § Affect applies to read statements My transaction is . . . My transaction is Read Uncommitted Repeatable Read DBMS Data

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

Transactions Dirty Reads “Dirty” data item: written by an uncommitted transaction Update Student Set GPA = (1. 1) GPA Where size. HS > 2500 concurrent with … Select GPA From Student Where s. ID = 123 concurrent with … Update Student Set size. HS = 2600 Where s. ID = 234

Transactions Isolation Level Read Uncommitted • A transaction may perform dirty reads Select GPA from Student Where size. HS > 2500 Update Student Set GPA = (1. 1) GPA Where size. HS > 2500 concurrent with … Set Transaction Isolation Level Read Uncommitted; Select Avg(GPA) From Student;

Transactions Isolation Level Read Committed • A transaction will not perform dirty reads • Only reads commited values of other transactions Still does not guarantee global serializability Select GPA From Student Where size. HS > 2500 Update Student Set GPA = (1. 1) GPA Where size. HS > 2500 concurrent with … Set Transaction Isolation Level Read Committed; Select Avg(GPA) From Student; Select Max(GPA) From Student;

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 Update Student Set GPA = (1. 1) GPA; Update Student Set size. HS = 1500 Where s. ID = 123; concurrent with … Set Transaction Isolation Level Repeatable Read; Select Avg(GPA) From Student; Select Avg(size. HS) From Student;

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 Into Student [ 100 new tuples ] concurrent with … Set Transaction Isolation Level Repeatable Read; Select Avg(GPA) From Student; Select Max(GPA) From Student;

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 From Student [ 100 tuples ] concurrent with … Set Transaction Isolation Level Repeatable Read; Select Avg(GPA) From Student; Select Max(GPA) From Student;

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

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?

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.

Transactions Isolation Levels: Summary dirty reads Read Uncommitted Read Committed Repeatable Read Serializable nonrepeatable phantoms reads

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

Example • Consider a relation R(A) containing {(5), (6)} and two transactions: – T 1: Update R set A = A+1; – T 2: 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)

Example • Consider a table R(A) containing {(1), (2)} and two transactions: – T 1: Update R set A = 2*A; – T 2: Select avg(A) from R. • If transaction T 2 executes using "read uncommitted", what are the possible values it returns?

Example • Consider tables R(A) and S(B), both containing {(1), (2)}. – T 1: Update R set A = 2*A; update S set B = 2*B – T 2: Select avg(A) from R; select avg(B) from S. • If transaction T 2 executes using "read committed", is it possible for T 2 to return two different values?

Example 5 • Consider table R(A) containing {(1), (2)}. – T 1: Update T set A=2*A; insert into R values (6); – T 2: Select avg(A) from R; select avg(A) from R; • If transaction T 2 executes using "repeatable read", what are the possible values returned by its SECOND statement?