Oracle7 Server Concepts

Contents Index Home Previous Next

How Oracle Controls Data Concurrency

Oracle maintains data concurrency, integrity, and consistency by using a multiversion consistency model and various types of locks and transactions.

Multiversion Concurrency Control

Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time. Oracle can also provide read consistency to all of the queries in a transaction.

Oracle uses the information maintained in its rollback segments to provide these consistent views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions.

Figure 10 - 2 shows how Oracle can provide statement-level read consistency using data in rollback segments.

Figure 10 - 2. Transactions and Read Consistency

As a query enters the execution stage, the current system change number (SCN) is determined; in Figure 10 - 2, this system change number is 10023. As data blocks are read on behalf of the query, only blocks written with the observed system change number are used. Blocks with changed data (more recent SCNs) are reconstructed using data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.

The "Snapshot Too Old" Message

In rare situations, Oracle cannot return a consistent set of results (often called a snapshot) for a long-running query. This occurs because not enough information remains in the rollback segments to reconstruct the older data. Usually, this error is produced when a lot of update activity causes the rollback segment to wrap around and overwrite changes needed to reconstruct data that the long-running query requires. In this event, error 1555 will result:

ORA-1555: snapshot too old (rollback segment too small) 

You can avoid this error by creating more or larger rollback segments. Alternatively, long-running queries can be issued when there are few concurrent transactions, or you can obtain a shared lock on the table you are querying, thus prohibiting any other exclusive locks during the transaction.

Statement Level Read Consistency

Oracle always enforces statement-level read consistency. This guarantees that the data returned by a single query is consistent with respect to the time that the query began. Therefore, a query never sees dirty data nor any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.

A consistent result set is provided for every query, guaranteeing data consistency, with no action on the user's part. The SQL statements SELECT, INSERT with a query, UPDATE, and DELETE all query data, either explicitly or implicitly, and all return consistent data. Each of these statements uses a query to determine which data it will affect (SELECT, INSERT, UPDATE, or DELETE, respectively).

A SELECT statement is an explicit query and may have nested queries or a join operation. An INSERT statement can use nested queries. UPDATE and DELETE statements can use WHERE clauses or subqueries to affect only some rows in a table rather than all rows.

While queries used in INSERT, UPDATE, and DELETE statements are guaranteed a consistent set of results, they do not see the changes made by the DML statement itself. In other words, the data the query in these operations sees reflects the state of the data before the operation began to make changes.

Transaction Level Read Consistency

Oracle also allows the option of enforcing transaction-level read consistency. When a transaction executes in serializable mode (see below), all data accesses reflect the state of the database as of the time the transaction began. This means that the data seen by all queries within the same transaction is consistent with respect to a single point in time, except that queries made by a serializable transaction do see changes made by the transaction itself. Therefore, transaction-level read consistency produces repeatable reads and does not expose a query to phantoms.

Oracle Isolation Levels

Oracle provides three transaction isolation modes:

read committed This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query will never read dirty (uncommitted) data.
Because Oracle does not prevent other transactions from modifying the data read by a query, that data may be changed by other transactions between two executions of the query. Thus, a transaction that executes a given query twice may experience both non-repeatable read and phantoms.
serializable transactions Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not see non-repeatable reads or phantoms.
read only Read only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.

Setting the Isolation Level

Application designers, application developers, and DBAs should set an isolation level for transactions appropriate for the specific application and workload. As an application designer, application developer, or DBA, you can choose different isolation levels for different transactions.

You can set the isolation level of a transaction by using one of these commands at the beginning of a transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ ONLY;

To save the networking and processing cost of beginning each transaction with a SET TRANSACTION command, you can use the ALTER SESSION command to set the transaction isolation level for all subsequent transactions:

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

You can also change the default transaction isolation level for the system by using the ALTER SYSTEM command. For detailed information on any of these SQL commands, see chapter 4 of Oracle7 Server SQL Reference.

Read Committed Isolation

The default isolation level for Oracle is read committed. This degree of isolation is appropriate for most applications. With read committed isolation levels, Oracle causes each query to execute with respect to its own snapshot time, thereby permitting non-repeatable reads and phantoms for two executions of a query, but providing higher potential throughput.

Read committed isolation is the appropriate level of isolation for environments where few transactions are likely to conflict.

Serializable Isolation

Serializable mode is suitable for environments with large databases and short transactions that update only a few rows. It is appropriate for environments where both of the following are true:

Serializable mode prevents interactions between transactions that would preclude them from executing one at a time. In other words, concurrent transactions executing in serializable mode are only permitted to make database changes the could have made if the transactions had been scheduled to execute one after another. This mode ensures transactions move the database from one consistent state to another consistent state. It prevents potentially harmful interactions between concurrently executing transactions, but causes a reduction in throughput that is often unacceptable.

A serializable transaction executes against the database as it existed at the beginning of the transaction. A serializable transaction cannot modify rows changed by other transactions that are "too recent," that is, that commit after the serializable transaction began.

