Oracle Essentials; Oracle8 and Oracle8i

Chapter 7: Multiuser Concurrency

In this chapter:
Basics of Concurrent Access
Oracle and Concurrent User Access
Oracle8's Isolation Levels
Oracle8 Concurrency Features
How Oracle8 Handles SQL Operations
Concurrent Access and Performance

All information systems fulfill a single purpose: to collect, store, and retrieve information. As systems grow to handle many different users with many different needs, problems can arise as a result of the conflicting demands for concurrent access to the same data.

Concurrent user access to the same data is one of the most central and vexing issues for applications utilizing databases. Concurrency can affect two of the most important facets of any application: the underlying integrity of the data and the performance of the application system.

As Ken Jacobs, Vice President at Oracle, puts it in his paper entitled "Transaction Control and Oracle7," a multiuser database must be able to handle concurrently executing transactions in a way that "ensure(s) predictable and reproducible results." This goal is the core issue of data integrity, which, in turn, is the foundation of any database system.

As multiple users access the same data, there is always the possibility that one user's changes to a specific piece of data will be unwittingly overwritten by another user's changes. If this situation occurs, the accuracy of the information in the database is corrupted, which can render the data useless or, even worse, misleading. At the same time, the techniques used to prevent this type of loss can dramatically reduce the performance of an application system, as users wait for other users to complete their work before continuing. You can't solve this type of performance problem by increasing the resources available to an application because it's caused by the traffic visiting a piece of data, not by any lack of horsepower in the system that's handling the data.

Although concurrency issues are central to the success of applications, they are some of the most difficult problems to predict because they stem from such complex interactive situations. The difficulties posed by concurrent access continue to increase as the number of concurrent users increases. Even a robust debugging and testing environment may fail to detect problems created by concurrent access since these problems are created by large numbers of users who may not be available in a test environment. Concurrency problems can also pop up as user access patterns change throughout the life of an application.

If the problems raised by concurrent access aren't properly handled by the underlying database software, developers may find themselves suffering in a number of ways. They will have to create their own customized solutions to these problems in their software, which will consume valuable development time. They will frequently find themselves adding code during the late stages of development and testing to work around the underlying deficiencies in the database, which can alter the design of the application. Worst of all, they may find themselves changing the optimal design of their data structures to compensate for weaknesses in the capabilities of the underlying database.

There is only one way to deal successfully with the issues raised by concurrent data access. The database that provides the access must implement strategies to transparently overcome the potential problems posed by concurrent access. Fortunately, Oracle has excellent methods for handling concurrent access.

This chapter describes the basics of concurrent data access and gives you an overview of the way that Oracle handles the issues raised by concurrent access. If you've worked with large database systems in the past and are familiar with concurrent user access, you might want to skip the first section of this chapter.

Basics of Concurrent Access

To prepare you to deal with the problems posed by multiuser concurrent access to data, we should review some of the basic concepts that relate to concurrency.

Transactions

The transaction is the bedrock of data integrity in multiuser databases, and the foundation of all concurrency schemes. A transaction is defined as a single indivisible piece of work that affects some data. All of the modifications made to data within a transaction are uniformly applied to a database with a COMMIT statement, or the data affected by the changes is uniformly returned to its initial state with a ROLLBACK statement. Once a transaction is committed, the changes made by that transaction become permanent and are made visible to other transactions and other users.

Transactions always occur over time, although most transactions occur over a very short period of time. Since the changes made by a transaction aren't official until the transaction is committed, each individual transaction must be isolated from the effects of other transactions. The mechanism used to enforce transaction isolation is the lock.

Locks

A database uses a system of locks to prevent transactions from interfering with each other. Transactions can interfere with each other by allowing one transaction to change a piece of data that another transaction is also in the process of changing. Figure 7-1illustrates a system without locks. Transaction A reads a piece of data; Transaction B reads the same piece of data and commits a change to the data. When Transaction A goes to commit the data, its change unwittingly overwrites the changes made by Transaction B, resulting in a loss of data integrity.

