Databases Reference
In-Depth Information
CHAPTER 8
Oracle Multiuser Concurrency
Sharing data is at the center of all information systems. As systems provide higher and
higher levels of functionality, we can sometimes forget that the ability to efficiently share
data is the underlying governor of overall system performance. At the same time, da‐
tabase systems must protect the integrity of the data, as the value of that data is directly
proportional to the correctness of the data. Database systems must protect data integrity,
while still providing high levels of performance for multiuser access. These two forces
sometimes conflict and shape some of the core technology in any database system.
Data integrity must always come first. As Ken Jacobs, fondly known as Dr. DBA in the
Oracle community, put it in his classic 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.
When 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
compromised, and that 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. These techniques act like a traffic signal, so you can't solve this
type of performance problem by increasing the resources available to the database. The
problem isn't due to a lack of horsepower—it's caused by a red light.
Although concurrency issues are central to the success of applications, they are some
of the most difficult problems to predict because they can stem from such complex
interactive situations. The difficulties posed by concurrent access 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