Oracle generates an error when a serializable transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began:

ORA-08177: Cannot serialize access for this transaction

Here is an example:

Figure 10 - 3. Serializable Transaction Failure

When a serializable transaction fails with the "Can't serialize access" error, the application can take any of several actions:

Oracle stores control information in each data block to manage access by concurrent transactions. Therefore, if you set the transaction isolation level to serializable, you must use the ALTER TABLE or CREATE TABLE command to set INITRANS to at least 3.

Additional Considerations for Serializable Isolation

The following sections provide additional background and information useful to application developers and database administrators planning to use serializable transactions.

Both read committed and serializable transactions use row-level locking, and both will wait if they try to change a row updated by an uncommitted concurrent transaction. The second transaction that tries to update a given row waits for the other transaction to commit or rollback and release its lock. If that other transaction rolls back, the waiting transaction (regardless of its isolation mode) can proceed to change the previously locked row, as if the other transaction had not existed.

However, read committed and serializable transactions behave differently if the other (blocking) transaction commits. When the other transaction commits and releases its locks, a read committed transaction will proceed with its intended update. A serializable transaction, however, will fail with the error "Can't serialize access", since the other transaction has committed a change that was made since the serializable transaction began.

Serializable Transactions and Row Locking

Oracle permits a serializable transaction to modify a data row only if it can determine that prior changes to the row were made by transactions that had committed when the serializable transaction began. To make this determination efficiently, Oracle uses control information stored within the block that indicates which rows in the block contain committed and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE and ALTER TABLE.

Update Intensive Environments and Serializable Transactions

Under some circumstances, Oracle may have insufficient history information to determine whether a row has been updated by a "too recent" transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period.

Higher values of INITRANS should be used for tables that will experience many transactions updating the same blocks. This parameter will cause Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block.

Referential Integrity and Serializable Transactions

Because Oracle does not use read locks, even in serializable transactions, data read by one transaction can be overwritten by another. Transactions that perform database consistency checks at the application level should not assume that the data they read will not change during the execution of the transaction (even though such changes are not visible to the transaction). Database inconsistencies can result unless such application-level consistency checks are coded with this in mind, even when using serializable transactions.

For more information about referential integrity and serializable transactions, see Oracle7 Server Application Developer's Guide.

Oracle Parallel Server and Distributed Transactions

Both read committed and serializable transaction isolation levels can be used with the Oracle Parallel Server (a cluster of several Oracle instances running against a single database across a number of nodes).

Oracle supports distributed serializable transactions, where a given transaction updates data in multiple physical databases (protected by two-phase commit to ensure all nodes or none commit). In a distributed database environment, all servers (whether Oracle or non-Oracle) that participate in a serializable transaction are required to support that transaction isolation mode.

If a serializable transaction tries to update data in a database managed by a server that does not support serializable transactions, the transaction receives an error indicating this. In this way, the transaction can rollback and retry only when the remote server does support serializable transactions. In contrast, read committed transactions can perform distributed transactions with servers that do not support serializable transactions.

Comparing Read Committed and Serializable Isolation

Oracle gives the application developer a choice of two transaction isolation levels with different characteristics. Both the read committed and serializable isolation levels provide a high degree of consistency and concurrency. Both levels provide the contention-reducing benefits of Oracle's "read consistency" multiversion concurrency control model and exclusive row-level locking implementation and are designed for real-world application deployment. The rest of this section compares the two isolation modes and provides information helpful in choosing between them.

A useful way to describe the read committed and serializable isolation levels in Oracle is to consider the following: a collection of database tables (or any set of data), a particular sequence of reads of rows in those tables, and the set of transactions committed at any particular time. An operation (a query or a transaction) is "transaction set consistent" if all its reads return data written by the same set of committed transactions. In an operation that is not transaction set consistent, some reads reflect the changes of one set of transactions, and other reads reflect changes made by other transactions. An operation that is not transaction set consistent in effect sees the database in a state that reflects no single set of committed transactions.

Oracle provides transactions executing in read committed mode with transaction set consistency on a per-statement basis. Serializable mode provides transaction set consistency on a per-transaction basis.

Differences Between Read Committed and Serializable Transactions

The table below summarizes key differences between read committed and serializable transactions in Oracle.

Read committed Serializable
Dirty write Not possible Not possible
Dirty read Not possible Not possible
Non-repeatable read Possible Not possible
Phantoms Possible Not possible
Compliant with ANSI/ISO SQL 92 Yes Yes
Read snapshot time Statement Transaction
Transaction set consistency Statement level Transaction level
Row-level locking Yes Yes
Readers block writers No No
Writers block readers No No
Different row-writers block writers No No
Same-row writers block writers Yes Yes
Waits for blocking transaction Yes Yes
Subject to "cannot serialize access" No Yes
Error after blocking transaction aborts No No
Error after blocking transaction commits No Yes

Choosing an Isolation Level

Application designers and developers should choose an isolation level based on application performance and consistency needs as well as application coding requirements.