There are two types of locks used to avoid this type of problem. The first is called a write lock, or an exclusive lock. An exclusive lock is taken out and held while changes are made to data in the course of a transaction and released when the transaction is ended by either a COMMIT or a ROLLBACK statement. A write lock can only be held by one user at a time, so only one user at a time can change that data.

Some databases also use read locks, or shared locks. A read lock can be held by any number of users who are merely reading the data, since the same piece of data can be shared among many readers. However, a read lock prevents a write lock from being placed on the data, as the write lock is an exclusive lock. In Figure 7-1, if a read lock were placed on the data when Transaction A began, Transaction B would not be prevented from reading the same data, but it would be prevented from acquiring a write lock on the data until Transaction A ended.

Oracle8 only uses read locks when a SQL operation specifically requests them with the FOR UPDATE clause in a SELECT statement. By default, you shouldn't use the FOR UPDATE clause because it unduly increases the probability that readers will interfere with writers, a situation that normally never occurs with Oracle8, as you will see shortly.

Concurrency and Contention

The safeguards that enforce isolation between concurrent users of data can lead to their own problems if they're improperly implemented. As you can see from the example described above, a single transaction can cause significant performance problems as the locks it places on the database prevent other transactions from completing. The interference caused by conflicting locks is called contention. The more contention in a database the greater the potential response times.

In most databases, increased concurrent access to data results in increased contention and decreased performance, in terms of both response time and throughput.

Integrity Problems

There are some basic integrity problems that can result if transaction isolation isn't enforced. Three of these problems are common to many databases:

Dirty reads

A dirty read occurs when a database allows a transaction to read data that has been changed by another transaction but hasn't been committed yet. The changes made by the transaction may be rolled back, so the data read may turn out to be incorrect. Many databases allow dirty reads to avoid the contention caused by read locks.

Nonrepeatable reads

A nonrepeatable read occurs as a result of changes made by another transaction. A transaction makes a query based on a particular condition. After the data has been returned to the first transaction, but before the first transaction is complete, another transaction changes the data so that some of the previously retrieved data no longer satisfies the selection condition. If the query was repeated in the same transaction, it would return a different set of results, so any changes made on the basis of the original results may no longer be valid. Data that was read once may return different results if it's read again later in the same transaction.

Phantom reads

A phantom read also occurs as a result of changes made by another transaction. One transaction makes a query based on a particular condition. After the data has been returned to the first transaction, but before the first transaction is complete, another transaction inserts new rows into the database that would have been selected by the first transaction. If the transaction is making changes based on the assumption that the only rows that satisfied the condition were returned, a phantom read could result in improper data. Although all the data read by the first query is returned for the second query, additional data also should have been returned, so any changes made on the basis of the original results may no longer be valid.

Serialization

The goal of a complete concurrency solution is to provide the highest level of isolation between the actions of different users accessing the same data. As defined by the SQL92 standard, this highest level is called serializable.[1] As the name implies, serializable transactions appear as though they have been executed in a series of distinct, ordered transactions. When one transaction begins, it's isolated from any changes that occur to its data from subsequent transactions.

To the user, a serializable transaction looks as though it has the exclusive use of the database for the duration of the transaction. Serializable transactions are predictable and reproducible, the two cardinal virtues of data integrity.

Of course, it's not trivial to have a database server support thousands of users while each one thinks he is the only one. But Oracle silently manages to pull off this dramatic feat.

Oracle and Concurrent User Access

Oracle solves the problems created by concurrent access through a technology called multiversion read consistency.

Multiversion read consistency guarantees that a user sees a consistent view of the data he or she requests. If another user changes the underlying data during the query execution, Oracle maintains a version of the data as it existed at the time the query began. If there were transactions underway but uncommitted at the time the query began, Oracle will ensure that the query neglects the changes made by those transactions. The data returned to the query will reflect all committed transactions at the time the query started.

This feature has two dramatic effects on the way queries impact the database. First, Oracle doesn't place any locks on data for read operations. This means that a read operation will never block a write operation. Even if a database places a single lock on a single row as part of a read operation, it can still cause contention in the database, especially since most database tables tend to concentrate update operations around a few "hot spots" of active data.

