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.

Exactly the same issue affected concurrent updates (since an UPDATE was really a DELETE followed by an INSERT in SQL Server). Perhaps this is why SQL Server, by default, commits or rolls back immediately after execution of each and every statement, compromising transactional integrity in an attempt to gain higher concurrency.

So in most cases, with page-level locking, multiple users could not simultaneously modify the same table. Compounding this was the fact that while a table modification was in progress, many queries were also effectively blocked against that table. If I tried to query a table and needed a page that was locked by an update, I waited (and waited and waited). The locking mechanism was so poor that providing support for transactions that took more than a second was deadly—the entire database would appear to freeze. I learned a lot of bad habits as a result. I learned that transactions were “bad” and that you ought to commit rapidly and never hold locks on data. Concurrency came at the expense of consistency. You either wanted to get it right or get it fast. I came to believe that you couldn’t have both.

When I moved on to Informix, things were better, but not by much. As long as I remembered to create a table with row-level locking enabled, then I could actually have two people simultaneously insert data into that table. Unfortunately, this concurrency came at a high price. Row-level locks in the Informix implementation were expensive, both in terms of time and memory. It took time to acquire and unacquire (release) them, and each lock consumed real memory. Also, the total number of locks available to the system had to be computed prior to starting the database. If you exceeded that number, you were just out of luck. Consequently, most tables were created with page-level locking anyway, and, as with SQL Server, both row and page-level locks would stop a query in its tracks. As a result, I found that once again I would want to commit as fast as I could. The bad habits I picked up using SQL Server were simply reinforced and, furthermore, I learned to treat a lock as a very scarce resource—something to be coveted. I learned that you should manually escalate locks from row level to table level to try to avoid acquiring too many of them and bringing the system down, and bring it down I did—many times.

When I started using Oracle, I didn’t really bother reading the manuals to find out how locking worked in this particular database. After all, I had been using databases for quite a while and was considered something of an expert in this field (in addition to Sybase, SQL Server, and Informix, I had used Ingress, DB2, Gupta SQLBase, and a variety of other databases). I had fallen into the trap of believing that I knew how things should work, so I thought of course they would work in that way. I was wrong in a big way.

It was during a benchmark that I discovered just how wrong I was. In the early days of these databases (around 1992/1993), it was common for the vendors to benchmark for really large procurements to see who could do the work the fastest, the easiest, and with the most features.

The benchmark was between Informix, Sybase SQL Server, and Oracle. Oracle went first. Their technical people came on-site, read through the benchmark specs, and started setting it up. The first thing I noticed was that the technicians from Oracle were going to use a database table to record their timings, even though we were going to have many dozens of connections doing work, each of which would frequently need to insert and update data in this log table. Not only that, but they were going to read the log table during the benchmark as well! Being a nice guy, I pulled one of the Oracle technicians aside to ask him if they were crazy. Why would they purposely introduce another point of contention into the system? Wouldn’t the benchmark processes all tend to serialize around their operations on this single table? Would they jam the benchmark by trying to read from this table as others were heavily modifying it? Why would they want to introduce all of these extra locks that they would need to manage? I had dozens of “Why would you even consider that?”–type questions. The technical folks from Oracle thought I was a little daft at that point. That is, until I pulled up a window into either Sybase SQL Server or Informix, and showed them the effects of two people inserting into a table, or someone trying to query a table with others inserting rows (the query returns zero rows per second). The differences between the way Oracle does it and the way almost every other database does it are phenomenal—they are night and day.

Needless to say, neither the Informix nor the SQL Server technicians were too keen on the database log table approach during their attempts. They preferred to record their timings to flat files in the operating system. The Oracle people left with a better understanding of exactly how to compete against Sybase SQL Server and Informix: just ask the audience “How many rows per second does your current database return when data is locked?” and take it from there.

The moral to this story is twofold. First, all databases are fundamentally different. Second, when designing an application for a new database platform, you must make no assumptions about how that database works. You must approach each new database as if you had never used a database before. Things you would do in one database are either not necessary or simply won’t work in another database.

In Oracle you will learn that:

• Transactions are what databases are all about. They are a good thing.
• You should defer committing until the correct moment. You should not do it quickly to avoid stressing the system, as it does not stress the system to have long or large transactions. The rule is commit when you must, and not before. Your transactions should only be as small or as large as your business logic dictates.
• You should hold locks on data as long as you need to. They are tools for you to use, not things to be avoided. Locks are not a scarce resource. Conversely, you should hold locks on data only as long as you need to. Locks may not be scarce, but they can prevent other sessions from modifying information.
• There is no overhead involved with row-level locking in Oracle—none. Whether you have 1 row lock or 1,000,000 row locks, the number of resources dedicated to locking this information will be the same. Sure, you’ll do a lot more work modifying 1,000,000 rows rather than 1 row, but the number of resources needed to lock 1,000,000 rows is the same as for 1 row; it is a fixed constant.
• You should never escalate a lock (e.g., use a table lock instead of row locks) because it would be “better for the system.” In Oracle, it won’t be better for the system—it will save no resources. There are times to use table locks, such as in a batch process, when you know you will update the entire table and you do not want other sessions to lock rows on you. But you are not using a table lock to make it easier for the system by avoiding having to allocate row locks; you are using a table lock to ensure you can gain access to all of the resources your batch program needs in this case.
• Concurrency and consistency can be achieved simultaneously. You can get it fast and correct, every time. Readers of data are not blocked by writers of data. Writers of data are not blocked by readers of data. This is one of the fundamental differences between Oracle and most other relational databases.

Before we discuss the various types of locks that Oracle uses (in Chapter 3), it is useful to look at some locking issues, many of which arise from badly designed applications that do not make correct use (or make no use) of the database’s locking mechanisms.

Lost Updates

A lost update is a classic database problem. Actually, it is a problem in all multiuser computer environments. Simply put, a lost update occurs when the following events occur, in the order presented here:

