Transactions

A transaction consists of a sequence of query and/or update statements. The SQL
standard specifies that a transaction begins implicitly when an SQL statement is
executed. One of the following SQL statements must end the transaction:

• Commit work commits the current transaction; that is, it makes the updates
performed by the transaction become permanent in the database. After the
transaction is committed, a new transaction is automatically started.

• Rollback work causes the current transaction to be rolled back; that is, it
undoes all the updates performed by the SQL statements in the transaction.
Thus, the database state is restored to what it was before the first statement
of the transaction was executed.

The keyword work is optional in both the statements.

Transaction rollback is useful if some error condition is detected during ex?ecution of a transaction. Commit is similar, in a sense, to saving changes to a
document that is being edited, while rollback is similar to quitting the edit ses?sion without saving changes. Once a transaction has executed commit work, its
effects can no longer be undone by rollback work. The database system guarantees that in the event of some failure, such as an error in one of the SQL statements,
a power outage, or a system crash, a transaction’s effects will be rolled back if it
has not yet executed commit work. In the case of power outage or other system
crash, the rollback occurs when the system restarts.

For instance, consider a banking application, where we need to transfer money
from one bank account to another in the same bank. To do so, we need to update
two account balances, subtracting the amount transferred from one, and adding
it to the other. If the system crashes after subtracting the amount from the first
account, but before adding it to the second account, the bank balances would be
inconsistent. A similar problem would occur, if the second account is credited
before subtracting the amount from the first account, and the system crashes just
after crediting the amount.

As another example, consider our running example of a university applica?tion. We assume that the attribute tot_cred of each tuple in the student relation
is kept up-to-date by modifying it whenever the student successfully completes
a course. To do so, whenever the takes relation is updated to record successful
completion of a course by a student (by assigning an appropriate grade) the corresponding student tuple must also be updated. If the application performing these two updates crashes after one update is performed, but before the second one is performed, the data in the database would be inconsistent.

By either committing the actions of a transaction after all its steps are com?pleted, or rolling back all its actions in case the transaction could not complete
all its actions successfully, the database provides an abstraction of a transaction
as being atomic, that is, indivisible. Either all the effects of the transaction are
reflected in the database, or none are (after rollback).

Applying the notion of transactions to the above applications, the update
statements should be executed as a single transaction. An error while a transaction
executes one of its statements would result in undoing of the effects of the earlier
statements of the transaction, so that the database is not left in a partially updated
state.

If a program terminates without executing either of these commands, the
updates are either committed or rolled back. The standard does not specify which
of the two happens, and the choice is implementation dependent.
In many SQL implementations, by default each SQL statement is taken to be a
transaction on its own, and gets committed as soon as it is executed. Automatic
commit of individual SQL statements must be turned off if a transaction consisting
of multiple SQL statements needs to be executed. How to turn off automatic
commit depends on the specific SQL implementation, although there is a standard
way of doing this using application program interfaces such as JDBC or ODBC,
which we study later, in Sections 5.1.1 and 5.1.2, respectively.

A better alternative, which is part of the SQL:1999 standard (but supported by
only some SQL implementations currently), is to allow multiple SQL statements
to be enclosed between the keywords begin atomic ... end. All the statements
between the keywords then form a single transaction.

We study further properties of transactions in Chapter 14; issues in imple?menting transactions in a single database are addressed in Chapters 15 and 16,
while Chapter 19 addresses issues in implementing transactions across multiple
databases, to deal with problems such as transfer of money across accounts in
different banks, which have different databases.

Frequently Asked Questions