Second, a user gets a complete "snapshot" view of the data, accurate at the point in time that the query began. Other databases may reduce the amount of contention in the database by locking an individual row only while it's being read, rather than over the complete duration of the row's transaction. A row that's retrieved at the end of a result set may have been changed since the time the result set retrieval began. Because rows that will be read later in the execution of the query aren't locked, they could be changed by other users, which would result in an inconsistent view of data.

Oracle8's Isolation Levels

Oracle8, like many other databases, uses the concept of isolation levels to describe how a transaction will interact with other transactions and how a transaction will be isolated from other transactions. An isolation level is essentially a locking scheme implemented by the database that guarantees a certain type of transaction isolation.

An application programmer can set an isolation level at the session or transaction level with a SQL statement, such as ALTER SESSION or SET TRANSACTION, respectively. Typically, a developer weighs the potential conflicts that will result from a more restrictive isolation level with the benefits that it will deliver in terms of increased protection against data integrity problems.

There are two basic isolation levels used frequently within Oracle8: READ COMMITTED and SERIALIZABLE. Both of these isolation levels create serializable database operations. The difference between the two levels is in the duration for which they enforce serializable operations. (A third level, READ ONLY, is described later in this section.)

READ COMMITTED

Enforces serialization at the statement level. This means that every statement will get a consistent view of the data as it existed at the start of the statement. However, since a transaction can contain more than one statement, it's possible that nonrepeatable reads and phantom reads can occur within the context of the complete transaction. The READ COMMITTED isolation level is the default isolation level for Oracle8.

SERIALIZABLE

Enforces serialization at the transaction level. This means that every statement within a transaction will get the same consistent view of data as it existed at the start of the transaction.

Because of their differing spans of control, these two isolation levels also react differently when they encounter a transaction that blocks their operation with a lock on a requested row. Once the lock has been released by the blocking transaction, an operation executing with the READ COMMITTED isolation level will simply retry the operation. Since this operation is only concerned with the state of data when the statement begins, this is a perfectly logical approach.

On the other hand, if the blocking transaction commits changes to the data, an operation executing with a SERIALIZABLE isolation level will return an error indicating that it cannot serialize operations. This error makes sense, because the blocking transaction will have changed the state of the data from the beginning of the SERIALIZABLE transaction, making it impossible to perform any more write operations on the changed rows. In this situation, an application programmer will have to add logic to his or her program to return to the start of the SERIALIZABLE transaction and begin it again. There are step-by-step examples of concurrent access later this chapter (in the "Concurrent Access and Performance" section), which illustrate the different ways that Oracle responds to this type of problem.

There is one other isolation level supported by Oracle8. You can declare that a session or transaction has an isolation level of READ ONLY. As the name implies, this level explicitly doesn't allow any write operations, but the READ ONLY level provides an accurate view of all the data at the time the transaction began.

Oracle8 Concurrency Features

There are three features used by Oracle8 to implement multiversion read consistency:

Rollback segments

Rollback segments are structures in the Oracle database that store "undo" information for transactions in case of rollback. This undo information restores database rows to the state they were in before the transaction in question started. When a transaction starts changing some data in a block, it first writes the old image of the data to a rollback segment. The information stored in a rollback segment provides the information necessary to roll back a transaction and supports multiversion read consistency.

A rollback segment is different from a redo log. The redo log is used to log all transactions to the database and recovers the database in the event of a system failure, while the rollback segment provides rollback for transactions and read consistency.

Blocks of rollback segments are cached in the SGA just like blocks of tables and indexes. If rollback segment blocks are unused for a period of time, they may be aged out of the cache and written to disk.

System Change Number (SCN)

To preserve the integrity of the data in the database, it's critical to keep track of the order in which actions were performed. Oracle8 must preserve the ordering of transactions with respect to time. The mechanism Oracle uses is the System Change Number.

The SCN is a logical timestamp that tracks the order in which events occurred. Oracle8 uses the SCN information in the redo log to reproduce transactions in the original and correct order when applying redo. Oracle8 also uses the SCN to determine when to clean up information in rollback segments that's no longer needed, as you will see in the following sections.

Locks in data blocks

