Database Reference
In-Depth Information
Chapter 6
Locking and Latching
One of the key challenges in developing multiuser, database-driven applications is to maximize concurrent access
and, at the same time, ensure that each user is able to read and modify the data in a consistent fashion. The locking
mechanisms that allow this to happen are key features of any database, and Oracle excels in providing them.
However, Oracle's implementation of these features is specific to Oracle—just as SQL Server's implementation is
to SQL Server—and it is up to you, the application developer, to ensure that when your application performs data
manipulation, it uses these mechanisms correctly. If you fail to do so, your application will behave in an unexpected
way, and inevitably the integrity of your data will be compromised (as was demonstrated in Chapter 1).
In this chapter, we'll take a detailed look at how Oracle locks both data (e.g., rows in tables) and shared data
structures (such as those found in the SGA). We'll investigate the granularity to which Oracle locks data and what
that means to you, the developer. When appropriate, I'll contrast Oracle's locking scheme with other popular
implementations, mostly to dispel the myth that row-level locking adds overhead; in reality, it adds overhead only
if the implementation adds overhead. In the next chapter, we'll continue this discussion and investigate Oracle's
multiversioning techniques and how locking strategies interact with them.
What Are Locks?
Locks
are mechanisms used to regulate concurrent access to a shared resource. Note how I used the term “shared
resource” and not “database row.” It is true that Oracle locks table data at the row level, but it also uses locks at many
other levels to provide concurrent access to various resources. For example, while a stored procedure is executing,
the procedure itself is locked in a mode that allows others to execute it, but it will not permit another user to alter that
instance of that stored procedure in any way. Locks are used in the database to permit concurrent access to these
shared resources, while at the same time providing data integrity and consistency.
In a single-user database, locks are not necessary. There is, by definition, only one user modifying the
information. However, when multiple users are accessing and modifying data or data structures, it is crucial to have a
mechanism in place to prevent concurrent modification of the same piece of information. This is what locking is
all about.
It is very important to understand that there are as many ways to implement locking in a database as there
are databases. Just because you have experience with the locking model of one particular relational database
management system (RDBMS) does not mean you know everything about locking. For example, before I got heavily
involved with Oracle, I used other databases including Sybase, Microsoft SQL Server, and Informix. All three of these
databases provide locking mechanisms for concurrency control, but there are deep and fundamental differences in
the way locking is implemented in each one.
To demonstrate this, I'll outline my progression from a Sybase SQL Server developer to an Informix user and
finally to an Oracle developer. This happened many years ago, and the SQL Server fans out there will tell me “But we
have row-level locking now!” It is true: SQL Server may now use row-level locking, but the way it is implemented is
totally different from the way it is done in Oracle. It is a comparison between apples and oranges, and that is the
key point.
