Locking and Issues - Oracle Database Transactions and Locking Revealed - page 6

Chapter 2
Locking and Issues
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.
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.
As a SQL Server programmer, I would hardly ever consider the possibility of multiple users inserting data into a
table concurrently. It was something that just didn't often happen in that database. At that time, SQL Server provided
only for page-level locking and, since all the data tended to be inserted into the last page of nonclustered tables,
concurrent inserts by two users was simply not going to happen.