Oracle Scratchpad

May 12, 2008


Filed under: Infrastructure,Performance,Tuning — Jonathan Lewis @ 9:19 pm BST May 12,2008

Jump to September 2022 update

There are several performance problems that show up only when you start running concurrency tests, and sometimes you need to manage a very precise degree of synchronisation to demonstrate these problems repeatably in a test environment.

For a clean and simply strategy for making sure that processes try to do the same thing at (virtually) the same moment, you need look no further than the dbms_lock package. Here’s a section of code to introduce the technique:

rem     Script:         sync_lock.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2002

        n1              number(38);
        m_handle        varchar2(60);

                lockname        => 'Synchronize',
                lockhandle      => m_handle

        n1 := dbms_lock.request(
                lockhandle              => m_handle,
                lockmode                => dbms_lock.x_mode,
                timeout                 => dbms_lock.maxwait,
                release_on_commit       => true         -- the default is false !!



The code uses the dbms_lock.allocate() call to create a ‘meaningless handle’ for a lock. This allows me to have many programs that refer to a lockable object by name, rather than number – this is a simple feature to enhance safety and readability in your code. Bear in mind, however, that a call to dbms_lock.allocate_unique() issues a commit, so you may want to wrap the call inside an autonomous transaction in your own code (but see Update Sept 2022).

After generating a lock “handle”, my code fragment requests an exclusive lock (x_mode) on the handle. Other options would be a share lock (s_mode) and all the other possibilities that you might expect of Oracle’s normal locking methods. (See $ORACLE_HOME/rdbms/admin/dbmslock.sql for more details).

You can be very inventive with this code fragment, but the use I make of it is usually very simple:

  • Session 1: Run the code fragment to acquire an exclusive lock on the “Synchronize” lock.
  • Session 2 – N: Start the scripts that are supposed to run concurrently. Each script starts with the same code fragment as session 1, but with a request for a share lock (s_mode).

Sessions 2 – N start to wait for their share lock: no-one can hold a share lock on a resource if anyone is holding an exclusive lock.  (On the other hand, you can hold a share lock on a resource if the only other locks on that resource are also share locks).

When sessions 2 – N are all waiting issue a commit in session 1 – at this point the exclusive lock is released (release_on_commit => true), and sessions 2 – N are free to acquire their share locks and resume running.

There are all sorts of ways you can use the dbms_lock package for synchronisation. But one of the really nice reasons for using it is that if a session crashes the  locks disappear as the session dies – so there’s no mess for someone to clear up afterwards.

Update March 2009

I’ve just received an email pointing me to a nice item about dbms_lock on Rob van Wijk’s blog highlighting the inherent (lack of) scalability of the package.

As a simple guideline – dbms_lock operates at the level of scalability of table locking – not at the scalability of row-locking. So be very careful about using dbms_lock to do “pseudo-locking” on rows.

Update Sept 2022


I’ve just noticed that dbms_lock now includes an autonomous version for the allocate_unique() procedure called (unsurprisingly) allocate_unique_autonomous(). This had appeared by version, but may have come into existence before then.

It’s probably also worth mentioning that Oracle copied the sleep() procedure from dbms_lock to dbms_session to avoid the need for giving access to the dbms_lock package for code only needed a pl/sql sleep.


  1. Hi Jonathan,
    I can attest to the usefulness of dbms_lock. I’ve built a RAC based data loading system, and I use dbms_lock to control access to the load tables.

    A load table is partitioned and has a set of “rolling” partitions. Multiple processes are continuously “loading” and these processes all take a “share” lock on the table through dbms_lock while they are inserting data.

    When partition maintenance must occur (i.e. adding a new daily partition) that process requests an “exclusive” lock on the load table. This effectively blocks future “share” lock requests, and once all of the current “share” locks are released the partition maintenance process will have an “exclusive” lock and can add the new partition.

    Of course this is all application level coordination, but with multiple processes running in a RAC environment the global lock coordination takes much longer to accomplish in a free for all than it does by temporarily halting access to the load table.

    The beauty of this is that it all coordinates automagically and since it’s based on Oracle’s own locking mechanism it’s been rock solid.

    Comment by Andy Rivenes — May 20, 2008 @ 4:19 pm BST May 20,2008 | Reply

  2. […] step in the code.  The code above shows a simple “pause” – I’ll be posting a note some time in the future about the method I usually […]

    Pingback by CPU usage « Oracle Scratchpad — November 13, 2008 @ 12:37 pm GMT Nov 13,2008 | Reply

  3. […] mechanism; one uses a table as an object that can be locked, the other uses Oracle’s dbms_lock package. I’ve posted the code for each fragment, and a sample of what you see in v$lock if […]

    Pingback by Lock Time | Oracle Scratchpad — September 24, 2014 @ 12:42 pm BST Sep 24,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Website Powered by

%d bloggers like this: