Transactions and LockingRose-Hulman Institute of TechnologyCurt CliftonOutline ACID Transactions COMMIT and ROLLBACK Managing Transactions LocksThe Setting Database systems are normally being accessed bymany users or processes at the same time Operating Systems also deal with concurrent access OSs allow two people to edit a document at the same time. If both write, one’s changes get lost. DB can and must do betterExample Mom and Dad each deposit $100 fromdifferent ATMs into your account at about thesame timeACID Transactions Atomic All or nothing Consistent Constraints preserved Isolated (Apparently) one user at a time Durable Crashes can’t violate the other propertiesTransactions in SQL SQL supports transactions Generic query interface Each statement issued is a transaction by itself Programming interfaces A transaction begins with first SQL statement Ends with the procedure end (or an explicit end)Ending Transactions COMMIT completes a transaction Modifications are now permanent in the database ROLLBACK ends transaction by aborting No effects on the database! Failures (e.g., division by 0) also causeROLLBACKAnother Example Assume the usual Sells(rest,soda,price)relation Suppose that Majnoo’s Rest sells only Coke for$1.50 and Salaam Cola for $1.75. Laila is querying Sells for the highest and lowest price Majnoo charges. Majnoo decides to stop selling Coke and Salaam Cola to starting only Juice at $2.00Laila’s Program Laila executes the following two SQLstatements Call this one “max”: SELECT MAX(price) FROM SellsWHERE rest = 'Majnoo''s Rest'; “min”: SELECT MIN(price) FROM SellsWHERE rest = 'Majnoo''s Rest';Majnoo’s Program At about the same time, Majnoo executes thefollowing SQL statements “del” DELETE FROM SellsWHERE rest = 'Majnoo''s Rest'; “ins” INSERT INTO SellsVALUES('Majnoo''s Rest', 'Juice', 2.00);Interleaving of Statements Constraints: max must come before min del must come before ins No other constraints on the order of thestatementsExample: Strange Interleaving Suppose the steps execute in the order: max del ins min What answers does Laila see?Fixing the Problem: Transactions If we group Laila’s statements max min intoone transaction: Cannot see this inconsistency Will see Majnoo’s prices at some fixed timeProblem: Undoing Changes Majnoo executes del ins Changes his mind Reverses the changes, say by del', ins' Suppose the order is: del ins max min del' ins' What does Laila see?Solution If Majnoo executes del ins as a transaction, itseffect cannot be seen by others until thetransaction executes COMMIT Instead of del' ins' he uses ROLLBACK instead Effects of transaction can never be seen.Transactions and Locks in SQL Server Transactions Ensure That Multiple DataModifications Are Processed Together Locks Prevent Update Conflicts Transactions are serializable Locking is automatic Locks allow concurrent use of data Concurrency ControlManaging Transactions (outline) Transaction Recovery and Checkpoints Considerations for Using Transactions Setting the Implicit Transactions Option Restrictions on User-defined TransactionsTransaction Recovery, CheckpointsTime (and place in log)DatabaseTransaction LogTransaction LogINSERT …DELETE …UPDATE ……INSERT …DELETE …UPDATE ……INSERT …DELETE …UPDATE ……INSERT …DELETE …UPDATE ……INSERT …DELETE …UPDATE ……CHECKPOINTCRASH!!!COMMITCOMMITCOMMITRecovery Needed? NONERecovery Needed? ROLL FORWARDRecovery Needed? ROLL BACKRecovery Needed? ROLL FORWARDRecovery Needed? ROLL BACKZOT!Considerations when Using Transactions Transaction Guidelines Keep transactions as small as possible Use caution with certain Transact-SQLstatements Avoid transactions that require user interaction Issues in Nesting Transactions Allowed, but not recommended Use @@trancount to determine nesting levelSET IMPLICIT_TRANSACTIONS ONImplicit Transactions Automatically Starts a Transaction When YouExecute Certain Statements Nested Transactions Are Not Allowed Transaction Must Be Explicitly Completedwith COMMIT or ROLLBACK By Default, Setting Is Off ALTER DATABASE BACKUP LOG CREATE DATABASE DROP DATABASE RECONFIGURE RESTORE DATABASE RESTORE LOG UPDATE STATISTICSRestrictions on Transactions Certain Statements May Not Be Included in aTransaction:How much ACID have we done? Explicit transactions support Atomicity Automatic rollback on errors supportsConsistency Transaction log supports DurabilityLocks Support IsolationLockable Resources Item Item Description DescriptionRID Row identifierKey Row lock within an indexPageExtentTableData page or index pageGroup of pagesEntire tableDatabase Entire databaseTypes of Locks Basic Locks Shared Exclusive Special Situation Locks Intent Update Schema Bulk updateLock Compatibility Locks May or May Not Be Compatible withOther Locks Examples Shared locks are compatible with all locks exceptexclusive Exclusive locks are not compatible with any otherlocks Update locks are compatible only with sharedlocksDynamicLockingTablePageRowCostGranularityLocking CostConcurrency CostWeek Eight Deliverables Sample Reports See rubric on Angel First draft due by Friday night (50 points) New versions due week nine (100 points) Meet with me during lab time today to agreeon