Database Reference
In-Depth Information
Notice I don't say, “you might...” or “you run the risk of...” but rather that invariably you
will
do these things.
You will do these things without even realizing it. Without correct concurrency control, you will corrupt the integrity
of your database because something that works in isolation will not work as you expect in a multiuser situation.
Your application will run slower than it should because you'll end up waiting for data. Your application will lose its
ability to scale because of locking and contention issues. As the queues to access a resource get longer, the wait gets
longer and longer.
An analogy here would be a backup at a tollbooth. If cars arrive in an orderly, predictable fashion, one after the
other, there won't ever be a backup. If many cars arrive simultaneously, queues start to form. Furthermore, the waiting
time does not increase linearly with the number of cars at the booth. After a certain point, considerable additional
time is spent “managing” the people who are waiting in line, as well as servicing them (the parallel in the database
would be context switching).
Concurrency issues are the hardest to track down; the problem is similar to debugging a multithreaded program.
The program may work fine in the controlled, artificial environment of the debugger but crashes horribly in the real
world. For example, under race conditions, you find that two threads can end up modifying the same data structure
simultaneously. These kinds of bugs are terribly hard to track down and fix. If you only test your application in
isolation and then deploy it to dozens of concurrent users, you are likely to be (painfully) exposed to an undetected
concurrency issue.
Over the next two sections, I'll relate two small examples of how the lack of understanding concurrency control
can ruin your data or inhibit performance and scalability.
Implementing Locking
The database uses
locks
to ensure that, at most, one transaction is modifying a given piece of data at any given time.
Basically, locks are the mechanism that allows for concurrency—without some locking model to prevent concurrent
updates to the same row, for example, multiuser access would not be possible in a database. However, if overused or
used improperly, locks can actually inhibit concurrency. If you or the database itself locks data unnecessarily, fewer
people will be able to concurrently perform operations. Thus, understanding what locking is and how it works in your
database is vital if you are to develop a scalable, correct application.
What is also vital is that you understand that each database implements locking differently. Some have page-level
locking, others row-level; some implementations escalate locks from row level to page level, some do not; some use
read locks, others don't; some implement serializable transactions via locking and others via read-consistent views
of data (no locks). These small differences can balloon into huge performance issues or downright bugs in your
application if you don't understand how they work.
The following points sum up Oracle's locking policy:
•
Oracle locks data at the row level on modification. There is no lock escalation to a block or
table level.
•
Oracle never locks data just to read it. There are no locks placed on rows of data by
simple reads.
•
A writer of data does not block a reader of data. Let me repeat:
reads
are not blocked by
writes
. This is fundamentally different from many other databases, where reads are blocked
by writes. While this sounds like an extremely positive attribute (and it generally is), if you
do not understand this thoroughly and you attempt to enforce integrity constraints in your
application via application logic,
you are most likely doing it incorrectly.
•
A writer of data is blocked only when another writer of data has already locked the row it was
going after. A reader of data never blocks a writer of data.
