Lecture 14: Transactions in SQLOutlineTransactionsWhy Do We Need TransactionsConcurrency control: Three Famous anomaliesDirty ReadsLost UpdatesInconsistent ReadProtection against crashesDefinitionTransactions in SQLRevised CodeTransaction Properties ACIDACID: AtomicityACID: ConsistencyACID: IsolationACID: DurabilityROLLBACKReasons for RollbackREAD-ONLY TransactionsIsolation Levels in SQLIsolation Level: Dirty ReadsSlide 23Isolation Level: Read CommittedIsolation Level: Repeatable ReadIsolation Level: SerializableThe Mechanics of DiskDisk Access CharacteristicsRAIDBuffer Management in a DBMSBuffer ManagerLeast Recently Used (LRU)Slide 33Transaction Management and the Buffer Manager1Lecture 14:Transactions in SQLMonday, October 30, 20062Outline•Transactions in SQL•The buffer manager3Transactions•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 transactions4Why Do We Need Transactions•Concurrency control•Recovery5Concurrency 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’6Dirty ReadsClient 1:/* transfer $100 from account 1 to account 2 */UPDATE Accounts SET balance = balance + 100WHERE accountNo = ‘11111’X = SELECT balance FROM Accounts WHERE accountNo = ‘2222’If X < 100 /* abort . . . . */ then UPDATE Accounts SET balance = balance - 100 WHERE accountNo = ‘11111’Else UPDATE Accounts SET balance = balance - 100 WHERE accountNo = ‘2222’Client 1:/* transfer $100 from account 1 to account 2 */UPDATE Accounts SET balance = balance + 100WHERE accountNo = ‘11111’X = SELECT balance FROM Accounts WHERE accountNo = ‘2222’If X < 100 /* abort . . . . */ then UPDATE Accounts SET balance = balance - 100 WHERE accountNo = ‘11111’Else UPDATE Accounts SET balance = balance - 100 WHERE accountNo = ‘2222’Client 2:/* withdraw $100 from account 1 */X = SELECT balance FROM Accounts WHERE accountNo = ‘1111’If X > 100 then UPDATE Accounts SET balance = balance - 100 WHERE accountNo = ‘11111’ . . . . . Dispense cash . . . .CliClient 2:/* withdraw $100 from account 1 */X = SELECT balance FROM Accounts WHERE accountNo = ‘1111’If X > 100 then UPDATE Accounts SET balance = balance - 100 WHERE accountNo = ‘11111’ . . . . . Dispense cash . . . .Cli7Lost UpdatesClient 1:UPDATE ProductSET Price = Price – 1.99WHERE pname = ‘Gizmo’Client 1:UPDATE ProductSET Price = Price – 1.99WHERE pname = ‘Gizmo’Two managers attempt to do a discount.Will it work ?Client 2:UPDATE ProductSET Price = Price*0.5WHERE pname=‘Gizmo’Client 2:UPDATE ProductSET Price = Price*0.5WHERE pname=‘Gizmo’8Inconsistent ReadWhat’s wrong ?Client 1:UPDATE Products SET quantity = quantity + 5WHERE product = ‘gizmo’UPDATE Products SET quantity = quantity - 5WHERE product = ‘gadget’Client 1:UPDATE Products SET quantity = quantity + 5WHERE product = ‘gizmo’UPDATE Products SET quantity = quantity - 5WHERE product = ‘gadget’Client 2:SELECT sum(quantity) FROM ProductClient 2:SELECT sum(quantity) FROM Product9Protection against crashesWhat’s wrong ?Client 1:UPDATE Products SET quantity = quantity + 5WHERE product = ‘gizmo’UPDATE Products SET quantity = quantity - 5WHERE product = ‘gadget’Client 1:UPDATE Products SET quantity = quantity + 5WHERE product = ‘gizmo’UPDATE Products SET quantity = quantity - 5WHERE product = ‘gadget’Crash !10Definition•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 disappear11Transactions in SQL•In “ad-hoc” SQL:–Default: each statement = one transaction•In a program:START TRANSACTION[SQL statements]COMMIT or ROLLBACK (=ABORT)May be omitted:first SQL querystarts txn12Revised CodeClient 1: START TRANSACTIONUPDATE ProductSET Price = Price – 1.99WHERE pname = ‘Gizmo’COMMITClient 2: START TRANSACTION UPDATE ProductSET Price = Price*0.5WHERE pname=‘Gizmo’COMMITClient 1: START TRANSACTIONUPDATE ProductSET Price = Price – 1.99WHERE pname = ‘Gizmo’COMMITClient 2: START TRANSACTION UPDATE ProductSET Price = Price*0.5WHERE pname=‘Gizmo’COMMITNow it works like a charm13Transaction 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 database14ACID: 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 nothing15ACID: Consistency•The state of the tables is restricted by 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:–Programmer makes sure a txn takes a consistent state to a consistent state–The system makes sure that the tnx is atomic16ACID: Isolation•A transaction executes concurrently with other transaction•Isolation: the effect is as if each transaction executes in isolation of the others17ACID: Durability•The effect of a transaction must continue to exists after the transaction, or the whole program has terminated•Means: write data to disk18ROLLBACK•If the app gets to a place where it can’t complete the transaction successfully, it can execute ROLLBACK•This causes the system to “abort” the transaction–The database returns to the state without any of the previous changes made by activity of the transaction19Reasons for
View Full Document
Unlocking...