Transactions

Transactions
The ISO standard defines a transaction model based on two SQL statements: COMMIT
and ROLLBACK. Most, but not all, commercial implementations of SQL conform to this
model, which is based on IBM’s DB2 DBMS. A transaction is a logical unit of work
consisting of one or more SQL statements that is guaranteed to be atomic with respect
to recovery. The standard specifies that an SQL transaction automatically begins with
a transaction-initiating SQL statement executed by a user or program (for example,
SELECT, INSERT, UPDATE). Changes made by a transaction are not visible to other
concurrently executing transactions until the transaction completes. A transaction can
complete in one of four ways:
n A COMMIT statement ends the transaction successfully, making the database changes
permanent. A new transaction starts after COMMIT with the next transaction-initiating
statement.
n A ROLLBACK statement aborts the transaction, backing out any changes made by
the transaction. A new transaction starts after ROLLBACK with the next transactioninitiating
statement.
n For programmatic SQL (see Appendix E), successful program termination ends the final
transaction successfully, even if a COMMIT statement has not been executed.
n For programmatic SQL, abnormal program termination aborts the transaction.
SQL transactions cannot be nested (see Section 20.4). The SET TRANSACTION statement
allows the user to configure certain aspects of the transaction. The basic format of
the statement is:
SET TRANSACTION
[READ ONLY | READ WRITE] |
[ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED |
REPEATABLE READ | SERIALIZABLE]
The READ ONLY and READ WRITE qualifiers indicate whether the transaction is read
only or involves both read and write operations. The default is READ WRITE if neither
qualifier is specified (unless the isolation level is READ UNCOMMITTED). Perhaps confusingly,
READ ONLY allows a transaction to issue INSERT, UPDATE, and DELETE
statements against temporary tables (but only temporary tables).
The isolation level indicates the degree of interaction that is allowed from other
transactions during the execution of the transaction. Table 6.9 shows the violations of
serializability allowed by each isolation level against the following three preventable
phenomena:
n Dirty read A transaction reads data that has been written by another as yet uncommitted
transaction.
n Nonrepeatable read A transaction rereads data it has previously read but another committed
transaction has modified or deleted the data in the intervening period.
n Phantom read A transaction executes a query that retrieves a set of rows satisfying a
certain search condition. When the transaction re-executes the query at a later time
additional rows are returned that have been inserted by another committed transaction
in the intervening period.
Only the SERIALIZABLE isolation level is safe, that is generates serializable schedules.
The remaining isolation levels require a mechanism to be provided by the DBMS that
can be used by the programmer to ensure serializability. Chapter 20 provides additional
information on transactions and serializability.