[Greenwald99] Chapter 7 Multiuser Concurrency

Oracle Essentials: Oracle8 & Oracle8i
By Rick Greenwald, Robert Stackowiak & Jonathan Stern
1st Edition October 1999
1-56592-708-7, Order Number: 7087
374 pages, $34.95Chapter 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 handlemany different users with many different needs, problems can arise as aresult of the conflicting demands for concurrent access to the samedata.
Concurrent user access to the same data is one of themost central and vexing issues for applications utilizing databases.Concurrency can affect two of the most important facets of anyapplication: the underlying integrity of the data and the performanceof the application system.
As Ken Jacobs, Vice President at Oracle, puts it in hispaper entitled "Transaction Control and Oracle7," a multiuser databasemust be able to handle concurrently executing transactions in a waythat "ensure(s) predictable and reproducible results." This goal is thecore issue of data integrity, which, in turn, is the foundation of anydatabase system.
As multiple users access the same data, there is alwaysthe possibility that one user's changes to a specific piece of datawill be unwittingly overwritten by another user's changes. If thissituation occurs, the accuracy of the information in the database iscorrupted, which can render the data useless or, even worse,misleading. At the same time, the techniques used to prevent this typeof loss can dramatically reduce the performance of an applicationsystem, as users wait for other users to complete their work beforecontinuing. You can't solve this type of performance problem byincreasing the resources available to an application because it'scaused by the traffic visiting a piece of data, not by any lack ofhorsepower in the system that's handling the data.
Although concurrency issues are central to the successof applications, they are some of the most difficult problems topredict because they stem from such complex interactive situations. Thedifficulties posed by concurrent access continue to increase as thenumber of concurrent users increases. Even a robust debugging andtesting environment may fail to detect problems created by concurrentaccess since these problems are created by large numbers of users whomay not be available in a test environment. Concurrency problems canalso pop up as user access patterns change throughout the life of anapplication.
If the problems raised by concurrent access aren'tproperly handled by the underlying database software, developers mayfind themselves suffering in a number of ways. They will have to createtheir own customized solutions to these problems in their software,which will consume valuable development time. They will frequently findthemselves adding code during the late stages of development andtesting to work around the underlying deficiencies in the database,which can alter the design of the application. Worst of all, they mayfind themselves changing the optimal design of their data structures tocompensate for weaknesses in the capabilities of the underlyingdatabase.
There is only one way to deal successfully with theissues raised by concurrent data access. The database that provides theaccess must implement strategies to transparently overcome thepotential problems posed by concurrent access. Fortunately, Oracle hasexcellent methods for handling concurrent access.
This chapter describes the basics of concurrent dataaccess and gives you an overview of the way that Oracle handles theissues raised by concurrent access. If you've worked with largedatabase systems in the past and are familiar with concurrent useraccess, you might want to skip the first section of this chapter.
To prepare you to deal with the problems posed bymultiuser concurrent access to data, we should review some of the basicconcepts that relate to concurrency.
Transactions
The transaction is thebedrock of data integrity in multiuser databases, and the foundation ofall concurrency schemes. A transaction is defined as a singleindivisible piece of work that affects some data. All of themodifications made to data within a transaction are uniformly appliedto a database with a COMMIT statement, or the data affected by thechanges is uniformly returned to its initial state with a ROLLBACKstatement. Once a transaction is committed, the changes made by thattransaction become permanent and are made visible to other transactionsand other users.
Transactions always occur over time, although mosttransactions occur over a very short period of time. Since the changesmade by a transaction aren't official until the transaction iscommitted, each individual transaction must be isolated from theeffects of other transactions. The mechanism used to enforce transaction isolation is the lock.
Locks
A database uses a system of locksto prevent transactions from interfering with each other. Transactionscan interfere with each other by allowing one transaction to change apiece of data that another transaction is also in the process ofchanging.Figure 7-1illustrates a system without locks. Transaction A reads a piece ofdata; Transaction B reads the same piece of data and commits a changeto the data. When Transaction A goes to commit the data, its changeunwittingly overwrites the changes made by Transaction B, resulting ina loss of data integrity.
Figure 7-1. Transactions over time

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 datain the course of a transaction and released when the transaction isended by either a COMMIT or a ROLLBACK statement. A write lock can onlybe held by one user at a time, so only one user at a time can changethat data.
Some databases also use read locks , or shared locks .A read lock can be held by any number of users who are merely readingthe data, since the same piece of data can be shared among manyreaders. However, a read lock prevents a write lock from being placedon the data, as the write lock is an exclusive lock. InFigure 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 itwould be prevented from acquiring a write lock on the data untilTransaction A ended.
Oracle8 only uses read locks when a SQL operationspecifically requests them with the FOR UPDATE clause in a SELECTstatement. By default, you shouldn't use the FOR UPDATE clause becauseit unduly increases the probability that readers will interfere withwriters, a situation that normally never occurs with Oracle8, as youwill see shortly.
Concurrency and Contention
The safeguards that enforce isolation betweenconcurrent users of data can lead to their own problems if they'reimproperly implemented. As you can see from the example describedabove, a single transaction can cause significant performance problemsas the locks it places on the database prevent other transactions fromcompleting. 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 dataresults in increased contention and decreased performance, in terms ofboth response time and throughput.
Integrity Problems
There are some basic integrity problems that can resultif transaction isolation isn't enforced. Three of these problems arecommon to many databases:
Dirty reads
A dirty read occurs when a database allows atransaction to read data that has been changed by another transactionbut hasn't been committed yet. The changes made by the transaction maybe rolled back, so the data read may turn out to be incorrect. Manydatabases allow dirty reads to avoid the contention caused by readlocks.
Nonrepeatable reads
A nonrepeatable read occurs as a result ofchanges made by another transaction. A transaction makes a query basedon a particular condition. After the data has been returned to thefirst transaction, but before the first transaction is complete,another transaction changes the data so thatsome of the previously retrieved data no longer satisfies the selectioncondition. If the query was repeated in the same transaction, it wouldreturn a different set of results, so any changes made on the basis ofthe original results may no longer be valid. Data that was read oncemay return different results if it's read again later in the sametransaction.
Phantom reads
A phantom read also occurs as a result ofchanges made by another transaction. One transaction makes a querybased on a particular condition. After the data has been returned tothe first transaction, but before the first transaction is complete,another transaction inserts new rows into the database that would havebeen selected by the first transaction. If the transaction is makingchanges based on the assumption that the only rows that satisfied thecondition were returned, a phantom read could result in improper data.Although all the data read by the first query is returned for thesecond query, additional data also should have been returned, so anychanges made on the basis of the original results may no longer bevalid.
Serialization
The goal of a complete concurrency solution is toprovide the highest level of isolation between the actions of differentusers accessing the same data. As defined by the SQL92 standard, thishighest level is called serializable .[1]As the name implies, serializable transactions appear as though theyhave been executed in a series of distinct, ordered transactions. Whenone transaction begins, it's isolated from any changes that occur toits data from subsequent transactions.
To the user, a serializable transaction looks as thoughit has the exclusive use of the database for the duration of thetransaction. Serializable transactions are predictable andreproducible, the two cardinal virtues of data integrity.
Of course, it's not trivial to have a database serversupport 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 consistencyguarantees that a user sees a consistent view of the data he or sherequests. If another user changes the underlying data during the queryexecution, Oracle maintains a version of the data as it existed at thetime the query began. If there were transactions underway butuncommitted at the time the query began, Oracle will ensure that thequery neglects the changes made by those transactions. The datareturned to the query will reflect all committed transactions at thetime the query started.
This feature has two dramatic effects on the wayqueries impact the database. First, Oracle doesn't place any locks ondata for read operations. This means that a read operation will neverblock a write operation. Even if a database places a single lock on asingle row as part of a read operation, it can still cause contentionin the database, especially since most database tables tend toconcentrate update operations around a few "hot spots" of active data.
Second, a user gets a complete "snapshot" view of thedata, accurate at the point in time that the query began. Otherdatabases may reduce the amount of contention in the database bylocking an individual row only while it's being read, rather than overthe complete duration of the row's transaction. A row that's retrievedat the end of a result set may have been changed since the time theresult set retrieval began. Because rows that will be read later in theexecution of the query aren't locked, they could be changed by otherusers, which would result in an inconsistent view of data.
Oracle8's Isolation Levels
Oracle8, like many other databases, uses the concept of isolation levelsto describe how a transaction will interact with other transactions andhow a transaction will be isolated from other transactions. Anisolation level is essentially a locking scheme implemented by thedatabase that guarantees a certain type of transaction isolation.
An application programmer can set an isolation level atthe session or transaction level with a SQL statement, such as ALTERSESSION or SET TRANSACTION, respectively. Typically, a developer weighsthe potential conflicts that will result from a more restrictiveisolation level with the benefits that it will deliver in terms ofincreased protection against data integrity problems.
There are two basic isolation levels used frequentlywithin Oracle8: READ COMMITTED and SERIALIZABLE. Both of theseisolation levels create serializable database operations. Thedifference between the two levels is in the duration for which theyenforce serializable operations. (A third level, READ ONLY, isdescribed later in this section.)
READ COMMITTED
Enforces serialization at the statement level.This means that every statement will get a consistent view of the dataas it existed at the start of the statement. However, since atransaction can contain more than one statement, it's possible thatnonrepeatable reads and phantom reads can occur within the context ofthe complete transaction. The READ COMMITTED isolation level is thedefault isolation level for Oracle8.
SERIALIZABLE
Enforces serialization at the transactionlevel. This means that every statement within a transaction will getthe same consistent view of data as it existed at the start of thetransaction.
Because of their differing spans of control, these twoisolation levels also react differently when they encounter atransaction that blocks their operation with a lock on a requested row.Once the lock has been released by the blocking transaction, anoperation executing with the READ COMMITTED isolation level will simplyretry the operation. Since this operation is only concerned with thestate of data when the statement begins, this is a perfectly logicalapproach.
On the other hand, if the blocking transaction commitschanges to the data, an operation executing with a SERIALIZABLEisolation level will return an error indicating that it cannotserialize operations. This error makes sense, because the blockingtransaction will have changed the state of the data from the beginningof the SERIALIZABLE transaction, making it impossible to perform anymore write operations on the changed rows. In this situation, anapplication programmer will have to add logic to his or her program toreturn 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 byOracle8. You can declare that a session or transaction has an isolationlevel of READ ONLY. As the name implies, this level explicitly doesn'tallow any write operations, but the READ ONLY level provides anaccurate 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 Oracledatabase that store "undo" information for transactions in case ofrollback. This undo information restores database rows to the statethey were in before the transaction in question started. When atransaction starts changing some data in a block, it first writes theold image of the data to a rollback segment. The information stored ina rollback segment provides the information necessary to roll back atransaction and supports multiversion read consistency.
A rollback segment is different from aredo log. The redo log is used to log all transactions to the databaseand recovers the database in the event of a system failure, while therollback segment provides rollback for transactions and readconsistency.
Blocks of rollback segments are cached inthe SGA just like blocks of tables and indexes. If rollback segmentblocks are unused for a period of time, they may be aged out of thecache and written to disk.
System Change Number (SCN)
To preserve the integrity of the data in thedatabase, it's critical to keep track of the order in which actionswere performed. Oracle8 must preserve the ordering of transactions withrespect to time. The mechanism Oracle uses is the System Change Number.
The SCN is a logical timestamp that tracksthe order in which events occurred. Oracle8 uses the SCN information inthe redo log to reproduce transactions in the original and correctorder when applying redo. Oracle8 also uses the SCN to determine whento 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 aparticular row is locked. Most databases keep a list of locks inmemory, which are managed by a lock manager process. Oracle8 keepslocks 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 diskfor an Oracle database, so whenever the row is requested, the block isread, and the lock is available within the block. Although the lockindicators are kept within a block, each lock only affects anindividual row within the block.
In addition to the above features, which directlypertain to multiversion read consistency, another implementationfeature in Oracle provides a greater level of concurrency in large userpopulations:
Nonescalating row locks
To reduce the overhead of the lock management process, other databases will sometimes escalatelocks to a higher level of granularity within the database. Forexample, if a certain percentage of rows in a table are locked, thedatabase will escalate the lock to a table lock, which locks all therows in a table including rows that aren't specifically used by the SQLstatement in question. Although lock escalation reduces the number oflocks the lock manager process has to handle, it causes unaffected rowsto 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 LockManager (DLM) that's used with Oracle Parallel Server to track locksacross multiple instances of Oracle. This is a completely different andseparate locking scheme that doesn't affect the way Oracle handles rowlocks. 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 thefeatures of Oracle8 to understand how the database handles issuesrelated to multiuser access. However, to make it perfectly clear howthese features interact, we'll walk you through three scenarios: asimple write to the database, a situation in which two users attempt towrite to the same row in the same table, and a read that takes place inthe midst of conflicting updates.
For the purposes of these examples, we'll use thescenario of one or two users modifying the EMP table, a part of thestandard sample Oracle schema that lists data about employees via aform.
A Simple Write Operation
This example describes a simple write operation, inwhich one user is writing to a row in the database. In example, an HRclerk wants to update the name for an employee. Assume that the HRclerk already has the employee record on-screen. The steps from thispoint are as follows:
The client modifies the employee name on the screen. The client process sends a SQL UPDATE statement over the network to the server process.
The server process obtains a System Change Number (SCN) and reads the data block containing the target row.
The server records row lock information in the data block.
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.
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.
The HR clerk commits the transaction.
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.
The server process sends a message to the client confirming the commit.
A Conflicting Write Operation
The write operation previously described is a littledifferent if there are two users, client A and client B, who are tryingto modify the same row of data at the same time. The steps are asfollows:
Client A modifies the employee name on the screen. Client A sends a SQL UPDATE statement over the network to the server process.
The server process obtains an SCN for the statement and reads the data block containing the target row.
The server records row lock information to the data block.
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.
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.
Client B modifies the employee name on the screen and sends a SQL UPDATE statement to the server.
The server process obtains an SCN and reads the data block containing the target row.
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.
Client A commits the transaction, the server process takes the appropriate action, and the server sends a message to client A confirming the commit.
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 behaviorof Oracle8 when it detects a problem caused by a potential lost update.Because the SERIALIZABLE isolation level has a more drastic consequencethan the READ COMMITTED isolation level, many developers prefer thelatter level. They can avoid some of the potential conflicts by eitherchecking for changes prior to issuing an update or by using the SELECTFOR UPDATE syntax in their SQL to avoid the problem altogether.
A Read Operation
By looking at how a user reads data from the table, youcan appreciate the beauty of Oracle8's read consistency model. In thisscenario, 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 afterClient A begins his transaction. The following steps are graphicallyillustrated inFigure 7-2.
Client A sends a SQL SELECT statement over the network to the server process.
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.
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.
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.
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.
Figure 7-2. Multiversion read consistency

