1
SQL: Transactions
CPS 116
Introduction to Database Systems
2
Announcements
Homework #2 due next Tuesday (Sept. 28)
Project milestone #1 due next Thursday
Discussion session this week (Homework #2 Q&A)
Time and place will be announced via email
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 transactio ns do not see
these changes
COMMIT command commits the transaction
Its effects are made final and visible to subseque nt 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
Sometime you need to turn off a feature called
AUTOCOMMIT, which automatically commits every
single statement
Example: Run DB2’s db2 command-line processor with
the option +c
More examples to come when we cover database API’s
6
Atomicity
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)
