Database Reference
In-Depth Information
Chapter 7
Concurrency and Multiversioning
As stated in the last chapter, one of the key challenges in developing multiuser, database-driven applications is
to maximize concurrent access but, at the same time, ensure that each user is able to read and modify the data in
a consistent fashion. In this chapter, we're going to take a detailed look at how Oracle achieves
multiversion read
consistency
and what that means to you, the developer. I will also introduce a new term,
write consistency
, and use it to
describe how Oracle works not only in a read environment with read consistency, but also in a mixed read and write
environment.
What Are Concurrency Controls?
Concurrency controls
are the collection of functions that the database provides to allow many people to access and
modify data simultaneously. As noted in the previous chapter, the
lock
is one of the core mechanisms by which Oracle
regulates concurrent access to shared database resources and prevents interference between concurrent database
transactions. To briefly summarize, Oracle uses a variety of locks, including the following:
•
TX (Transaction) locks
: These locks are acquired for the duration of a data-modifying
transaction.
•
TM (DML Enqueue) and DDL locks
: These locks ensure that the structure of an object is not
altered while you are modifying its contents (TM lock) or the object itself (DDL lock).
•
Latches and Mutexes
: These are internal locks that Oracle employs to mediate access to its
shared data structures. We'll refer to both as Latches in this chapter, although they might be
implemented by a Mutex on your operating system, depending on the Oracle version.
In each case, there is minimal overhead associated with lock acquisition. TX transaction locks are extremely
scalable both in terms of performance and cardinality. TM and DDL locks are applied in the least restrictive mode
whenever possible. Latches and enqueues are both very lightweight and fast (enqueues are slightly the heavier of the
two, though more feature-rich). Problems only arise from poorly designed applications that hold locks for
longer than
necessary
and cause blocking in the database. If you design your code well, Oracle's locking mechanisms will allow for
scalable, highly concurrent applications.
■
I used the phrase “longer than necessary.” That does not mean you should attempt to commit (end your
transaction) as soon as possible. Transactions should be exactly as long as they need to be—and no longer than that.
That is, your transaction is your unit of work; it is all or nothing. You should commit when your unit of work is complete
and not before—and not any later either!
Note
