Transactions

Transactions

The definition of transaction is a unit of "work" that is atomic, meaning that either all the work is done, or nothing at all. The "work" usually includes the modification of shared resources, e.g. data stored in a database, but also data stored in a transient store. The modifications done in context of a transaction can either be committed or rolled back: a commit means that all modifications actually take place, a rollback means that all modifications are reverted. Transactions are typically characterized by the four ACID properties: Atomicity, Consistency, Isolation and Durability.

ACID Properties of a Transaction

Figure 1: Atomicity	Figure 2: Consistency
Figure 3: Isolation	Figure 4: Durability

• Atomicity: All modifications done in context of the transaction are performed in a single atomic operation. Either all modification are performed (after a commit) or none of them (after a rollback).

• Consistency The data modified by the transaction is in a consistent state before and after a transaction. The meaning what is consistent is depending on the application.

• Isolation The concept of isolation means that the intermediate (possibly inconsistent) state of a transaction is invisible to other transactions. The isolation is discussed in the next section in more detail.

• Durability This property means that the modifications of a transaction are persisted once a transaction is committed. This means that the changed data is still stored after a restart or a system failure.

Transaction Isolation

The question what part of the work of one transaction can be seen by another transaction needs to be discussed in a bit more detail. The strongest concept of isolation requires that concurrent transaction always behave as if they were executed one after another. It is easy to imagine that this restricts the possibility to execute different transactions concurrently and this usually has impacts on the general performance. Mainly because of this trade-off between isolation and concurrency other levels of isolation have been defined. The idea is to relax the requirements of isolation in order to get a better overall performance. The SQL-92 standard suggests four different isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. The strongest isolation level is called SERIALIZABLE since the transactions behave as executed in a serialized way one after another. The isolation level specifies the kind of phenomena that can occur during the execution of concurrent transactions.

Figure 1: Lost Update	Figure 2: Dirty Read
Figure 3: Non Repeatable Read	Figure 4: Phantom Read

• The "Lost Update" phenomenon (see figure 1) occurs when a transactions first read a value and update it depending on the current value. If now transaction A reads the value, then transaction B reads the value updates it and commits the new value, and finally transaction A updates and commits the value, the update done by transaction B is lost.

• The "Dirty Read" phenomeona (see figure 2) occurs when transaction A updates a value and transaction B sees this new value before it is committed (it may never get committed).

• The "Non Repeatable Read" phenomenon (see figure 3) occurs when transaction A reads a value, transaction B modifies this value (and commits it) and then transaction A reads the value again. If transaction A now sees the new value changed by the other transaction the read was not repeatable.

• The "Phantom Read" phenomenon (see figure 4) occurs when transaction A reads some data according to a search condition. Meanwhile transaction B creates additional data that satisfies this search condition. If now transaction A would repeat the query it would get a different search result.

The table in the following figure shows which isolation levels allow which phenomena to occur:

Figure 5: Isolation Levels

• The SERIALIZABLE isolation level is the strongest and forbids all of the phenomena described above.

• The REPEATABLE READ isolation level forbids the "Loat Update", the "Dirty Read" and the "Non Repeatable Read" phenomenon. Only the "Phantom Read" is permitted.

• The READ COMMITTED isolation level forbids the "Loat Update" and the "Dirty Read" phenomena. The "Non Repeatable Read" and "Phantom Read" phenmena are permitted.

• The READ UNCOMMITTED isolation level only forbids the "Loat Update" phenomenon. All others are permitted.

• The SNAPSHOT ISOLATION isolation level is sometimes defined in addition to the four levels. It is quite imilar to the REPEATABLE READ isolation level.

Solution Approaches

There are different concepts that are usually combined to meet the ACID requirements of a transactional system:

• Private workspace: Each transaction has a private workspace containing copies / snapshots of the entries accessed in this transaction. Updates are only done in the private workspace and written back only on commit (preventing dirty read).

• Pessimistic read locking: entries read or updated by one transaction are locked for this transaction. Other transactions that want to read or update these entries have to wait until the first transaction is finished.

• Pessimistic write locking: entries updated by one transaction are locked for this transaction. Other transactions that want to update these entries have to wait until the first transaction is finished.

• Optimistic locking: if a transaction updates an entry it increases a version counter for this entry. If between the read of an entry and the commit of an update the version counter of the entry has changed an optimistic lock error is raised. This should be handled by rolling the whole transaction back and retrying it.

Figure 1: Private Workspace Model

Figure 2: Optimistic Locking

Next Chapter: Concepts