100%(1)1 out of 1 people found this document helpful
This preview shows page 1 - 9 out of 34 pages.
Databases - TransactionsJianxin LiSchool of Computer Science & Software EngineeringUniversity of Western AustraliaJianxin Li (UWA)Transactions1 / 34
ACIDACID is the one acronym universally associated with Databases.AtomicityConsistencyIsolationDurabilityJianxin Li (UWA)Transactions2 / 34
VideosYou may find Jennifer Widom’s videos on transactions useful:Jianxin Li (UWA)Transactions3 / 34
RobustnessOne of the most important properties of a modern DBMS is that it isrobustunder both normal and unusual operating conditions.Normal conditions include multiple users concurrently accessing thedatabase.Unusual conditions include computer crashes, connection failures andpower outages.It is important for a DBA to understand the role thattransactionsplay in thisrobustness.Jianxin Li (UWA)Transactions4 / 34
InterleavingMost databases are being used by more than one client simultaneously.Suppose that clientAtries to run queriesS1,S2,S3and that at about the sametime, clientBtries to run queriesT1,T2,T3.In order to keep the system responsive for all users, the system willinterleavethe statements fromAwith the statements fromB. So the system mightactually run:S1,T1,T2,S2,S3,T3in that order.What ifAandBare trying to work with thesame tableand some ofA’sstatements are altering things thatBneeds?Jianxin Li (UWA)Transactions5 / 34
System FailureThe “canonical example” of an application where correct treatment oftransactions is critical is transferring money in a bank.For example, suppose that a user at an ATM transfers money:UPDATE AccountsSET balance = balance - 500WHERE id = 1;UPDATE AccountsSET balance = balance + 500WHERE id = 2;Suppose the system crashes after the first statement, but before the second?A database must be able to recover to a consistent state when the systemcomes back online.Jianxin Li (UWA)Transactions6 / 34
TransactionsAtransactionis defined to be anyone executionof a user program.In this context, a “user program” consists of a number of statements thatreadandwritedatabase objects (i.e. tables, values etc), before finallycommittingatwhich point any changes to the state of the DB are made permanent (i.e.written to disk).A transaction then is a sequence of statements that must be treated on an“all-or-nothing” basis — either all the statements must finish withoutinterference from other users, or none.Jianxin Li (UWA)Transactions7 / 34
ACIDAtomicityThe wordatomicis used in a number of contexts to denoteindivisible.In a DB context, transactions areatomicif the system ensures that they cannotbe “half-done” — in other words, the user is guaranteed that either the entiretransaction completes or it fails and has no effect on the database.