CS 143 Transactions Professor Junghoo John Cho Motivation

CS 143: Transactions Professor Junghoo “John” Cho

Motivation (1) • Crash recovery • Example: Transfer $1 M from Susan to Jane S 1: UPDATE Account SET balance = balance - 1000000 WHERE owner = `Susan‘ S 2: UPDATE Account SET balance = balance + 1000000 WHERE owner = `Jane‘ System crashes after S 1 but before S 2. What now?

Motivation (2) t access to. T 1 data A = balance A = A - 10 Give out $10 balance = A T 2 balance 100 B = balance B = B - 20 Give out $20 balance = B • Q: How can DBMS guarantee that these “bad” scenarios will never happen?

Transaction • A sequence of SQL statements that are executed as “one unit” • Two key commands related to transaction • After a sequence of SQL commands, user can issue either COMMIT or ROLLBACK • COMMIT • “I am done. Commit everything that I have done!” • All changes made by the transaction must be stored permanently • ROLLBACK • “I changed mind. Ignore what I just did!” • Undo all changes made by the transaction

Creating a Transaction • All SQL commands until COMMIT/ROLLBACK become one transaction. time INSERT DELETE SELECT COMMIT DELETE ROLLBACK INSERT

ACID Property of Transaction • DBMS guarantees ACID property on all transactions • Atomicity: “all or nothing” • Either ALL OR NONE of the operations in a transaction is executed • If system crashes in the middle of a transaction, all changes are “undone” • Consistency • If the database was in a “consistent” state before transaction, it is still in a consistent state after the transaction • Isolation • Even if multiple transactions run concurrently, the final result is the same as each transaction runs in isolation in a sequential order • Durability • All changes made by “committed” transaction will remain even after system crash

Autocommit Mode • Sometimes, it is too inconvenient to declare transactions explicitly • Autocommit mode • When ON: Every SQL statement automatically becomes one transaction • When OFF: As usual • All SQL commands through COMMIT/ROLLBACK become one transaction

Setting Autocommit Mode • Oracle: SET AUTOCOMMIT ON/OFF (default is off) • My. SQL: SET AUTOCOMMIT = {0|1} (default is on. Inno. DB only) • MS SQL Server: SET IMPLICIT_TRANSACTIONS OFF/ON (default is off) • IMPLICIT_TRANSACTION ON means AUTOCOMMIT OFF • DB 2: UPDATE COMMAND OPTIONS USING c ON/OFF (default is on) • In JDBC: connection. set. Auto. Commit(true/false) (default is on) • In Oracle, My. SQL, and MS SQL Sever, “BEGIN TRANSACTION” command temporarily disables autocommit mode until COMMIT or ROLLBACK

SQL Isolation Levels • By default, RDBMS guarantees ACID for transactions • Some applications may not need ACID and may want to allow minor “bad scenarios” to gain more “concurrency” • By specifying “SQL Isolation Level, ” app developer can specify what type of “bad scenarios” can be allowed for their apps • Dirty read, non-repeatable read, and phantom

Dirty Read name salary Amy 1000 Eddie 1000 Esther 1000 John 1000 Melanie 1000 • T 1: UPDATE Employee SET salary = salary + 100; T 2: SELECT salary FROM Employee WHERE name = ‘Amy’; • Q: Under ACID, once T 1 update Amy’s salary, can T 2 read Amy’s salary? • Some applications may be OK with dirty read • Among 4 SQL isolation levels, READ UNCOMMITTED allows dirty read

SQL Isolation Levels Dirty read Read uncommitted Read committed Repeatable read Y N N Serializable N 11

Non-repeatable Read • T 1: UPDATE Employee SET salary = salary + 100 WHERE name = ‘John’; T 2: (S 1) SELECT salary FROM Employee WHERE name = ‘John’; . . . (S 2) SELECT salary FROM Employee WHERE name = ‘John’; • Q: Under ACID, can T 2 get different values for S 1 and S 2? • Non-repeatable read: When Ti reads the same tuple multiple times, Ti may get different value • SQL isolation levels, READ UNCOMMITTED and READ COMMITTED, allow non-repeatable read 12

SQL Isolation Levels Dirty read Non-repeatable read Read uncommitted Read committed Repeatable read Y N N Y Y N Serializable N N 13

Phantom • T 1: INSERT INTO Employee VALUES (Beverly, 1000), (Zack, 1000); T 2: SELECT SUM(salary) FROM Employee; name salary Amy 1000 Eddie 1000 Esther 1000 John 1000 Melanie 1000 • Q: Under ACID, what may T 2 return?

