Oracle Scratchpad

May 12, 2008

Synchronisation

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

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:

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

	dbms_lock.allocate_unique(
		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 !!
	);

	dbms_output.put_line(n1);

end;
/

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.

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.

2 Comments »

  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 BST Nov 13,2008 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers