13.7. Transactions and Concurrency

Operations affecting data in a DBMS are contained in transactions. A transaction may be composed of many individual actions, which, in some sense, can be considered together as an atomic unit of activity. In order to preserve the integrity of the database, we can make positive decisions about whether to accept the results of a unit of activity or not. The corresponding database operations are commit, which makes the results permanent, and rollback, which aborts the activity and removes the results of the transaction, leaving the database in the state it was in before the transaction began. In some situations, the commit or rollback is carried out implicitly, depending on whether a program terminates normally or abnormally.

If database systems handled transactions one at a time this mechanism would be sufficient to maintain integrity. We could call this idealistic situation serialexecution. However, a realistic database system could be expected to have many concurrent users, each carrying out their own individual transactions. It’s therefore entirely likely that several users could be working in the same part of the database: for example, writing and reading data to and from the same table. Although we may expect integrity within a transaction, some potential problems arise when transactions overlap. The following situations are possible.

Dirty reads (also known as uncommitted reads). Transaction 1 modifies a row in a table. Transaction2 reads that row before Transaction 1 performs a commit. If Transaction 1 subsequently performs a rollback, Transaction2 will have read a row that never really existed.

Nonrepeatable reads. Transaction 1 reads a row from a table. Transaction2 modifies the row, or perhaps deletes it, and then performs a commit. If Transaction 1 attempts to read the row again it will find that it has changed, or perhaps even disappeared completely!

Phantoms. Transaction 1 reads some rows that satisfy a search condition. Transaction2 then creates some rows that would match the search condition used by Transaction 1. If Transaction 1 attempts to read the rows again, using the same search condition, it will obtain the original result plus some additional rows that have now mysteriously appeared.

Interactions between transactions can be controlled using lockson resources. A transaction that wants to use a resource, such as a row or a table, can acquire a lock that makes it the temporary owner of the resource. Other transactions are obliged to wait until the first transaction has relinquished the lock before they can compete for the same resource. A typical sequence is shown in Figure 13.3.

There are two main kinds of lock: sharedand exclusive.A shared lock is typically acquired by a transaction when it wants to read some data but does not want another transaction to change it. The resource can be shared with other transactions that have the same intentions. An exclusive lock is typically acquired when a transaction wants to modify the resource, and wants to prevent other transactions from either writing to or reading from the resource until the operation is complete. Commercial systems may have many subtly different variations on these two main kinds of lock.

Obviously, locking is likely to have an impact on how quickly transactions can complete their business as they compete for resources. We have a potential trade-off here between better isolation between transactions but reduced performance, or reduced isolation but better performance, as shown in Figure 13.4.

The choice will be dependent on the nature of the application, and so the SQL standard defines several isolation levelsthat represent different points along this spectrum. The standard isolation levels are summarized in Table 13.4. At the highest level, SERIALIZABLE, we can treat transactions as though they are running in a one-at-a-time serial mode, albeit with a certain cost in terms of performance.

Most database vendors support at least some of these isolation levels, but not necessarily under the same names. For example, DB2 refers to these same four levels as UNCOMMITTED READ, CURSOR STABILITY, READ STABILITY, and REPEATABLE READ (the last of these being particularly confusing when compared to the standard). Other vendors may offer additional isolation levels. For example, SQL Server 2005 supports the four standard levels plus an additional SNAPSHOT level.

An application can specify the isolation level required for a connection. If no level is specified, the DBMS will assume some default, such as READ COMMITTED, that represents a reasonable compromise between isolation and performance. It may also be appropriate to switch temporarily to a different isolation level to improve either isolation or performance. For example, an application reading data from a table that is read only could temporarily drop down to the READ UNCOMMITTED level to improve performance—in this case without compromising isolation, because dirty reads cannot occur on a table that is not being updated concurrently.

A practical DBMS also has to deal with a number of side effects of locking. One is the deadlocksituation. Transaction 1 requires resources Aand B,and successfully acquires a lock on A.Meanwhile, Transaction2, which requires the same two resources, has acquired a lock on B.The result is that Transaction 1 is unable to proceed because it is waiting for B,and Transaction2 is unable to proceed because it is waiting for A.Most DBMSs have a built-in background process to detect deadlock situations. Typically deadlock situations are resolved automatically by rolling back one of the transactions. Another potential problem is a poorly designed application that acquires a lock and retains it for an unduly long period. Again, an automatic process can be included to detect such conditions and roll back the offending transactions.