Oracle7 Server Concepts

Data Concurrency in a Multi-user Environment

In a single-user database, the user can modify data in the database without concern for other users modifying the same data at the same time. However, in a multi-user database, the statements within multiple simultaneous transactions can update the same data. Transactions executing at the same time need to produce meaningful and consistent results. Therefore, control of data concurrency and data consistency is vital in a multi-user database. These concepts are defined here:

data concurrency Many users can access data at the same time.
data consistency Users should see a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.

To describe consistent transaction behavior when transactions execute at the same time, database researchers have defined a transaction isolation model called serializability. The serializable mode of transaction behavior tries to ensure that transactions execute in such a way that they appear to be executed one at a time, or serially, rather than concurrently.

While this degree of isolation between transactions is generally desirable, running many applications in this mode can seriously compromise the application throughput. Complete isolation of concurrently running transactions could mean that one transaction could not do an insert into a table that was being queried by another. In short, real world considerations usually make it necessary to choose a compromise between perfect transaction isolation and performance.

Oracle offers two isolation levels, providing application developers with operational modes that preserve consistency and provide high performance.

General Concurrency Issues

The ANSI/ISO SQL standard (SQL92) defines several levels of transaction isolation with differing degrees of impact on transaction processing throughput. These isolation levels are defined in terms of phenomena that must be prevented between concurrently executing transactions.

Preventable Phenomena

The SQL standard defines three phenomena and four levels of isolation that provide protection against the phenomena. The three preventable phenomena are defined as:

dirty reads A transaction reads data that has been written by a transaction that has not been committed yet.
non-repeatable (fuzzy) reads A transaction re-reads data it has previously read and finds that another committed transaction has modified or deleted the data.
phantom read A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

Isolation Levels

The SQL standard defines four levels of isolation in terms of the phenomena a transaction running at a particular isolation level is permitted to experience.

Isolation Level Dirty Read Non-Repeatable Read Phantom Read
Read uncommitted Possible Possible Possible
Read committed Not possible Possible Possible
Repeatable read Not possible Not possible Possible
Serializable Not possible Not possible Not possible

Oracle offers the read committed and serializable isolation levels. Read committed is the default and was the only automatic isolation level provided before Release 7.3.

Locking Mechanisms

In general, multi-user databases use some form of data locking to solve the problems associated with data concurrency, integrity, and consistency. Locks are mechanisms used to prevent destructive interaction between users accessing the same resource.

Resources include two general types of objects:

user objects, such as tables and rows (structures and data)

system objects not visible to users, such as shared data structures in the memory and data dictionary rows

Restrictiveness of Locks

In general, you can use two levels of locking in a multi-user database:

exclusive locks An exclusive lock prevents the associated resource from being shared and are obtained to modify data. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released.
share locks A share lock allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who holds an exclusive lock). Several transactions can acquire share locks on the same resource.

Deadlocks

A deadlock is a situation that can occur in multi-user systems that prevents some transactions from continuing work. A deadlock can occur when two or more users are waiting for data locked by each other. Figure 10 - 1 illustrates two transactions in a deadlock.

Figure 10 - 1. Two Transactions in a Deadlock

In Figure 10 - 1, no problem exists at time point A, as each transaction has a row lock on the row it attempts to update. Each transaction proceeds (without being terminated). However, each tries to update the row currently held by the other transaction. Therefore, a deadlock results at time point B, because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock because no matter how long each transaction waits, the conflicting locks are held.

Lock Escalation

Lock escalation occurs when numerous locks are held at one level and the database automatically changes the locks to different locks at a higher level. For example, if a single user locks many rows in a table, the database might automatically escalate the user's row locks to a single table lock. With this plan, the number of locks has been reduced, but the restrictiveness of what is being locked has increased.

Lock escalation greatly increases the likelihood of deadlocks. For example, imagine the situation where the system is trying to escalate locks on behalf of transaction T1 but cannot because of the locks held by transaction T2. A deadlock is created if transaction T2 also requires lock escalation before it can proceed, since the escalator is devoted to T1.

Note: Oracle never escalates locks.

data concurrency	Many users can access data at the same time.
data consistency	Users should see a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.

dirty reads	A transaction reads data that has been written by a transaction that has not been committed yet.
non-repeatable (fuzzy) reads	A transaction re-reads data it has previously read and finds that another committed transaction has modified or deleted the data.
phantom read	A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

exclusive locks	An exclusive lock prevents the associated resource from being shared and are obtained to modify data. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released.
share locks	A share lock allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who holds an exclusive lock). Several transactions can acquire share locks on the same resource.