Lecture 14Transactions in SQL.ppt

Lecture 14:Transactions in SQL Monday, October 30, 2006 Outline Transactions in SQL The buffer manager Transactions Major component of database systems Critical for most applications; arguably more so than SQL Turing awards to database researchers: Charles Bachman 1973 Edgar Codd 1981 for inventing relational dbs Jim Gray 1998 for inventing transactions Why Do We Need Transactions Concurrency control Recovery Concurrency control:Three Famous anomalies Dirty read T reads data written by T’ while T’ is running Then 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’ Dirty Reads Lost Updates Inconsistent Read Protection against crashes Definition A transaction = one or more operations, which reflects a single real-world transition In the real world, this happened completely or not at all Examples Transfer money between accounts Purchase a group of products Register for a class (either waitlist or allocated) If grouped in transactions, all problems in previous slides disappear Transactions in SQL In “ad-hoc” SQL: Default: each statement = one transaction In a program: START TRANSACTION [SQL statements] COMMIT or ROLLBACK (=ABORT) Revised Code Transaction PropertiesACID 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 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 ACID: Consistency The state of the tables is restricted by integrity constraints Account number is unique Stock amount can’t be negative Su