Synchronisation

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.