1. A transaction in Session1 retrieves (queries) a row of data into local memory and displays it to an end user, User1.
2. Another transaction in Session2 retrieves that same row, but displays the data to a different end user, User2.
3. User1, using the application, modifies that row and has the application update the database and commit. Session1’s transaction is now complete.
4. User2 modifies that row also, and has the application update the database and commit. Session2’s transaction is now complete.

This process is referred to as a lost update because all of the changes made in Step 3 will be lost. Consider, for example, an employee update screen that allows a user to change an address, work number, and so on. The application itself is very simple: a small search screen to generate a list of employees and then the ability to drill down into the details of each employee. This should be a piece of cake. So, we write the application with no locking on our part, just simple SELECT and UPDATE commands.

Then an end user (User1) navigates to the details screen, changes an address on the screen, clicks Save, and receives confirmation that the update was successful. Fine, except that when User1 checks the record the next day to send out a tax form, the old address is still listed. How could that have happened? Unfortunately, it can happen all too easily. In this case, another end user (User2) queried the same record just after User1 did—after User1 read the data, but before User1 modified it. Then, after User2 queried the data, User1 performed her update, received confirmation, and even re-queried to see the change for herself. However, User2 then updated the work telephone number field and clicked Save, blissfully unaware of the fact that he just overwrote User1’s changes to the address field with the old data! The reason this can happen in this case is that the application developer wrote the program such that when one particular field is updated, all fields for that record are refreshed (simply because it’s easier to update all the columns instead of figuring out exactly which columns changed and only updating those).

Note that for this to happen, User1 and User2 didn’t even need to be working on the record at the exact same time. They simply needed to be working on the record at about the same time.

I’ve seen this database issue crop up time and again when GUI programmers with little or no database training are given the task of writing a database application. They get a working knowledge of SELECT, INSERT, UPDATE, and DELETE and set about writing the application. When the resulting application behaves in the manner just described, it completely destroys a user’s confidence in it, especially since it seems so random, so sporadic, and totally irreproducible in a controlled environment (leading the developer to believe it must be user error).

Many tools, such as Oracle Forms and APEX (Application Express, the tool we used to create the AskTom web site), transparently protect you from this behavior by ensuring the record is unchanged from the time you query it, and locked before you make any changes to it (known as optimistic locking); but many others (such as a handwritten Visual Basic or a Java program) do not. What the tools that protect you do behind the scenes, or what the developers must do themselves, is use one of two types of locking strategies: pessimistic or optimistic.

Pessimistic Locking

The pessimistic locking method would be put into action the instant before a user modifies a value on the screen. For example, a row lock would be placed as soon as the user indicates his intention to perform an update on a specific row that he has selected and has visible on the screen (by clicking a button on the screen, say). That row lock would persist until the application applied the users’ modifications to the row in the database and committed.

Pessimistic locking is useful only in a stateful or connected environment—that is, one where your application has a continual connection to the database and you are the only one using that connection for at least the life of your transaction. This was the prevalent way of doing things in the early to mid 1990s with client/server applications. Every application would get a direct connection to the database to be used solely by that application instance. This method of connecting, in a stateful fashion, has become less common (though it is not extinct), especially with the advent of application servers in the mid to late 1990s.

Assuming you are using a stateful connection, you might have an application that queries the data without locking anything:

SCOTT@ORA12CR1> select empno, ename, sal from emp where deptno = 10;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2450
      7839 KING             5000
      7934 MILLER           1300

Eventually, the user picks a row she would like to update. Let’s say in this case, she chooses to update the MILLER row. Our application will, at that point, (before the user makes any changes on the screen but after the row has been out of the database for a while) bind the values the user selected so we can query the database and make sure the  data hasn’t been changed yet. In SQL*Plus, to simulate the bind calls the application would make, we can issue the following:

SCOTT@ORA12CR1> variable empno number
SCOTT@ORA12CR1> variable ename varchar2(20)
SCOTT@ORA12CR1> variable sal number
SCOTT@ORA12CR1> exec :empno := 7934; :ename := 'MILLER'; :sal := 1300;
PL/SQL procedure successfully completed.

Now in addition to simply querying the values and verifying that they have not been changed, we are going to lock the row using FOR UPDATE NOWAIT. The application will execute the following query:

SCOTT@ORA12CR1> select empno, ename, sal
  2    from emp
  3   where empno = :empno
  4     and decode(ename, :ename, 1 ) = 1
  5     and decode(sal, :sal, 1 ) = 1
  6     for update nowait
  7  /

     EMPNO ENAME             SAL
---------- ---------- ----------
      7934 MILLER           1300

The application supplies values for the bind variables from the data on the screen (in this case 7934, MILLER, and 1300) and re-queries this same row from the database, this time locking the row against updates by other sessions; hence this approach is called pessimistic locking. We lock the row before we attempt to update because we doubt—we are pessimistic—that the row will remain unchanged otherwise.

Since all tables should have a primary key (the preceding SELECT will retrieve at most one record since it includes the primary key, EMPNO) and primary keys should be immutable (we should never update them), we’ll get one of three outcomes from this statement:

• If the underlying data has not changed, we will get our MILLER row back, and this row will be locked from updates (but not reads) by others.
• If another user is in the process of modifying that row, we will get an ORA-00054 resource busy error. We must wait for the other user to finish with it.
• If, in the time between selecting the data and indicating our intention to update, someone has already changed the row, then we will get zero rows back. That implies the data on our screen is stale. To avoid the lost update scenario previously described, the application needs to re-query and lock the data before allowing the end user to modify it. With pessimistic locking in place, when User2 attempts to update the telephone field, the application would now recognize that the address field had been changed and would re-query the data. Thus, User2 would not overwrite User1’s change with the old data in that field.

Once we have locked the row successfully, the application will bind the new values, issue the update, and commit the changes:

SCOTT@ORA12CR1> update emp
  2     set ename = :ename, sal = :sal
  3   where empno = :empno;

