Introduction to Database SystemsCSE 444Lecture 8: Transactions in SQLCSE 444 - Summer 2010 1Where We Are• What we have already learned– Relational model of data– Data manipulation language: SQL– Views and constraints–Database design (E/R diagrams & normalization)• But what if I want to update my data?• Today: transactions in SQL (Sec. 6.6)2CSE 444 - Summer 2010TransactionsPblAliti tfl•Problem: An application must perform severalwrites and reads to the database, as a unit• Solution: multiple actions of the application are bundled into one unit called Transaction•Very powerful concepty– Database transactions (that’s where they started)– Transaction monitorsTtil3–Transactional memoryCSE 444 - Summer 2010Turing Awards toTuring Awards to Database Researchers• Charles Bachman 1973 for CODASYL• Edgar Codd 1981 for relational databases• Jim Gray 1998 for transactionsCSE 444 - Summer 2010 4The World Without Transactions• Just write applications that talk to databases• Rely on operating systems for scheduling, and for concurrency control• What can go wrong ? gg– Three famous anomalies– Other anomalies are possible (but not famous)5CSE 444 - Summer 2010Lost UpdatesClient 1:UPDATECustomerClient 2:UPDATECustomerUPDATECustomerSET rentals= rentals + 1WHERE cname= ‘Fred’UPDATECustomerSET rentals= rentals + 1WHERE cname= ‘Fred’Two people attempt to rent two movies for Fred,from two different terminals. What happens ?6CSE 444 - Summer 2010Inconsistent Read (1/2)Client 1: rent-a-moviex=SELECTrentalsFROMCustInconsistent Read (1/2)x SELECT rentalsFROMCustWHERE cname= ‘Fred’if(<5)Client 2: rent-a-moviex=SELECTrentalsFROMCustif (x< 5){ UPDATE CustSET rentals= rentals + 1x= SELECT rentalsFROMCustWHERE cname= ‘Fred’WHERE cname= ‘Fred’ }else println(“Denied !”)if (x < 5){ UPDATE CustSETrentals=rentals+1SETrentals rentals 1WHERE cname= ‘Fred’ }else println(“Denied !”)7What’s wrong ?CSE 444 - Summer 2010InconsistentRead (2/2)Client 1: move fromgizmoÆgadgetInconsistent Read (2/2)Client 1: move from gizmoÆgadgetUPDATE ProductsSETtit tit + 5SETquantity = quantity + 5WHERE product = ‘gizmo’Client 2: inventory….UPDATEProductsSELECT sum(quantity)FROM ProductUPDATE ProductsSET quantity = quantity - 5WHERE product = ‘gadget’What’s wrong ?8CSE 444 - Summer 2010What s wrong ?Dirty ReadsDirty ReadsClient 1: transfer $100 acc1Æ acc2X = Account1.balanceAccount2.balance+=100Account2.balance 100If (X>=100) Account1.balance -=100else { /* rollback ! */else { /* rollback ! */account2.balance -= 100println(“Denied !”) }Client 1: transfer $100 acc2Æacc3Client 1: transfer $100 acc2 Æacc3Y = Account2.balanceAccount3.balance += 100If (Y>=100) Account2.balance -=100else { /* rollback ! */What’s wrong ?9{account3.balance -= 100println(“Denied !”) }Some Famous anomalies• Dirty read– T reads data written by T’ while T’ has not committedWhat can go wrong: T’writes more data (which T has–What can go wrong: T writes more data (which T has already read) or T’ aborts• Inconsistent read–T sees some but not all changes made by T’• Lost update–Two tasks T and T’both modify the same data–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’CSE 444 - Summer 2010 10Protection against crashesClient 1:UPDATE AccountsSET balance= balance - 500WHERE‘F d’WHEREname= ‘Fred’UPDATE AccountsCrash !What’s wrong ?SET balance = balance + 500WHERE name= ‘Joe’11CSE 444 - Summer 2010Enter Transactions• Concurrency control– The famous anomalies and more…• Recovery12CSE 444 - Summer 2010Definition•A transaction= one or more operations•A transaction= one or more operations, which reflect 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 disappear13ppCSE 444 - Summer 2010Transactions in ApplicationsSTART TRANSACTIONMay be omitted:first SQL querystartstxn[SQL statements]COMMITROLLBACK ( ABORT)starts txnCOMMIT or ROLLBACK (=ABORT)(Ad-hoc SQL default: each statement = one txn)14CSE 444 - Summer 2010Revised CodeRevised CodeClient 1: rent-a-movieSTART TRANSACTIONClient 2: rent-a-movieSTART TRANSACTIONSTART TRANSACTIONx= SELECT rentalsFROM CustWHERE‘F d’START TRANSACTIONx= SELECT rentalsFROM CustWHERE‘F d’WHEREcname= ‘Fred’WHEREcname= ‘Fred’if (x < 5){ UPDATE CustSETrentals=rentals+1if (x < 5){ UPDATE CustSETrentals=rentals+1SETrentals rentals 1WHERE cname= ‘Fred’ }else println(“Denied !”)COMMITSETrentals rentals 1WHERE cname= ‘Fred’ }else println(“Denied !”)COMMIT15COMMITCOMMITRevised CodeClient 1: transfer $100 acc1Æacc2Client 1: transfer $100 acc1Æacc2START TRANSACTIONX = Account1.balance; Account2.balance += 100If (X>=100) { Account1.balance -=100; COMMIT }else {println(“Denied!”);ROLLBACK}else {println( Denied !); ROLLBACK}Client 1: transfer $100 acc2Æ acc3START TRANSACTIONSTART TRANSACTIONX = Account2.balance; Account3.balance += 10016If (X>=100) { Account2.balance -=100; COMMIT }else {println(“Denied !”) ; ROLLBACK}Using TransactionsVer eas to seVery easy to use:• START TRANSACTIONCOMMIT•COMMIT• ROLLBACKBut what EXACTLY do they mean ?• Popular culture: ACID• Underlying theory: serializability17CSE 444 - Summer 2010Transaction PropertiesTransaction PropertiesACID•Atomic– State shows either all the effects of txn, or none of them•Consistent•Consistent– Txn moves from a state where integrity holds, to another where integrity holdsIltd•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 database18databaseCSE 444 - Summer 2010ACID: 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 nothing19CSE 444 - Summer 2010ACID: Isolation• A transaction executes concurrently with other transaction• Isolation: the effect is as if each transaction executes in isolation of the others20CSE 444 - Summer 2010ACID: Consistency• The database satisfies integrity constraints– Account number is
View Full Document
Unlocking...