Concurrent Access and Performance
When you read through all the steps involved in theabove processes, it seems as if Oracle8 would be a very slow database.This is not at all true. Oracle8 has consistently turned in benchmarksthat make it one of the fastest databases, if not the fastest, on themarket today.
Oracle8 provides good performance while implementingmultversion read consistency by minimizing and deferring unnecessaryI/O operations. To assure the integrity of the data in a database, thedatabase must be able to recover in the event of a system failure. Thismeans that there must be a way to ensure that the data in the databaseaccurately reflects the state of the committed data at the time of thecrash. Oracle8 can do this by writing changed data to the databasewhenever a transaction commits. However, the redo log contains muchless information than the entire data block for the changed data, soit's much "cheaper" to write to disk. Oracle8 writes the redoinformation to disk as soon as a transaction commits, and deferswriting the changed data blocks to the database until several sets ofchanged blocks can be written together. Oracle8 can restore thedatabase using the redo logs, and Oracle8 cuts down on time-consumingI/O operations.
But when you're considering the performance of adatabase, you have to think about more than simple I/O operations. Itdoesn't really matter how fast your database runs if your transactionis waiting for another transaction to release a lock. A faster databasemay complete the blocking transaction faster, but your transaction isstill at a dead stop until the blocking transaction completes. Becausemost databases perform a mixture of reading and writing, and becauseOracle8 is the only database on the market that doesn't use read locks,Oracle8 will essentially always deliver the lowest amount of databasecontention. Less contention equals greater throughput for a mixedapplication 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. BecauseOracle8 provides much less contention with its read consistency model,developers have to spend less time adding workarounds to theirapplications to handle the results of contention.
It's not as though Oracle is the only database to giveyou a concurrency solution, which you can use to implement applicationsthat provide adequate data integrity. But the multiversion readconsistency model makes it easy for you to get a consistent view ofdata without excessive contention and without having to writeworkarounds in your application. If it sounds as if we're big fans ofOracle'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.
Back to:Oracle Essentials: Oracle8 & Oracle8i
oreilly.com Home |O'Reilly Bookstores |How to Order |O'Reilly ContactsInternational |About O'Reilly |Affiliated Companies |Privacy Policy
© 2001, O'Reilly & Associates, Inc.