1 row updated.

SCOTT@ORA12CR1> commit;
Commit complete.

We have now very safely changed that row. It is not possible for us to overwrite someone else’s changes, as we verified the data did not change between when we initially read it out and when we locked it—our verification made sure no one else changed it before we did, and our lock ensures no one else can change it while we are working with it.

Optimistic Locking

The second method, referred to as optimistic locking, defers all locking up to the point right before the update is performed. In other words, we will modify the information on the screen without a lock being acquired. We are optimistic that the data will not be changed by some other user; hence we wait until the very last moment to find out if we are right.

This locking method works in all environments, but it does increase the probability that a user performing an update will lose. That is, when that user goes to update her row, she finds that the data has been modified, and she has to start over.

One popular implementation of optimistic locking is to keep the old and new values in the application, and upon updating the data, use an update like this:

Update table
   Set column1 = :new_column1, column2 = :new_column2, ....
 Where primary_key = :primary_key
   And decode(column1, :old_column1, 1 ) = 1
   And decode(column2, :old_column2, 1 ) = 1
    ...

Here, we are optimistic that the data doesn’t get changed. In this case, if our update updates one row, we got lucky; the data didn’t change between the time we read it and the time we got around to submitting the update. If we update zero rows, we lose; someone else changed the data and now we must figure out what we want to do to continue in the application. Should we make the end user re-key the transaction after querying the new values for the row (potentially causing the user frustration, as there is a chance the row will have changed yet again)? Should we try to merge the values of the two updates by performing update conflict-resolution based on business rules (lots of code)?

The preceding UPDATE will, in fact, avoid a lost update, but it does stand a chance of being blocked, hanging while it waits for an UPDATE of that row by another session to complete. If all of your applications use optimistic locking, then using a straight UPDATE is generally OK since rows are locked for a very short duration as updates are applied and committed. However, if some of your applications use pessimistic locking, which will hold locks on rows for relatively long periods of time, or if there is any application (such as a batch process) that might lock rows for a long period of time (more than a second or two is considered long), then you should consider using a SELECT FOR UPDATE NOWAIT instead to verify the row was not changed, and lock it immediately prior to the UPDATE to avoid getting blocked by another session.

There are many methods of implementing optimistic concurrency control. We’ve discussed one whereby the application will store all of the before images of the row in the application itself. In the following sections, we’ll explore two others, namely:

• Using a special column that is maintained by a database trigger or application code to tell us the “version” of the record
• Using a checksum or hash that was computed using the original data

Optimistic Locking Using a Version Column

This is a simple implementation that involves adding a single column to each database table you wish to protect from lost updates. This column is generally either a NUMBER or DATE/TIMESTAMP column. It is typically maintained via a row trigger on the table, which is responsible for incrementing the NUMBER column or updating the DATE/TIMESTAMP column every time a row is modified.

The application you want to implement optimistic concurrency control would need only to save the value of this additional column, not all of the before images of the other columns. The application would only need to verify that the value of this column in the database at the point when the update is requested matches the value that was initially read out. If these values are the same, then the row has not been updated.

Let’s look at an implementation of optimistic locking using a copy of the SCOTT.DEPT table. We could use the following Data Definition Language (DDL)to create the table:

EODA@ORA12CR1> create table dept
  2  (deptno     number(2),
  3    dname      varchar2(14),
  4    loc        varchar2(13),
  5    last_mod   timestamp with time zone
  6               default systimestamp
  7               not null,
  8    constraint dept_pk primary key(deptno)
  9  )
 10  /
Table created.

Then we INSERT a copy of the DEPT data into this table:

EODA@ORA12CR1> insert into dept(deptno, dname, loc )
  2  select deptno, dname, loc
  3    from scott.dept;
4 rows created.

EODA@ORA12CR1> commit;
Commit complete.

That code re-creates the DEPT table, but with an additional LAST_MOD column that uses the TIMESTAMP WITH TIME ZONE data type. We have defined this column to be NOT NULL so that it must be populated, and its default value is the current system time.

This TIMESTAMP data type has the highest precision available in Oracle, typically going down to the microsecond (millionth of a second). For an application that involves user think time, this level of precision on the TIMESTAMP is more than sufficient, as it is highly unlikely that the process of the database retrieving a row and a human looking at it, modifying it, and issuing the update back to the database could take place within a fraction of a second. The odds of two people reading and modifying the same row in the same fraction of a second are very small indeed.

Next, we need a way of maintaining this value. We have two choices: either the application can maintain the LAST_MOD column by setting its value to SYSTIMESTAMP when it updates a record, or a trigger/stored procedure can maintain it. Having the application maintain LAST_MOD is definitely more performant than a trigger-based approach, since a trigger will add additional processing on top of that already done by Oracle. However, this does mean that you are relying on all of the applications to maintain LAST_MOD consistently in all places that they modify this table. So, if each application is responsible for maintaining this field, it needs to consistently verify that the LAST_MOD column was not changed and set the LAST_MOD column to the current SYSTIMESTAMP. For example, if an application queries the row where DEPTNO=10:

