This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 1 SQL: Transactions CPS 116 Introduction to Database Systems 2 Announcements (Tue. Nov. 8) Homework #3 due today! Project milestone #2 due Thursday 3 Transactions 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 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 up 2 4 SQL 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 undone 5 Fine 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 turn it on/off through the API (e.g., JDBC) Examples later in this lecture For PostgreSQL: psql command-line processor turns it on by default You can turn it off at the psql prompt by typing:...
View Full Document
This document was uploaded on 01/17/2012.
- Spring '09