A database must have a way of determining if a particular row is locked. Most databases keep a list of locks in memory, which are managed by a lock manager process. Oracle8 keeps locks with an indicator in the actual block in which the row is stored. A data block is the smallest amount of data that can be read from disk for an Oracle database, so whenever the row is requested, the block is read, and the lock is available within the block. Although the lock indicators are kept within a block, each lock only affects an individual row within the block.

In addition to the above features, which directly pertain to multiversion read consistency, another implementation feature in Oracle provides a greater level of concurrency in large user populations:

Nonescalating row locks

To reduce the overhead of the lock management process, other databases will sometimes escalate locks to a higher level of granularity within the database. For example, if a certain percentage of rows in a table are locked, the database will escalate the lock to a table lock, which locks all the rows in a table including rows that aren't specifically used by the SQL statement in question. Although lock escalation reduces the number of locks the lock manager process has to handle, it causes unaffected rows to be locked. Because each row's lock is kept within its data block, there is no need for Oracle8 to escalate a lock so it never does.

There is a lock manager called the Distributed Lock Manager (DLM) that's used with Oracle Parallel Server to track locks across multiple instances of Oracle. This is a completely different and separate locking scheme that doesn't affect the way Oracle handles row locks. The DLM is briefly described in Chapter 8.

How Oracle8 Handles SQL Operations

If you've been reading this chapter from the beginning, you should now know enough about the concepts of concurrency and the features of Oracle8 to understand how the database handles issues related to multiuser access. However, to make it perfectly clear how these features interact, we'll walk you through three scenarios: a simple write to the database, a situation in which two users attempt to write to the same row in the same table, and a read that takes place in the midst of conflicting updates.

For the purposes of these examples, we'll use the scenario of one or two users modifying the EMP table, a part of the standard sample Oracle schema that lists data about employees via a form.

A Simple Write Operation

This example describes a simple write operation, in which one user is writing to a row in the database. In example, an HR clerk wants to update the name for an employee. Assume that the HR clerk already has the employee record on-screen. The steps from this point are as follows:

1. The client modifies the employee name on the screen. The client process sends a SQL UPDATE statement over the network to the server process.
2. The server process obtains a System Change Number (SCN) and reads the data block containing the target row.
3. The server records row lock information in the data block.
4. The server process copies the old image of the employee data about to be changed to a rollback segment and then modifies the employee data.
5. The server process records the changes to the rollback segment and the database block in the redo log buffer in the SGA. The rollback segment changes are part of the redo, since the redo log stores all changes resulting from the transaction.
6. The HR clerk commits the transaction.
7. Log Writer (LGWR) writes the redo information for the entire transaction, including the SCN that marks the time the transaction was committed, from the redo log buffer to the current redo log file on disk. When the operating system confirms that the write to the redo log file has successfully completed, the transaction is considered committed.
8. The server process sends a message to the client confirming the commit.

A Conflicting Write Operation

The write operation previously described is a little different if there are two users, client A and client B, who are trying to modify the same row of data at the same time. The steps are as follows:

1. Client A modifies the employee name on the screen. Client A sends a SQL UPDATE statement over the network to the server process.
2. The server process obtains an SCN for the statement and reads the data block containing the target row.
3. The server records row lock information to the data block.
4. The server process then copies the old image of the employee data about to be changed to a rollback segment. Once the server process has written the old employee data to a rollback segment, the server process modifies the cached database block to change the employee name.
5. The server process records the changes to the rollback segment and the database block in the redo log buffer in the SGA. The rollback segment changes are part of the redo, since the redo log stores all changes resulting from the transaction.
6. Client B modifies the employee name on the screen and sends a SQL UPDATE statement to the server.
7. The server process obtains an SCN and reads the data block containing the target row.
8. The server process sees that there is a lock on the target row from the information in the header of the data block, so it waits for the blocking transaction to complete. If Client B executed the SQL statement with the SERIALIZABLE isolation level, an error is returned to the client.
9. Client A commits the transaction, the server process takes the appropriate action, and the server sends a message to client A confirming the commit.
10. If Client B executed the SQL statement with the READ COMMITTED isolation level, the SQL statement then proceeds through its normal operation.