Phantom • Phantom: When new tuples are inserted, statements may or may not see (part of) them • Preventing phantom can be very costly • Exclusive lock on the entire table or a range of tuples • Except the isolation level SERIALIZABLE, phantoms are allowed

SQL Isolation Levels Dirty read Non-repeatable read Phantom Read uncommitted Read committed Repeatable read Y N N Y Y Y Serializable N N N 16

Access Mode • A transaction can be declared to be read only, when it has SELECT statements only (no INSERT, DELETE, UPDATE) • DBMS may use this information to optimize for more concurrency

Declaring SQL Isolation Level • SET TRANSACTION [READ ONLY] ISOLATION LEVEL <level> • e. g. , SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; • More precisely “SET TRANSACTION [access mode, ] ISOLATION LEVEL <level>” • access mode: READ ONLY/READ WRITE (default: READ WRITE) • level: • • READ UNCOMMITTED READ COMMITTED (default in Oracle, MS SQL Server) REPEATABLE READ (default in My. SQL, IBM DB 2) SERIALIZABLE • READ UNCOMMITED is allowed only for READ ONLY access mode • Isolation level needs to be set before every transaction

Mixing Isolation Levels • John’ initial salary = 1000 T 1: UPDATE Employee SET salary = salary + 100; ROLLBACK; T 2: SELECT salary FROM Employee WHERE name = ‘John’; • Q: T 1: SERIALIZABLE and T 2: SERIALIZABLE. What may T 2 return? • Q: T 1: SERIALIZABLE and T 2: READ UNCOMMITTED. What may T 2 return? • Isolation level is in the eye of the beholding operation • Global ACID is guaranteed only when all transactions are SERIALIZABLE

Guaranteeing ACID • T 1: UPDATE Student SET GPA = 3. 0 WHERE sid = 30; Main memory (20, Elaine, 2. 0) (30, James, 4. 0) 3. 0 (20, Elaine, 2. 0) (30, James, 4. 0)3. 0 . . . (20, Elaine, 2. 0) (30, James, 4. 0) Disk • DBMS does not immediately writes the updated disk block back to disk for performance reasons • Q: What happens if the system crashes before the block is written back?

Rolling Back to Earlier State •

Partial Execution •

Logging: Intuition • Log record When

Logging Example T 1 x = read(A) x = x - 50 write(A, x) y = read(B) y = y + 50 write(B, y) commit T 2 50 Memory A: 100 z = read(C) z=z*2 write(C, z) commit 150 B: 100 Disk 200 C: 100 <T 1, <T 2, <T 1, B: 100 C: 100 Log file 1 2 3 4 5 6 7 A: 100 start> A, 100, 50> start> C, 100, 200> commit> B, 100, 150> commit> 1 2 3 4 5 6 7 <T 1, <T 2, <T 1, start> A, 100, 50> start> C, 100, 200> commit> B, 100, 150> commit>

Rules for Log-Based Recovery •

Example: Recovery T 1 x = read(A) x = x - 50 write(A, x) y = read(B) y = y + 50 write(B, y) commit Disk T 2 A: 100 z = read(C) z=z*2 write(C, z) commit B: 100 C: 100 Log file 1 2 3 4 <T 1, <T 2, start> A, 100, 50> start> C, 100, 200>

Example: Recovery T 1 x = read(A) x = x - 50 write(A, x) y = read(B) y = y + 50 write(B, y) commit Disk T 2 A: 50 z = read(C) z=z*2 write(C, z) commit B: 100 C: 100 Log file 1 2 3 4 5 <T 1, <T 2, start> A, 100, 50> start> C, 100, 200> commit>

Example: Recovery T 1 x = read(A) x = x - 50 write(A, x) y = read(B) y = y + 50 write(B, y) commit Disk T 2 A: 100 z = read(C) z=z*2 write(C, z) commit B: 100 C: 100 Log file 1 2 3 4 5 6 7 <T 1, <T 2, <T 1, start> A, 100, 50> start> C, 100, 200> commit> B, 100, 150> commit>

Summary • DBMS uses a log file to ensure ACID for transactions • Helps rolling back partially executed transactions • Helps recovery after crash • Before modifying any data, DBMS generates a log record • Before commit, DBMS flushes log records to disk to ensure durability • During recovery, records in the log file are “replayed” to put the system in the supposed state