EODA@ORA12CR1> variable deptno   number
EODA@ORA12CR1> variable dname    varchar2(14)
EODA@ORA12CR1> variable loc      varchar2(13)
EODA@ORA12CR1> variable last_mod varchar2(50)
EODA@ORA12CR1>
EODA@ORA12CR1> begin
  2      :deptno := 10;
  3      select dname, loc, to_char(last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' )
  4        into :dname,:loc,:last_mod
  5        from dept
  6       where deptno = :deptno;
  7  end;
  8  /
PL/SQL procedure successfully completed.

which we can see is currently

EODA@ORA12CR1> select :deptno dno, :dname dname, :loc loc, :last_mod lm
  2    from dual;

    DNO DNAME        LOC        LM
------- ------------ ---------- ----------------------------------------
     10 ACCOUNTING   NEW YORK   15-APR-2014 07.04.01.147094 PM -06:00

would use this next update statement to modify the information. The last line does the very important check to make sure the timestamp has not changed and uses the built-in function TO_TIMESTAMP_TZ (tz is short for time zone ) to convert the string we saved in from the SELECT statement back into the proper data type. Additionally, line 3 of the UPDATE statement updates the LAST_MOD column to be the current time if the row is found to be updated:

EODA@ORA12CR1> update dept
  2     set dname = initcap(:dname),
  3         last_mod = systimestamp
  4   where deptno = :deptno
  5     and last_mod = to_timestamp_tz(:last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' );

1 row updated.

As you can see, one row was updated, the row of interest. We updated the row by primary key (DEPTNO) and verified that the LAST_MOD column had not been modified by any other session between the time we read it first and the time we did the update. If we were to try to update that same record again, using the same logic but without retrieving the new LAST_MOD value, we would observe the following:

EODA@ORA12CR1> update dept
  2     set dname = upper(:dname),
  3         last_mod = systimestamp
  4   where deptno = :deptno
  5     and last_mod = to_timestamp_tz(:last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' );

0 rows updated.

Notice how 0 rows updated is reported this time because the predicate on LAST_MOD was not satisfied. While DEPTNO 10 still exists, the value at the moment we wish to update no longer matches the timestamp value at the moment we queried the row. So, the application knows that the data has been changed in the database, based on the fact that no rows were modified—and it must now figure out what it wants to do about that.

You would not rely on each application to maintain this field for a number of reasons. For one, it adds code to an application, and it is code that must be repeated and correctly implemented anywhere this table is modified. In a large application, that could be in many places. Furthermore, every application developed in the future must also conform to these rules. There are many chances to miss a spot in the application code and thus not have this field properly used. So, if the application code itself isn’t responsible for maintaining this LAST_MOD field, then I believe that the application shouldn’t be responsible for checking this LAST_MOD field either (if it can do the check, it can certainly do the update). So, in this case, I suggest encapsulating the update logic in a stored procedure and not allowing the application to update the table directly at all. If it cannot be trusted to maintain the value in this field, then it cannot be trusted to check it properly either. So, the stored procedure would take as inputs the bind variables we used in the previous updates and do exactly the same update. Upon detecting that zero rows were updated, the stored procedure could raise an exception back to the client to let the client know the update had, in effect, failed.

An alternate implementation uses a trigger to maintain this LAST_MOD field, but for something as simple as this, my recommendation is to avoid the trigger and let the DML take care of it. Triggers introduce a measurable amount of overhead, and in this case they would be unnecessary. Furthermore, the trigger would not be able to confirm that the row has not been modified (it would only be able to supply the value for LAST_MOD, not check it during the update), hence the application has to be made painfully aware of this column and how to properly use it. So the trigger is not by itself sufficient.

Optimistic Locking Using a Checksum

This is very similar to the previous version column method, but it uses the base data itself to compute a “virtual” version column. I’ll quote the Oracle Database PL/SQL Packages and Types Reference manual (before showing how to use one of the supplied packages) to help explain the goal and concepts behind a checksum or hash function:

We can use these hashes or checksums in the same way that we used our version column. We simply compare the hash or checksum value we obtain when we read data out of the database with that we obtain before modifying the data. If someone modified the row’s values after we read it out, but before we updated it, then the hash or checksum will almost certainly be different.

There are many ways to compute a hash or checksum. I’ll list several of these and demonstrate one in this section. All of these methods are based on supplied database functionality.

• OWA_OPT_LOCK.CHECKSUM: This method is available on Oracle8i version 8.1.5 and up. There is a function that, given a string, returns a 16-bit checksum, and another function that, given a ROWID, will compute the 16-bit checksum of that row and lock it at the same time. Possibilities of collision are 1 in 65,536 strings (the highest chance of a false positive).
• DBMS_OBFUSCATION_TOOLKIT.MD5: This method is available in Oracle8i version 8.1.7 and up. It computes a 128-bit message digest. The odds of a collision are about 1 in 3.4028E+38 (very small).
• DBMS_CRYPTO.HASH: This method is available in Oracle 10g Release 1 and up. It is capable of computing a Secure Hash Algorithm 1 (SHA-1) or MD4/MD5 message digests. It is recommended that you use the SHA-1 algorithm.
• DBMS_SQLHASH.GETHASH: This method is available in Oracle 10g Release 2 and up. It supports hash algorithms of SHA-1, MD4, and MD5. As a SYSDBA privileged user, you must grant execute on this package to a user before they can access it. This package is documented in the Oracle Database Security Guide.
• STANDARD_HASH: This method is available in Oracle 12c Release 1 and up. This is a built-in SQL function that computes a hash value on an expression using standard hash algorithms such as SHA1 (default), SHA256, SHA384, SHA512, and MD5. The returned value is a RAW data type.
• ORA_HASH: This method is available in Oracle 10g Release 1 and up. This is a built-in SQL function that takes a VARCHAR2 value as input and (optionally) another pair of inputs that control the return value. The returned value is a number—by default a number between 0 and 4294967295.

The following example shows how you might use the ORA_HASH built-in function in Oracle 10g and above to compute these hashes/checksums. The technique would also be applicable for the other listed approaches; the logic would not be very much different, but the APIs you call would be. First, we’ll start by removing the column we used in the previous example:

EODA@ORA12CR1> alter table dept drop column last_mod;
Table altered.

And then have our application query and display the information for department 10. Note that while we query the information, we compute the hash using the ORA_HASH built-in. This is the version information that we retain in our application. Following is our code to query and display:

EODA@ORA12CR1> variable deptno number
EODA@ORA12CR1> variable dname varchar2(14)
EODA@ORA12CR1> variable loc varchar2(13)
EODA@ORA12CR1> variable hash number

EODA@ORA12CR1> begin
  2  select deptno, dname, loc,
  3         ora_hash(dname || '/' || loc ) hash
  4    into :deptno, :dname, :loc, :hash
  5    from dept
  6   where deptno = 10;
  7  end;
  8  /
PL/SQL procedure successfully completed.

EODA@ORA12CR1> select :deptno, :dname, :loc, :hash
  2    from dual;

   :DEPTNO :DNAME     :LOC            :HASH
---------- ---------- ---------- ----------
        10 Accounting NEW YORK   2721972020

As you can see, the hash is just some number. It is the value we would want to use before updating. To update that row, we would lock the row in the database as it exists right now, and then compare the hash value of that row with the hash value we computed when we read the data out of the database. The logic for doing so could look like the following:

EODA@ORA12CR1> exec :dname := lower(:dname);
PL/SQL procedure successfully completed.

EODA@ORA12CR1> update dept
  2     set dname = :dname
  3   where deptno = :deptno
  4     and ora_hash(dname || '/' || loc ) = :hash
  5  /
1 row updated.

EODA@ORA12CR1> select dept.*,
  2         ora_hash(dname || '/' || loc ) hash
  3    from dept
  4   where deptno = :deptno;

    DEPTNO DNAME      LOC              HASH
---------- ---------- ---------- ----------
        10 accounting NEW YORK   2818855829

Upon re-querying the data and computing the hash again after the update, we can see that the hash value is different. If someone had modified the row before we did, our hash values would not have compared. We can see this by attempting our update again, using the old hash value we read out the first time:

EODA@ORA12CR1> update dept
  2     set dname = :dname
  3   where deptno = :deptno
  4     and ora_hash(dname || '/' || loc ) = :hash
  5  /

0 rows updated.

As you see, there were zero rows updated, since our hash value did not match the data currently in the database.

In order for this hash-based approach to work properly, we must ensure every application uses the same approach when computing the hash, specifically they must concatenate dname with ‘/’ with loc – in that order. To make that approach universal, I would suggest adding a virtual column to the table (in Oracle 11g Release 1 and above) or using a view to add a column, so that the function is hidden from the application itself. Adding a column would look like this in Oracle 11g Release 1 and above:

EODA@ORA12CR1> alter table dept
  2  add hash as
  3  (ora_hash(dname || '/' || loc ) );
Table altered.

EODA@ORA12CR1> select *
  2    from dept
  3   where deptno = :deptno;

    DEPTNO DNAME      LOC              HASH
---------- ---------- ---------- ----------
        10 accounting NEW YORK   2818855829

The added column is a virtual column and as such incurs no storage overhead. The value is not computed and stored on disk. Rather, it is computed upon retrieval of the data from the database.

This example showed how to implement optimistic locking with a hash or checksum. You should bear in mind that computing a hash or checksum is a somewhat CPU-intensive operation; it is computationally expensive. On a system where CPU bandwidth is a scarce resource, you must take this fact into consideration. However, this approach is much more network-friendly because the transmission of a relatively small hash instead of a before-and-after image of the row (to compare column by column) over the network will consume much less of that resource.

Optimistic or Pessimistic Locking?

So which method is best? In my experience, pessimistic locking works very well in Oracle (but perhaps not so well in other databases) and has many advantages over optimistic locking. However, it requires a stateful connection to the database, like a client/server connection. This is because locks are not held across connections. This single fact makes pessimistic locking unrealistic in many cases today. In the past, with client/server applications and a couple dozen or hundred users, it would have been my first and only choice. Today, however, optimistic concurrency control is what I would recommend for most applications. Having a connection for the entire duration of a transaction is just too high a price to pay.

Of the methods available, which do I use? I tend to use the version column approach with a timestamp column. It gives me the extra update information in a long-term sense. Furthermore, it’s less computationally expensive than a hash or checksum, and it doesn’t run into the issues potentially encountered with a hash or checksum when processing LONG, LONG RAW, CLOB, BLOB, and other very large columns (LONG and LONG RAW are obsolete, I only mention them here because they’re still used in the Oracle data dictionary).

If I had to add optimistic concurrency controls to a table that was still being used with a pessimistic locking scheme (e.g., the table was accessed in both client/server applications and over the Web), I would opt for the ORA_HASH approach. The reason is that the existing legacy application might not appreciate a new column appearing. Even if we took the additional step of hiding the extra column, the application might suffer from the overhead of the necessary trigger. The ORA_HASH technique would be nonintrusive and lightweight in that respect. The hashing/checksum approach can be very database independent, especially if we compute the hashes or checksums outside of the database. However, by performing the computations in the middle tier rather than the database, we will incur higher resource usage penalties in terms of CPU usage and network transfers.

Blocking

Blocking occurs when one session holds a lock on a resource that another session is requesting. As a result, the requesting session will be blocked—it will hang until the holding session gives up the locked resource. In almost every case, blocking is avoidable. In fact, if you do find that your session is blocked in an interactive application, then you have probably been suffering from the lost update bug as well, perhaps without realizing it. That is, your application logic is flawed and that is the cause of the blocking.

The five common DML statements that will block in the database are INSERT, UPDATE, DELETE, MERGE, and SELECT FOR UPDATE. The solution to a blocked SELECT FOR UPDATE is trivial: simply add the NOWAIT clause and it will no longer block. Instead, your application will report a message back to the end user that the row is already locked. The interesting cases are the remaining four DML statements. We’ll look at each of them and see why they should not block and how to correct the situation if they do.

Blocked Inserts

There are few times when an INSERT will block. The most common scenario is when you have a table with a primary key or unique constraint placed on it and two sessions attempt to insert a row with the same value. One of the sessions will block until the other session either commits (in which case the blocked session will receive an error about a duplicate value) or rolls back (in which case the blocked session succeeds). Another case involves tables linked together via referential integrity constraints. An INSERT into a child table may become blocked if the parent row it depends on is being created or deleted.

Blocked INSERTs typically happen with applications that allow the end user to generate the primary key/unique column value. This situation is most easily avoided by using a sequence or the SYS_GUID() built-in function to generate the primary key/unique column value. Sequences/SYS_GUID() were designed to be highly concurrent methods of generating unique keys in a multiuser environment. In the event that you cannot use either and must allow the end user to generate a key that might be duplicated, you can use the following technique, which avoids the issue by using manual locks implemented via the built-in DBMS_LOCK package.

With inserts, there’s no existing row to select and lock; there’s no way to prevent others from inserting a row with the same value, thus blocking our session and causing an indefinite wait. Here is where DBMS_LOCK comes into play. To demonstrate this technique, we will create a table with a primary key and a trigger that will prevent two (or more) sessions from inserting the same values simultaneously. The trigger will use DBMS_UTILITY.GET_HASH_VALUE to hash the primary key into some number between 0 and 1,073,741,823 (the range of lock ID numbers permitted for our use by Oracle). In this example, I’ve chosen a hash table of size 1,024, meaning we will hash our primary keys into one of 1,024 different lock IDs. Then we will use DBMS_LOCK.REQUEST to allocate an exclusive lock based on that ID. Only one session at a time will be able to do that, so if someone else tries to insert a record into our table with the same primary key, that person’s lock request will fail (and the error resource busy will be raised):

SCOTT@ORA12CR1> create table demo ( x int primary key );
Table created.

SCOTT@ORA12CR1> create or replace trigger demo_bifer
  2  before insert on demo
  3  for each row
  4  declare
  5      l_lock_id   number;
  6      resource_busy   exception;
  7      pragma exception_init(resource_busy, -54 );
  8  begin
  9      l_lock_id :=
 10            dbms_utility.get_hash_value(to_char(:new.x ), 0, 1024 );
 11      if ( dbms_lock.request
 12               (  id                => l_lock_id,
 13                  lockmode          => dbms_lock.x_mode,
 14                  timeout           => 0,
 15                  release_on_commit => TRUE ) not in (0,4) )
 16      then
 17          raise resource_busy;
 18      end if;
 19  end;
 20  /
Trigger created.

SCOTT@ORA12CR1> insert into demo(x) values (1);
1 row created.

Now, to demonstrate us catching this blocking INSERT problem in a single session, we’ll use an AUTONOMOUS_TRANSACTION so that it seems as if this next block of code was executed in another SQL*Plus session. In fact, if you use another session, the behavior will be the same. Here we go:

SCOTT@ORA12CR1> declare
  2      pragma autonomous_transaction;
  3  begin
  4      insert into demo(x) values (1);
  5      commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SCOTT.DEMO_BIFER", line 14
ORA-04088: error during execution of trigger 'SCOTT.DEMO_BIFER'
ORA-06512: at line 4

The concept here is to take the supplied primary key value of the table protected by the trigger and put it in a character string. We can then use DBMS_UTILITY.GET_HASH_VALUE to come up with a mostly unique hash value for the string. As long as we use a hash table smaller than 1,073,741,823, we can lock that value exclusively using DBMS_LOCK.

After hashing, we take that value and use DBMS_LOCK to request that lock ID to be exclusively locked with a timeout of ZERO (this returns immediately if someone else has locked that value). If we timeout or fail for any reason, we raise ORA-00054 Resource Busy. Otherwise, we do nothing—it is OK to insert, we won’t block. Upon committing our transaction, all locks, including those allocated by this DBMS_LOCK call, will be released.

Of course, if the primary key of your table is an INTEGER and you don’t expect the key to go over 1 billion, you can skip the hash and just use the number as the lock ID.

You’ll need to play with the size of the hash table (1,024 in this example) to avoid artificial resource busy messages due to different strings hashing to the same number. The size of the hash table will be application (data)-specific, and it will be influenced by the number of concurrent insertions as well. You might also add a flag to the trigger to allow people to turn the check on and off. If I were going to insert hundreds or thousands of records, for example, I might not want this check enabled.

Blocked Merges, Updates, and Deletes

In an interactive application—one where you query some data out of the database, allow an end user to manipulate it, and then put it back into the database—a blocked UPDATE or DELETE indicates that you probably have a lost update problem in your code. (I’ll call it a bug in your code if you do). You are attempting to UPDATE a row that someone else is already updating (in other words, one that someone else already has locked). You can avoid the blocking issue by using the SELECT FOR UPDATE NOWAIT query to

• Verify the data has not changed since you queried it out (preventing lost updates).
• Lock the row (preventing the UPDATE or DELETE from blocking).

As discussed earlier, you can do this regardless of the locking approach you take. Both pessimistic and optimistic locking may employ the SELECT FOR UPDATE NOWAIT query to verify the row has not changed. Pessimistic locking would use that SELECT FOR UPDATE NOWAIT statement the instant the user indicated her intention to modify the data. Optimistic locking would use that statement immediately prior to updating the data in the database. Not only will this resolve the blocking issue in your application, but it’ll also correct the data integrity issue.

Since a MERGE is simply an INSERT and UPDATE (and in 10g and above, with the enhanced MERGE syntax, it’s a DELETE as well), you would use both techniques simultaneously.

Deadlocks

Deadlocks occur when you have two sessions, each of which is holding a resource that the other wants. For example, if I have two tables, A and B, in my database, and each has a single row in it, I can demonstrate a deadlock easily. All I need to do is open two sessions (e.g., two SQL*Plus sessions). In session A, I update table A. In session B, I update table B. Now, if I attempt to update table A in session B, I will become blocked. Session A has this row locked already. This is not a deadlock; it is just blocking. I have not yet deadlocked because there is a chance that session A will commit or roll back, and session B will simply continue at that point.

If I go back to session A and then try to update table B, I will cause a deadlock. One of the two sessions will be chosen as a victim and will have its statement rolled back. For example, the attempt by session B to update table A may be rolled back, with an error such as the following:

update a set x = x+1
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Session A’s attempt to update table B will remain blocked—Oracle will not roll back the entire transaction. Only one of the statements that contributed to the deadlock is rolled back. Session B still has the row in table B locked, and session A is patiently waiting for the row to become available. After receiving the deadlock message, session B must decide whether to commit the outstanding work on table B, roll it back, or continue down an alternate path and commit later. As soon as this session does commit or roll back, the other blocked session will continue on as if nothing happened.

Oracle considers deadlocks to be so rare and unusual that it creates a trace file on the server each time one does occur. The contents of the trace file will look something like this:

*** 2014-04-16 18:58:26.602
*** SESSION ID:(31.18321) 2014-04-16 18:58:26.603
*** CLIENT ID:() 2014-04-16 18:58:26.603
*** SERVICE NAME:(SYS$USERS) 2014-04-16 18:58:26.603
*** MODULE NAME:(SQL*Plus) 2014-04-16 18:58:26.603
*** ACTION NAME:() 2014-04-16 18:58:26.603
*** 2014-04-16 18:58:26.603
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Obviously, Oracle considers these application deadlocks a self-induced error on the part of the application and, for the most part, Oracle is correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost nonexistent. Typically, you must come up with artificial conditions to get one.

The number one cause of deadlocks in the Oracle database, in my experience, is unindexed foreign keys. (The number two cause is bitmap indexes on tables subject to concurrent updates). Oracle will place a full table lock on a child table after modification of the parent table in three scenarios:

• If you update the parent table’s primary key (a very rare occurrence if you follow the rule of relational databases stating that primary keys should be immutable), the child table will be locked in the absence of an index on the foreign key.
• If you delete a parent table row, the entire child table will be locked (in the absence of an index on the foreign key) as well.
• If you merge into the parent table, the entire child table will be locked (in the absence of an index on the foreign key) as well. Note this is only true in Oracle9i and 10g and is no longer true in Oracle 11g Release 1 and above.

These full table locks are a short-term occurrence in Oracle9i and above, meaning they need to be taken for the duration of the DML operation, not the entire transaction. Even so, they can and do cause large locking issues. As a demonstration of the first point, if we have a pair of tables set up as follows, nothing untoward happens yet:

EODA@ORA12CR1> create table p (x int primary key );
Table created.

EODA@ORA12CR1> create table c (x references p );
Table created.

EODA@ORA12CR1> insert into p values ( 1 );
1 row created.

EODA@ORA12CR1> insert into p values ( 2 );
1 row created.

EODA@ORA12CR1> commit;
Commit complete.

EODA@ORA12CR1> insert into c values ( 2 );
1 row created.

But if we go into another session and attempt to delete the first parent record, we’ll find that session gets immediately blocked.

EODA@ORA12CR1> delete from p where x = 1;

It is attempting to gain a full table lock on table C before it does the delete. Now no other session can initiate a DELETE, INSERT, or UPDATE of any rows in C (the sessions that had already started may continue, but no new sessions may start to modify C).

This blocking would happen with an update of the primary key value as well. Because updating a primary key is a huge no-no in a relational database, this is generally not an issue with updates. However, I have seen this updating of the primary key become a serious issue when developers use tools that generate SQL for them, and those tools update every single column, regardless of whether the end user actually modified that column or not. For example, say that we use Oracle Forms and create a default layout on any table. Oracle Forms by default will generate an update that modifies every single column in the table we choose to display. If we build a default layout on the DEPT table and include all three fields, Oracle Forms will execute the following command whenever we modify any of the columns of the DEPT table:

update dept set deptno=:1,dname=:2,loc=:3 where rowid=:4

In this case, if the EMP table has a foreign key to DEPT and there is no index on the DEPTNO column in the EMP table, then the entire EMP table will be locked during an update to DEPT. This is something to watch out for carefully if you are using any tools that generate SQL for you. Even though the value of the primary key does not change, the child table EMP will be locked after the execution of the preceding SQL statement. In the case of Oracle Forms, the solution is to set that table’s UPDATE CHANGED COLUMNS ONLY property to YES. Oracle Forms will generate an UPDATE statement that includes only the changed columns (not the primary key).

Problems arising from deletion of a row in a parent table are far more common. As I demonstrated, if I delete a row in table P, then the child table, C, will become locked during the DML operation, thus preventing other updates against C from taking place for the duration of the transaction (assuming no one else was modifying C, of course; in which case the delete will wait). This is where the blocking and deadlock issues come in. By locking the entire table C, I have seriously decreased the concurrency in my database to the point where no one will be able to modify anything in C. In addition, I have increased the probability of a deadlock, since I now own lots of data until I commit. The probability that some other session will become blocked on C is now much higher; any session that tries to modify C will get blocked. Therefore, I’ll start seeing lots of sessions that hold some preexisting locks on other resources getting blocked in the database. If any of these blocked sessions are, in fact, locking a resource that my session also needs, we will have a deadlock. The deadlock in this case is caused by my session preventing access to many more resources (in this case, all of the rows in a single table) than it ever needed. When someone complains of deadlocks in the database, I have them run a script that finds unindexed foreign keys; 99 percent of the time we locate an offending table. By simply indexing that foreign key, the deadlocks—and lots of other contention issues—go away. The following example demonstrates the use of this script to locate the unindexed foreign key in table C:

EODA@ORA12CR1> column columns format a30 word_wrapped
EODA@ORA12CR1> column table_name format a15 word_wrapped
EODA@ORA12CR1> column constraint_name format a15 word_wrapped

EODA@ORA12CR1> select table_name, constraint_name,
  2         cname1 || nvl2(cname2,','||cname2,null) ||
  3         nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
  4         nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
  5         nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
  6                columns
  7      from ( select b.table_name,
  8                    b.constraint_name,
  9                    max(decode(position, 1, column_name, null )) cname1,
 10                    max(decode(position, 2, column_name, null )) cname2,
 11                    max(decode(position, 3, column_name, null )) cname3,
 12                    max(decode(position, 4, column_name, null )) cname4,
 13                    max(decode(position, 5, column_name, null )) cname5,
 14                    max(decode(position, 6, column_name, null )) cname6,
 15                    max(decode(position, 7, column_name, null )) cname7,
 16                    max(decode(position, 8, column_name, null )) cname8,
 17                    count(*) col_cnt
 18               from (select substr(table_name,1,30) table_name,
 19                            substr(constraint_name,1,30) constraint_name,
 20                            substr(column_name,1,30) column_name,
 21                            position
 22                       from user_cons_columns ) a,
 23                    user_constraints b
 24              where a.constraint_name = b.constraint_name
 25                and b.constraint_type = 'R'
 26              group by b.table_name, b.constraint_name
 27           ) cons
 28     where col_cnt > ALL
 29             ( select count(*)
 30                 from user_ind_columns i,
 31                      user_indexes     ui
 32                where i.table_name = cons.table_name
 33                  and i.column_name in (cname1, cname2, cname3, cname4,
 34                                        cname5, cname6, cname7, cname8 )
 35                  and i.column_position <= cons.col_cnt
 36                  and ui.table_name = i.table_name
 37                  and ui.index_name = i.index_name
 38                  and ui.index_type IN ('NORMAL','NORMAL/REV')
 39                group by i.index_name
 40             )
 41  /

TABLE_NAME      CONSTRAINT_NAME COLUMNS
--------------- --------------- ------------------------------
C               SYS_C0061427    X

This script works on foreign key constraints that have up to eight columns in them (if you have more than that, you probably want to rethink your design). It starts by building an inline view named CONS in the previous query. This inline view transposes the appropriate column names in the constraint from rows into columns, with the result being a row per constraint and up to eight columns that have the names of the columns in the constraint. Additionally, there is a column, COL_CNT, which contains the number of columns in the foreign key constraint itself. For each row returned from the inline view, we execute a correlated subquery that checks all of the indexes on the table currently being processed. It counts the columns in that index that match columns in the foreign key constraint and then groups them by index name. So, it generates a set of numbers, each of which is a count of matching columns in some index on that table. If the original COL_CNT is greater than all of these numbers, then there is no index on that table that supports that constraint. If COL_CNT is less than all of these numbers, then there is at least one index that supports that constraint. Note the use of the NVL2 function, which we used to “glue” the list of column names into a comma-separated list. This function takes three arguments: A, B, C. If argument A is not null, then it returns argument B; otherwise, it returns argument C. This query assumes that the owner of the constraint is the owner of the table and index as well. If another user indexed the table or the table is in another schema (both rare events), it will not work correctly.

The prior script also checks to see if the index type is a B*Tree index (NORMAL or NORMAL/REV). We’re checking to see if it’s a B*Tree index because a bitmap index on a foreign key column does not prevent the locking issue.

So, the prior script shows that table C has a foreign key on the column X but no index. By creating a B*Tree index on X, we can remove this locking issue all together. In addition to this table lock, an unindexed foreign key can also be problematic in the following cases:

• When you have an ON DELETE CASCADE and have not indexed the child table. For example, EMP is child of DEPT. DELETE DEPTNO = 10 should CASCADE to EMP. If DEPTNO in EMP is not indexed, you will get a full table scan of EMP for each row deleted from the DEPT table. This full scan is probably undesirable, and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
• When you query from the parent to the child. Consider the EMP/DEPT example again. It is very common to query the EMP table in the context of a DEPTNO. If you frequently run the following query (say, to generate a report), you’ll find that not having the index in place will slow down the queries:

          select * from dept, emp
          where emp.deptno = dept.deptno and dept.deptno = :X;

When do you not need to index a foreign key? The answer is, in general, when the following conditions are met:

• You do not delete from the parent table.
• You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools).
• You do not join from the parent to the child (like DEPT to EMP).

If you satisfy all three conditions, feel free to skip the index; it’s not needed. If you meet any of the preceding conditions, be aware of the consequences. This is the one rare instance when Oracle tends to overlock data.

Lock Escalation

When lock escalation occurs, the system is decreasing the granularity of your locks. An example would be the database system turning your 100 row-level locks against a table into a single table-level lock. You are now using one lock to lock everything and, typically, you are also locking a whole lot more data than you were before. Lock escalation is used frequently in databases that consider a lock to be a scarce resource and overhead to be avoided.

Oracle never escalates locks, but it does practice lock conversion or lock promotion, terms that are often confused with lock escalation.

Oracle will take a lock at the lowest level possible (i.e., the least restrictive lock possible) and convert that lock to a more restrictive level if necessary. For example, if you select a row from a table with the FOR UPDATE clause, two locks will be created. One lock is placed on the row(s) you selected (and this will be an exclusive lock; no one else can lock that specific row in exclusive mode). The other lock, a ROW SHARE TABLE lock, is placed on the table itself. This will prevent other sessions from placing an exclusive lock on the table and thus prevent them from altering the structure of the table, for example. Another session can modify any other row in this table without conflict. As many commands as possible that could execute successfully given there is a locked row in the table will be permitted.

Lock escalation is not a database “feature.” It is not a desired attribute. The fact that a database supports lock escalation implies there is some inherent overhead in its locking mechanism and significant work is performed to manage hundreds of locks. In Oracle, the overhead to have 1 lock or 1 million locks is the same: none.

Summary

This chapter covered a lot of material that, at times, may have made you scratch your head. While locking is rather straightforward, some of its side effects are not. However, it is vital that you understand these issues. For example, if you were not aware of the table lock Oracle uses to enforce a foreign key relationship when the foreign key is not indexed, then your application would suffer from poor performance. If you did not understand how to review the data dictionary to see who was locking whom, you might never figure that one out. You would just assume that the database hangs sometimes. I sometimes wish I had a dollar for every time I was able to solve the insolvable hanging issue by simply running the query to detect unindexed foreign keys and suggesting that we index the one causing the problem. I would be very rich.