The previous example illustrates the default behavior of Oracle8 when it detects a problem caused by a potential lost update. Because the SERIALIZABLE isolation level has a more drastic consequence than the READ COMMITTED isolation level, many developers prefer the latter level. They can avoid some of the potential conflicts by either checking for changes prior to issuing an update or by using the SELECT FOR UPDATE syntax in their SQL to avoid the problem altogether.

A Read Operation

By looking at how a user reads data from the table, you can appreciate the beauty of Oracle8's read consistency model. In this scenario, Client A is reading a series of rows from the EMP table, while Client B modifies a row before it's read by Client A, but after Client A begins his transaction. The following steps are graphically illustrated in Figure 7-2.

1. Client A sends a SQL SELECT statement over the network to the server process.
2. The server process obtains an SCN for the statement and begins to read the requested data for the query. For each data block that it reads, it compares the SCN that timestamps the SELECT statement with the SCNs for any transactions that were active in the data block. Wherever it detects changes that were uncommitted as of the SCN of the SELECT statement, the server process uses data in the rollback segments to create a "consistent read" version of the data block, current as of the time the SELECT was issued. This is what provides the multiversion read consistency.
3. Client B sends a SQL UPDATE statement for a row in the EMP table that has not yet been read by Client A's SELECT statement. The server process gets an SCN for the statement and begins the operation.
4. Client B commits his changes. The server process completes the operation, which includes recording information in the data block that contained the modified row that allows Oracle to determine the SCN that indicated when the update transaction was committed.
5. The server process for Client A's read operation comes to the newly modified block. It sees that the data block contains changes made by a transaction that has an SCN that's later than the SCN of the SELECT statement. The server process looks in the data block header, which has a pointer to the rollback segment that contains the data as it existed when Client A's transaction started. The rollback segment uses the old version of the data to create a version of the block as it existed when the SELECT statement started. Client A's SELECT statement reads the desired rows from this consistent version of the data block.

Figure 7-2 illustrates the process of reading with multiversion read consistency.

Concurrent Access and Performance

When you read through all the steps involved in the above processes, it seems as if Oracle8 would be a very slow database. This is not at all true. Oracle8 has consistently turned in benchmarks that make it one of the fastest databases, if not the fastest, on the market today.

Oracle8 provides good performance while implementing multversion read consistency by minimizing and deferring unnecessary I/O operations. To assure the integrity of the data in a database, the database must be able to recover in the event of a system failure. This means that there must be a way to ensure that the data in the database accurately reflects the state of the committed data at the time of the crash. Oracle8 can do this by writing changed data to the database whenever a transaction commits. However, the redo log contains much less information than the entire data block for the changed data, so it's much "cheaper" to write to disk. Oracle8 writes the redo information to disk as soon as a transaction commits, and defers writing the changed data blocks to the database until several sets of changed blocks can be written together. Oracle8 can restore the database using the redo logs, and Oracle8 cuts down on time-consuming I/O operations.

But when you're considering the performance of a database, you have to think about more than simple I/O operations. It doesn't really matter how fast your database runs if your transaction is waiting for another transaction to release a lock. A faster database may complete the blocking transaction faster, but your transaction is still at a dead stop until the blocking transaction completes. Because most databases perform a mixture of reading and writing, and because Oracle8 is the only database on the market that doesn't use read locks, Oracle8 will essentially always deliver the lowest amount of database contention. Less contention equals greater throughput for a mixed application load.

There is also more than one type of performance. Performance for database operations is measured in milliseconds; performance for application developers is measured in months. Because Oracle8 provides much less contention with its read consistency model, developers have to spend less time adding workarounds to their applications to handle the results of contention.

It's not as though Oracle is the only database to give you a concurrency solution, which you can use to implement applications that provide adequate data integrity. But the multiversion read consistency model makes it easy for you to get a consistent view of data without excessive contention and without having to write workarounds in your application. If it sounds as if we're big fans of Oracle's locking scheme, well--we are.

---

1. The SQL92 standard is the most recent version of the SQL standard as defined by the ANSI committee.