Unformatted text preview:

1SQL: TransactionsCPS 116Introduction to Database Systems2Announcements (Thu. Sep. 25) Homework #2 due in a week! Graded Homework #1 should be available next Tuesday Project milestone #1 due in 3 weeksj I will start pitching project ideas in class Midterm in two weeks Sample midterm will be available next Tuesday Open book, open notes3Transactions 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 up4SQL 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 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 You can it on/off through the API (e.g., JDBC)• Examples later in this lecture For DB2:• db2 command-line processor turns it on by default• You can turn it off with option +c6Atomicity 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)27Durability 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.10SQL 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 ergeExample: 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;313REPEATABLE 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_UNCOMMITTEDSet the transaction to be readonly or read/write (default)15Set 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();ANSI 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 h17Transaction Xperforms its operations on a private snapshot of the database taken at the start of X X can commit only if it does not write any data that has been


View Full Document

Duke CPS 116 - Transactions

Documents in this Course
Part I

Part I

8 pages

XSLT

XSLT

4 pages

XSLT

XSLT

8 pages

Part I

Part I

8 pages

XSLT

XSLT

8 pages

Load more
Download Transactions
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Transactions and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Transactions 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?