Getting Started - Oracle Database Transactions and Locking Revealed - page 2

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.

You must take these facts into consideration when developing your application and you must also realize that
this policy is unique to Oracle; every database has subtle differences in its approach to locking. Even if you go with
lowest common denominator SQL in your applications, the locking and concurrency control models employed
by each vendor assure something will be different. A developer who does not understand how his or her database
handles concurrency will certainly encounter data integrity issues. (This is particularly common when a developer
moves from another database to Oracle, or vice versa, and neglects to take the differing concurrency mechanisms into
account in the application.)

Concurrency Control
Concurrency control ensures that no two transactions modify the same piece of data at the same time. This is an area
where databases differentiate themselves. Concurrency control is an area that sets a database apart from a file system
and databases apart from each other. As a programmer, it is vital that your database application works correctly under
concurrent access conditions, and yet time and time again this is something people fail to test. Techniques that work well
if everything happens consecutively do not necessarily work so well when everyone does them simultaneously. If you
don't have a good grasp of how your particular database implements concurrency control mechanisms, then you will:

• Corrupt the integrity of your data.

• Have applications run slower than they should with a small number of users.

• Decrease your applications' ability to scale to a large number of users.

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 it crashes horribly in the real
world. For example, under race conditions, you find that two threads can end up modifying the same data structure