Even though Oracle Db started with optimistic locking many years back, old fashion programming style can still be observed too often.

In this article I’ll explain why you should avoid using pessimistic locking as mach as possible, and will show one of the alternative way to do the same task by using optimistic locking.

I’ll start with well known DEPT table inside SCOTT schema.

In the first session I’ll execute the following SELECT..FOR..UPDATE statement:


select * from dept where deptno = 10 for update;

From the second session I’ll execute the following SQL:


select l.type, l.lmode, o.owner, o.object_name 
from v$lock l, dba_objects o
WHERE 
l.id1 = o.object_id
and o.owner = 'SCOTT';

TY      LMODE OWNER                          OBJECT_NAME                                                                                                                     
-- ---------- ------------------------------ ------------
TM          3 SCOTT                          DEPT                                                                                                                            

As we can see, SELECT … FOR UPDATE set TM lock on the affected row(s).

TM type of locks are used to ensure that table is not altered while you are modifying its contents.

If I want to update the same row from other session, the following error will be thrown.


select * from scott.dept where DEPTNO = 10 for update nowait;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
00054. 00000 -  "resource busy and acquire with NOWAIT specified or timeout expired"
*Cause:    Interested resource is busy.
*Action:   Retry if necessary or increase timeout.

By using SELECT..FOR..UPDATE clause, I locked one record from DEPT table at the beginning of code logic to ensure that no one else can modify that particular row till I commit or rollback transaction.

As I lock the row at the beginning of transaction, and lock is held through the duration of transaction (commit/rollback) as I expected that someone else might change the same row (which is extremely rarely going to happen), this type of coding logic is called pessimistic locking.

Oracle Forms is known to trigger pessimistic style of locking.

Such type of coding was popular during the 80’s and 90’s, but in today’s world it is old fashion way of coding which impose to many concurrency restrictions.

 

Let’s check what optimistic type of locking means and one way of how to implement it.

One possible solutions is to use ora_hash function.

First I’ll create a copy of DEPT table:


create table dept_optimistic 
tablespace users
as
select * from dept; 

After that I can add virtual column (assuming that majority of Oracle clients are already on 11g or higher version):


alter table dept_optimistic add hash_key as (ora_hash(dname || '-' || loc));

Here I can also include DEPTNO column which is primary key, but there is no need for that as I’m using WHERE condition on top of DEPTNO which is already unique. In case when I expect that WHERE clause may return multiple rows (for example WHERE DEPTNO > 10), I would need to include all columns to get unique hash value for each row and I need to use collections to keep track of changes across all rows returned by the query.


column deptno FORMAT 99
column dname format a15
column loc format a15
column hash_key format 999999999999999
select * from dept_optimistic;

DEPTNO DNAME           LOC                     HASH_KEY
------ --------------- --------------- ----------------
    10 ACCOUNTING      NEW YORK              4082290700
    20 RESEARCH        DALLAS                  26008817
    30 SALES           CHICAGO               1385115923
    40 OPERATIONS      BOSTON                2868873274

Here I’m generating HASH_KEY by using combination of two non unique columns which combined together become unique and thus produce unique hash across the rows (otherwise I’ll need to include DEPTNO Promary Key).

To use optimistic locking we can apply the following logic:


select DEPTNO, DNAME, LOC, HASH_KEY into L_DEPTNO, L_DNAME, L_LOC, L_HASH_KEY from dept_optimistic where deptno = 10;

L_DEPTNO = 10
L_DNAME = 'ACCOUNTING'
L_LOC = 'NEW YORK' 
L_HASH_KEY = '4082290700'

Now I can update row with DEPTNO = 10


update dept_optimistic set loc = 'LONDON' where deptno = 10 and hash_key = 4082290700;
1 row updated.

If I query the table now, we get the following:


select * from dept_optimistic where deptno = 10;

L_DNAME = 'ACCOUNTING'
L_LOC = 'NEW YORK' 
L_HASH_KEY = 2395529269

It’s clear that HASH_KEY has a new value, which indicates that row has been changed.

That guarantees if someone has updated the row of interest, I have a way to check as I’m keeping track of original HASH_KEY value in local variable.

If I try to execute another update:


update dept_optimistic set loc = 'LONDON' where deptno = 10 and hash_key = 4082290700;
0 rows updated.

This time no rows has been updated, meaning the row has been changed in the meanwhile, and a new HASH_KEY value confirms that fact.

But the main question is why all those logic and additional code instead of simple SELECT..FOR..UPDATE clause?

Main reason is that duration between storing column values into local variables and time when I finally want to update row of interest might be substantial, and during that time there will be no locks at all, while in previous case we are keeping row of interest locked through the duration of transaction.

As I’m not locking row of interest during transactions (as I’m not expecting that someone else will change row of interest), that type of code logic is called optimistic locking.

Application, in case that number of updated rows is 0, will inform end user that someone else has changed the row, after that I’ll need to re-execute the same query again and repeat the transaction.

Not producing any locks till the time when I really execute the UPDATE statement (following by commit) will increase throughput of Oracle database and improve performance while lowering memory and CPU consumption.

 

Summary:

Optimistic locking was always one of feature that makes difference between Oracle RDBMS and the others. If you are not using it, you are not taking advantage that’s on your disposal.

Lower number of concurrency contention affects all parts of Oracle Db and improve performance, throughput, lower CPU usage etc.

Only in some special cases I would recommend to use the old way of coding the app by using SELECT … FOR … UPDATE clause.

In all other cases, optimistic locking logic should be used.

Usage of ORA_HASH is not the only way to implement logic of optimistic locking.

In one of the future articles I’ll show some alternatives to hash key approach.


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.