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 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