For environments with many concurrent users rapidly submitting transactions, designers must assess transaction performance requirements in terms of the expected transaction arrival rate and response time demands. You should choose an isolation level that provides the required degree of consistency while satisfying performance expectations. Frequently, for high performance environments, the choice of isolation levels involves making a tradeoff between consistency and concurrency (transaction throughput).

Both Oracle isolation modes provide high levels of consistency and concurrency (and performance) through the combination of row-level locking and Oracle's multiversion concurrency control system. Because readers and writers don't block one another in Oracle, while queries still see consistent data, both read committed and serializable isolation provide a high level of concurrency for high performance, without the need for reading uncommitted ("dirty") data.

Read committed isolation can provide considerably more concurrency with a somewhat increased risk of inconsistent results (due to phantoms and non-repeatable reads) for some transactions. The serializable isolation level provides somewhat more consistency by protecting against phantoms and non-repeatable reads and may be important where a read/write transaction executes a query more than once. However, serializable mode requires applications to check for the "Cannot serialize access" error and can significantly reduce throughput in an environment with many concurrent transactions accessing the same data for update. Application logic that checks database consistency must take into account the fact reads don't block writes in either mode.

Choosing Read Committed Isolation

For many applications, read committed is the most appropriate isolation level. This is the isolation level used by applications running on Oracle releases previous to release 7.3.

Often, high performance environments with high transaction arrival rates require more throughput and faster response times than can be achieved with serializable isolation. On the other hand, an environment that supports few users with a very low transaction arrival rate faces exceedingly low risk of incorrect results due to phantoms and non-repeatable reads. Both of these environments are suitable for read committed isolation.

Oracle read committed isolation provides transaction set consistency for every query (that is, every query sees data in a consistent state). Therefore, read committed isolation will suffice for many applications that might require a higher degree of isolation if run on other database management systems that do not use multiversion concurrency control.

Read committed isolation mode does not require application logic to trap the "Cannot serialize access" error and loop back to restart a transaction. In most applications, few transactions have a functional need to re-issue the same query twice, so for many applications protection against phantoms and non-repeatable reads is not important. Therefore many developers choose read committed to avoid the need to write such error checking and retry code in each transaction.

Choosing Serializable Isolation

Oracle's serializable isolation mode is suitable for environments where there is relatively low chance that two concurrent transactions will modify the same rows and the relatively long-running transactions are primarily read only. It is most suitable for environments with large databases and short transactions that update only a few rows.

Unlike other implementations of serializable mode, which lock blocks for read as well as write, Oracle provides the benefit of non-blocking queries and the fine granularity of row-level locking. Oracle's row-level locking and non-blocking sequence generators also reduce write/write contention. For applications that experience mostly read/write contention, Oracle serializable mode can provide significantly more throughput than other systems. Therefore, some applications might be suitable for serializable mode on Oracle but not on other systems.

Because all queries in an Oracle serializable transaction see the database as of a single point in time, this mode is suitable where multiple consistent queries must be issued in a read-write transaction. A report-writing application that generates summary data and stores it in the database might use serializable mode because it provides the consistency that a READ ONLY transaction provides, but also allows INSERT, UPDATE and DELETE.

Coding serializable transactions requires extra work by the application developer (to check for the "Cannot serialize access" error and to rollback and retry the transaction). Similar extra coding is needed in other database management systems to manage deadlocks. For adherence to corporate standards or for applications that are run on multiple database management systems, it may be necessary to design transactions for serializable mode. Transactions that check for serializability failures and retry can be used with Oracle read committed mode (which does not generate serializability errors).

Serializable mode is probably not the best choice in an environment with relatively long transactions that must update the same rows accessed by a high volume of short update transactions. Because a longer running transaction is unlikely to be the first to modify a given row, it will repeatedly need to rollback, wasting work. Note that a conventional read-locking "pessimistic" implementation of serializable mode would not be suitable for this environment either, because long-running transactions (even read transactions) would block the progress of short update transactions and vice versa.

Application developers should take into account the cost of rolling back and retrying transactions when using serializable mode. As with read-locking systems where deadlocks frequently occur, use of serializable mode requires rolling back the work done by aborted transactions and retrying them. In a high contention environment, this activity can use significant resources.

In most environments, a transaction that restarts after receiving the "Cannot serialize access" error may be unlikely to encounter a second conflict with another transaction. For this reason it can help to execute those statements most likely to contend with other transactions as early as possible in a serializable transaction. However, there is no guarantee that the transaction will successfully complete, so the application should be coded to limit the number of retries.

Although Oracle serializable mode is compatible with SQL92 and offers many benefits as compared with read-locking implementations, it does not provide semantics identical to such systems. Application designers must take into account the fact that reads in Oracle do not block writes as they do in other systems. Transactions that check for database consistency at the application level may require coding techniques such as the use of SELECT FOR UPDATE. This issue should be considered when applications using serializable mode are ported to Oracle from other environments.


Contents Index Home Previous Next