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.