Using Transactions

Transactions are a mechanism by which to group sets of SQL statements for execution. The primary reasons to group statements are:

• To ensure that the entire set of statements runs (or none at all)

• To prevent other statements from being executed at inopportune times

Relational databases are used by millions of people every day. Many of these people are actually using the same databases simultaneously. Transactions can prevent multiple users from editing the same data at the same time. They also allow a series of queries to succeed in bulk or not at all. If transactions were not possible, databases would be useless in multi-user environments.

As an example, say that a college student runs out of money and asks his mother for a wire transfer. His mother goes to the bank, fills out the transfer form, and hands it to the teller. The teller withdraws $100 from the woman's account, and in the same transaction deposits the money into her son's account at his local bank a few hundred miles away. Because these two actions take place within a single database transaction, the withdrawal will be canceled if the deposit fails. Therefore, even though the withdrawal is immediate, it is not "committed" to the database until the deposit succeeds a few moments later at the remote bank.

This idea of binding all actions in one transaction means that no money would be withdrawn from the woman's account unless her son's bank successfully took the deposit. In addition, a transaction will lock the woman's bank account until it is finished. If she went to another teller, for instance, she would be prevented from withdrawing more money than her eventual balance would reflect.

Two concepts are critical to understanding transactions and can be translated into features offered by the major database vendors. These concepts are locking and isolation.

Locking

Locking is a simple idea that is exposed through complex algorithms in each database engine. Fortunately, for our purposes as ColdFusion developers, we need concern ourselves only with the simple ideas of exclusive and shared locking.

In the wire transfer example, the bank's database system might give exclusive privileges to the transaction so that no other reads could take place. If the woman conducting the wire transfer went to another teller to ask for her current balance, the second teller would inform her that the balance could not be calculated because a pending transaction was in progress. This means that the wire transfer placed an exclusive lock on the information being changed. If, on the other hand, a shared lock had been placed on the account, the teller would be able to provide a balance. A shared lock would allow the woman to see the balance but make no updates to it.

NOTE

Although an explanation of shared and exclusive locks is important, it does not follow that ColdFusion can declare locks on the database. Usually, locking is handled by the database on its own. In addition, databases can escalate a lock from record to table to database, depending on how much data is being modified. ColdFusion has no bearing on these affairs. Yet ColdFusion does have the capability to change isolation levels, which affects the lock type. Isolation levels are covered next.

Isolation

Isolation levels are the only means by which ColdFusion can recommend lock types to the relational database. Isolation levels were created to increase performance by giving the database a plan for locking records before the transaction executes.

Four isolation levels are supported by relational databases, and thus all are supported by ColdFusion's transaction tags. These levels are listed below.

NOTE

The isolation levels described here affect performance. They are listed from worst performance (serializable) to best (read_uncommitted).

Other performance considerations related to ColdFusions use of databases are explored in Chapter 48, "Improving Performance."

serializable

serializable is the highest isolation level provided by a database and is also the default. serializable isolation is equivalent to an exclusive lock. No data can be read by other transactions until the transaction that owns the lock is finished. This makes serializable the most reliable isolation level.

repeatable_read

repeatable_read is the second-highest isolation level. A repeatable read is similar to the serializable level, except that other SQL statements can insert data during the transaction. This can change the results of a transaction if the transaction repeats a query that yields the newly inserted data. If you know ahead of time that inserted data will not affect the transaction, using repeatable_read is preferred to using a serializable isolation level for performance reasons.

read_committed

The third-highest level of isolation, read_committed, means that locks will be shared for both inserts and updates across transactions. So a transaction could read some data once and, before finishing, read for a second time the data that had changed since the original read. Note that the other transactions (the ones updating) must be finished for the read to be different. A successfully finished transaction is said to have been committed.

read_uncommitted

The final level of isolation, providing the best performance of the four, is read_uncommitted. This isolation level is the most dangerous to use, because dirty reads are very likely. A dirty read occurs when a certain transaction reads data that has not been committed. If this isolation level was employed in the wire transfer example, the woman could be the victim of some erroneous accounting. Say that the bank were to run its interest rate calculations on her account after her withdrawal of $100. Now imagine if her son's bank went offline and was not able to accept the deposit. Because the entire transaction would fail, her interest rate would be calculated incorrectly. In the same way, a read_uncommitted isolation level allows inserts and updates to be read regardless of whether a transaction has finished.