1SQL: TransactionsCPS 116Introduction to Database Systems2Announcements (October 2) Project milestone #1 due in 1½ weeks Come to my office hours if you want to chat about project ideas Midterm in class this Thursday Open book, open notes Format similar to the sample midterm Covers everything up to today’s lecture Emphasizes materials exercised in homeworks Sample solutions to sample midterm and Homework #2 available3Transactions A transaction is a sequence of database operations with the following properties (ACID): Atomic: Operations of a transaction are executed all-or-nothing, and are never left “half-done”Consistency: Assume all database constraints are satisfiedConsistency: Assume all database constraints are satisfied at the start of a transaction, they should remain satisfied at the end of the transaction Isolation: Transactions must behave as if they were executed in complete isolation from each other Durability: If the DBMS crashes after a transaction commits, all effects of the transaction must remain in the database when DBMS comes back up24SQL transactions A transaction is automatically started when a user executes an SQL statement Subsequent statements in the same session are executed as part of this transaction Statements see changes made by earlier ones in the same transaction Statements in other concurrently running transactions do not see these changes COMMIT command commits the transaction Its effects are made final and visible to subsequent transactions ROLLBACK command aborts the transaction Its effects are undone5Fine prints Schema operations (e.g., CREATE TABLE) implicitly commit the current transaction Because it is often difficult to undo a schema operation Many DBMS support an AUTOCOMMIT feature, which automatically commits every single statement For DB2:• db2 command-line processor turns it on by default• You can turn it off with option +c More examples to come when we cover database API’s6Atomicity Partial effects of a transaction must be undone when User explicitly aborts the transaction using ROLLBACK• E.g., application asks for user confirmation in the last step and issues COMMIT or ROLLBACK depending on the response The DBMS crashes before a transaction commits Partial effects of a modification statement must be undone when any constraint is violated However, only this statement is rolled back; the transaction continues How is atomicity achieved? Logging (to support undo)37Durability Effects of committed transactions must survive DBMS crashes How is durability achieved? Forcing all changes to disk at the end of every transaction?• Too expensive: DBMS manipulates data in memory Logging (to support redo)8Consistency Consistency of the database is guaranteed by constraints and triggers declared in the database and/or transactions themselves Whenever inconsistency arises, abort the statement or hd f d h ktransaction, or (with deferred constraint checking or application-enforced constraints) fix the inconsistency within the transaction9Isolation Transactions must appear to be executed in a serial schedule (with no interleaving operations) For performance, DBMS executes transactions using a serializable schedule In this schedule, operations from different transactions can interleave and execute concurrently But the schedule is guaranteed to produce the same effects as a serial schedule How is isolation achieved? Locking, multi-version concurrency control, etc.410SQL isolation levels Strongest isolation level: SERIALIZABLE Complete isolation SQL default Weaker isolation levels: REPEATABLE READ, READCOMMITTED, READ UNCOMMITTED Increase performance by eliminating overhead and allowing higher degrees of concurrency Trade-off: sometimes you get the “wrong” answer11READ UNCOMMITTED Can read “dirty” data A data item is dirty if it is written by an uncommitted transaction Problem: What if the transaction that wrote the dirty data eventually aborts?Emple rong ergeExample: wrong average -- T1: -- T2:UPDATE StudentSET GPA = 3.0WHERE SID = 142; SELECT AVG(GPA)FROM Student;ROLLBACK;COMMIT;12READ COMMITTED No dirty reads, but non-repeatable reads possible Reading the same data item twice can produce different results Example: different averages -- T1: -- T2:SELECT AVG(GPA)SELECT AVG(GPA)FROM Student;UPDATE StudentSET GPA = 3.0WHERE SID = 142;COMMIT;SELECT AVG(GPA)FROM Student;COMMIT;513REPEATABLE READ Reads are repeatable, but may see phantoms Example: different average (still!) -- T1: -- T2:SELECT AVG(GPA)FROM Student;FROM Student;INSERT INTO StudentVALUES(789, ‘Nelson’, 10, 1.0);COMMIT;SELECT AVG(GPA)FROM Student;COMMIT;14Summary of SQL isolation levelsIsolation level/anomaly Dirty reads Non-repeatable reads PhantomsREAD UNCOMMITTEDPossible Possible PossibleREAD COMMITTEDImpossible Possible PossibleREPEATABLE READImpossible Impossible PossibleSERIALIZABLEImpossible Impossible Impossible Syntax: At the beginning of a transaction,SET TRANSACTION ISOLATION LEVEL isolation_level [READ ONLY|READ WRITE]; READ UNCOMMITTED can only be READ ONLYTransactions in programming (JDBC) Set isolation level for the current transaction con.setTransactionIsolationLevel(l); Where l is one of TRANSACTION_SERIALIZABLE (default), TRANSACTION_REPEATABLE_READ, TRANSACTION_READ_COMITTED, and TRANSACTION_READ_UNCOMMITTEDSet the transaction to be readonly or read/write (default)15Set the transaction to be read-only or read/write (default) con.setReadOnly(true|false); Turn on/off AUTOCOMMIT (commits every single statement) con.setAutoCommit(true|false); Commit/rollback the current transaction (when AUTOCOMMIT is off) con.commit(); con.rollback();6ANSI isolation levels are lock-based READ UNCOMMITTED Short-duration locks: lock, access, release immediately READ COMMITTED Long-duration write lock: do not release write locks until 16commit REPEATABLE READ Long-duration locks on all data items accessed SERIALIZABLE Lock ranges to prevent insertion as wellAn isolation level not based on locksSnapshot isolation Based on multiversion concurrency control Available in Oracle, PostgreSQL, MS SQL Server, etc. How it worksTiXfi i i h17Transaction Xperforms its operations on a private snapshot of the database taken
View Full Document
Unlocking...