1 Lecture 8: Transactions in SQL Wednesday, April 14, 2010 Dan Suciu -- 444 Spring 20102 Outline • Transactions in SQL (6.6) Dan Suciu -- 444 Spring 20103 Transactions • The problem: An application must perform several writes and reads to the database, as a unity • Solution: multiple actions of the application are bundled into one unit called Transaction Dan Suciu -- 444 Spring 2010Transactions • Database transactions (that’s where they started) • Transaction monitors • Transactional memory Dan Suciu -- 444 Spring 2010 4Turing Awards to Database Researchers • Charles Bachman 1973 for CODASYL • Edgar Codd 1981 for relational databases • Jim Gray 1998 for transactions 5 Dan Suciu -- 444 Spring 2010The World Without Transactions • Just write applications that talk to databases • Rely on operating systems for scheduling, and for concurrency control • What can go wrong ? – Three famous anomalies – Other anomalies are possible (but not famous) 6 Dan Suciu -- 444 Spring 20107 Lost Updates Client 1: UPDATE Customer SET rentals= rentals + 1 WHERE cname= ‘Fred’ Two people attempt to rent two movies for Fred, from two different terminals. What happens ? Client 2: UPDATE Customer SET rentals= rentals + 1 WHERE cname= ‘Fred’ Dan Suciu -- 444 Spring 20108 Inconsistent Read (1/2) Client 1: rent-a-movie x = SELECT rentals FROM Cust WHERE cname= ‘Fred’ if (x < 5) { UPDATE Cust SET rentals= rentals + 1 WHERE cname= ‘Fred’ } else println(“Denied !”) Client 2: rent-a-movie x = SELECT rentals FROM Cust WHERE cname= ‘Fred’ if (x < 5) { UPDATE Cust SET rentals= rentals + 1 WHERE cname= ‘Fred’ } else println(“Denied !”) What’s wrong ? Dan Suciu -- 444 Spring 20109 Inconsistent Read (2/2) Client 1: move from gizmogadget UPDATE Products SET quantity = quantity + 5 WHERE product = ‘gizmo’ UPDATE Products SET quantity = quantity - 5 WHERE product = ‘gadget’ Client 2: inventory…. SELECT sum(quantity) FROM Product Dan Suciu -- 444 Spring 201010 Dirty Reads Client 1: transfer $100 acc1 acc2 X = Account1.balance Account2.balance += 100 If (X>=100) Account1.balance −=100 else { /* rollback ! */ account2.balance −= 100 println(“Denied !”) What’s wrong ? Dan Suciu -- 444 Spring 2010 Client 1: transfer $100 acc2 acc3 Y = Account2.balance Account3.balance += 100 If (Y>=100) Account2.balance −=100 else { /* rollback ! */ account3.balance −= 100 println(“Denied !”)11 The Three Famous anomalies • Dirty read – T reads data written by T’ while T’ has not committed – What can go wrong: T’ write more data (which T has already read), or T’ aborts • Lost update – Two tasks T and T’ both modify the same data – T and T’ both commit – Final state shows effects of only T, but not of T’ • Inconsistent read – One task T sees some but not all changes made by T’ Dan Suciu -- 444 Spring 201012 Protection against crashes What’s wrong ? Client 1: UPDATE Accounts SET balance= balance - 500 WHERE name= ‘Fred’ UPDATE Accounts SET balance = balance + 500 WHERE name= ‘Joe’ Crash ! Dan Suciu -- 444 Spring 201013 Transactions Do two things: • Concurrency control • Recovery Dan Suciu -- 444 Spring 201014 Definition • A transaction = one or more operations, which reflects a single real-world transition – Happens completely or not at all • Examples – Transfer money between accounts – Rent a movie; return a rented movie – Purchase a group of products – Register for a class (either waitlisted or allocated) • By using transactions, all previous problems disappear Dan Suciu -- 444 Spring 201015 Transactions in Applications START TRANSACTION [SQL statements] COMMIT or ROLLBACK (=ABORT) May be omitted: first SQL query starts txn Dan Suciu -- 444 Spring 2010Transactions in Ad-hoc SQL • Default: each statement = one transaction 16 Dan Suciu -- 444 Spring 201017 Revised Code Client 1: rent-a-movie START TRANSACTION x = SELECT rentals FROM Cust WHERE cname= ‘Fred’ if (x < 5) { UPDATE Cust SET rentals= rentals + 1 WHERE cname= ‘Fred’ } else println(“Denied !”) COMMIT Client 2: rent-a-movie START TRANSACTION x = SELECT rentals FROM Cust WHERE cname= ‘Fred’ if (x < 5) { UPDATE Cust SET rentals= rentals + 1 WHERE cname= ‘Fred’ } else println(“Denied !”) COMMIT Dan Suciu -- 444 Spring 2010Revised Code 18 Client 1: transfer $100 acc1 acc2 START TRANSACTION X = Account1.balance; Account2.balance += 100 If (X>=100) { Account1.balance -=100; COMMIT } else {println(“Denied !”; ROLLBACK) Dan Suciu -- 444 Spring 2010 Client 1: transfer $100 acc2 acc3 START TRANSACTION X = Account2.balance; Account3.balance += 100 If (X>=100) { Account2.balance -=100; COMMIT } else {println(“Denied !”; ROLLBACK)Using Transactions Very easy to use: • START TRANSACTION • COMMIT • ROLLBACK But what EXACTLY do they mean ? • Popular culture: ACID • Underlying theory: serializability 19 Dan Suciu -- 444 Spring 201020 Transaction Properties ACID • Atomic – State shows either all the effects of txn, or none of them • Consistent – Txn moves from a state where integrity holds, to another where integrity holds • Isolated – Effect of txns is the same as txns running one after another (ie looks like batch mode) • Durable – Once a txn has committed, its effects remain in the database Dan Suciu -- 444 Spring 201021 ACID: Atomicity • Two possible outcomes for a transaction – It commits: all the changes are made – It aborts: no changes are made • That is, transaction’s activities are all or nothing Dan Suciu -- 444 Spring 201022 ACID: Isolation • A transaction executes concurrently with other transaction • Isolation: the effect is as if each transaction executes in isolation of the others Dan Suciu -- 444 Spring 201023 ACID: Consistency • The database satisfies integrity constraints – Account number is unique – Stock amount can’t be negative – Sum of debits and of credits is 0 • Constraints may be explicit or implicit • How consistency is achieved: – Applications preserve consistency, assuming they run atomically, and they run in isolation – The system ensures atomicity
View Full Document
Unlocking...