How to Protect Data with SQL Transactions

By Allen G. Taylor

It is very important to maintain the integrity of your data. The transaction in one of SQL’s main tools for maintaining database integrity. SQL transactions and the default transaction can useful in certain situations.

How to use SQL transactions

An SQL transaction encapsulates all the SQL statements that can have an effect on the database. An SQL transaction is completed with either a COMMIT or ROLLBACK statement:

  • If the transaction finishes with a COMMIT, the effects of all the statements in the transaction are applied to the database in one rapid-fire sequence.

  • If the transaction finishes with a ROLLBACK, the effects of all the statements are rolled back (that is, undone), and the database returns to the state it was in before the transaction began.

In this discussion, the term application means either an execution of a program (whether in Java, C++, or some other programming language) or a series of actions performed at a terminal during a single logon.

An application can include a series of SQL transactions. The first SQL transaction begins when the application begins; the last SQL transaction ends when the application ends. Each COMMIT or ROLLBACK that the application performs ends one SQL transaction and begins the next. For example, an application with three SQL transactions has the following form:

Start of the application
Various SQL statements (SQL transaction-1)
COMMIT or ROLLBACK
Various SQL statements (SQL transaction-2)
COMMIT or ROLLBACK
Various SQL statements (SQL transaction-3)
COMMIT or ROLLBACK
End of the application

A normal SQL transaction has an access mode that is either READ-WRITE or READ-ONLY; it has an isolation level that is SERIALIZABLE, REPEATABLE READ, READ COMMITTED, or READ UNCOMMITTED. The default characteristics are READ-WRITE and SERIALIZABLE. If you want any other characteristics, you have to specify them with a SET TRANSACTION statement such as the following:

SET TRANSACTION READ ONLY ;

or

SET TRANSACTION READ ONLY REPEATABLE READ ;

or

SET TRANSACTION READ COMMITTED ;

You can have multiple SET TRANSACTION statements in an application, but you can specify only one in each transaction, and it must be the first SQL statement executed in the transaction. If you want to use a SET TRANSACTION statement, execute it either at the beginning of the application or after a COMMIT or ROLLBACK.

You must perform a SET TRANSACTION at the beginning of every transaction for which you want nondefault properties, because each new transaction after a COMMIT or ROLLBACK is given the default properties automatically.

A SET TRANSACTION statement can also specify a DIAGNOSTICS SIZE, which determines the number of error conditions for which the implementation should be prepared to save information. (Such a numerical limit is necessary because an implementation can detect more than one error during a statement.) The SQL default for this limit is implementation-defined, and that default is almost always adequate.

The default transaction

The default SQL transaction has characteristics that are satisfactory for most users most of the time. If necessary, you can specify different transaction characteristics with a SET TRANSACTION statement.

The default transaction makes a couple of other implicit assumptions:

  • The database will change over time.

  • It’s always better to be safe than sorry.

It sets the mode to READ-WRITE, which, as you may expect, enables you to issue statements that change the database. It also sets the isolation level to SERIALIZABLE, which is the highest level of isolation possible (thus the safest). The default diagnostics size is implementation-dependent. Look at your SQL documentation to see what that size is for your system.