Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 41
Pages: 41
Authors: Richard Laermer, Michael Prichinello
The DBMS_LOCK package exposes, to the programmer, the locking mechanism used by Oracle itself. It allows them to create their own named locks. These locks can be monitored in the same way as any other Oracle lock. They will show up in the dynamic performance view V$LOCK with a type of UL (user lock). Also any standard tool such as Oracle Enterprise Manager, and the UTLOCKT.SQL script (found in [ORACLE_HOME]/rdbms/admin) will display them as well. In addition to exposing the locking mechanism for a programmer to use, DBMS_LOCK has one other utility function, a SLEEP function, which allows a PL/SQL program to pause for a given number of seconds.
The DBMS_LOCK package has many uses, for example:
You have a routine that uses UTL_FILE to write audit messages to an operating system file. Only one process at a time should write to this file. On some operating systems, such as Solaris, many can write simultaneously (the OS does not prevent it). This results in inter-leaved audit messages that are hard or impossible to read. DBMS_LOCK can be used to serialize access to this file.
To prevent mutually exclusive operations from occurring concurrently. For example, assume you have a data purge routine that can run only when other sessions that need the data are not running. These other sessions cannot begin while a purge is happening - they must wait. The purge session would attempt to get a named lock in X (exclusive) mode. The other sessions would attempt to get this same named lock in S (shared) mode. The X lock request will block while any S locks are present, and the S lock request will block while the X lock is held. You will have made it so the purge session will wait until there are no 'normal' sessions, and if the purge session is executing, all other sessions will be blocked until it is finished.
These are two common uses of this package. They work well as long as all sessions co-operate in the use of locks (there is nothing stopping a session from using UTL_FILE to open and write to that audit file without getting the appropriate lock). As an example, we will implement a solution to a mutual exclusion problem that many applications could benefit from. This problem arises from two sessions attempting to INSERT into the same table, and that table has a primary key or unique constraint on it. If both sessions attempt to use the same value for the constrained columns, the second (and third, and so on) sessions will block indefinitely, waiting for the first session to commit or rollback. If the first session commits, these blocked sessions will get an error. Only if the first session rolls back will one of the subsequent sessions have their INSERT succeed. The gist of this is that people will wait for a while to find out they cannot do what they wanted to.
This issue is avoidable when using UPDATE, because we can lock the row we want to update in a non-blocking fashion, prior to updating it. That is, instead of just executing:
update emp set ename = 'King' where empno = 1234; you can code:
select ename from emp where empno = 1234 FOR UPDATE NOWAIT; update emp set ename = 'King' where empno = 1234;
The use of the FOR UPDATE NOWAIT on the SELECT will have the effect of locking the row for your session (making it so the UPDATE will not block), or returning an ORA-54 'Resource Busy' error. If we do not get an error from the SELECT, the row is locked for us.
When it comes to INSERTs however, we have no such method. There is no existing row to SELECT and lock, and hence, no way to prevent others from inserting a row with the same value, thus blocking our session and causing us to wait indefinitely. Here is where DBMS_LOCK comes into play. To demonstrate this, we will create a table with a primary key and a trigger that will prevent two (or more) sessions from inserting the same values simultaneously. We will place a trigger on this table as well. This trigger will use DBMS_UTILITY.GET_HASH_VALUE (see the DBMS_UTILITY section later in this appendix for more information) to hash the primary key into some number between 0 and 1,073,741,823 (the range of lock ID numbers permitted for our use by Oracle). In this example, I've chosen a hash table of size 1,024, meaning we will hash our primary keys into one of 1,024 different lock IDs. Then, we will use DBMS_LOCK.REQUEST to allocate an exclusive lock based on that ID. Only one session at a time will be able to do this, so if someone else tries to insert a record into our table with the same primary key, their lock request will fail (and the error RESOURCE BUSY will be raised to them):
tkyte@TKYTE816> create table demo ( x int primary key ); Table created. tkyte@TKYTE816> create or replace trigger demo_bifer 2 before insert on demo 3 for each row 4 declare 5 l_lock_id number; 6 resource_busy exception; 7 pragma exception_init( resource_busy, -54 ); 8 begin 9 l_lock_id := 10 dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024 ); 11 12 if ( dbms_lock.request 13 ( id => l_lock_id, 14 lockmode => dbms_lock.x_mode, 15 timeout => 0, 16 release_on_commit => TRUE ) = 1 ) 17 then 18 raise resource_busy; 19 end if; 20 end; 21 / Trigger created.
If, in two separate sessions you execute:
tkyte@TKYTE816> insert into demo values ( 1 ); 1 row created.
it will succeed in the first one, but immediately issue:
tkyte@TKYTE816> insert into demo values ( 1 ); insert into demo values ( 1 ) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified ORA-06512: at "TKYTE.DEMO_BIFER", line 15 ORA-04088: error during execution of trigger 'TKYTE.DEMO_BIFER'
in the second session (unless the first session commits, and then a UNIQUE CONSTRAINT violation will be the error message).
The concept here is to take the primary key of the table in the trigger, and put it in a character string. We can then use DBMS_UTILITY.GET_HASH_VALUE to come up with a 'mostly unique' hash value for the string. As long as we use a hash table smaller than 1,073,741,823, we can 'lock' that value exclusively using DBMS_LOCK. We could use the DBMS_LOCK routine ALLOCATE_UNIQUE as well, but it comes with some amount of overhead. ALLOCATE_UNIQUE creates a unique lock identifier in the range of 1,073,741,824 to 1,999,999,999. It does this using another database table, and a recursive (autonomous) transaction. The hashing approach uses less resource, and avoids this recursive SQL call.
After hashing, we take this value, and use DBMS_LOCK to request that lock ID to be exclusively locked with a timeout of zero (it returns immediately if someone else has locked that value). If we timeout, we raise ORA-54 RESOURCE BUSY. Else, we do nothing - it is OK to INSERT, we won't block.
Of course, if the primary key of your table is an INTEGER, and you don't expect the key to go over 1 billion, you can skip the hash, and just use the number as the lock ID as well.
You'll need to play with the size of the hash table (1,024 in my example) to avoid artificial RESOURCE BUSY messages, due to different strings hashing to the same number. The size of the hash table will be application (data) specific and will be influenced by the number of concurrent insertions as well. Also, the owner of the trigger will need EXECUTE on DBMS_LOCK granted directly to them (not via a role). Lastly, you might find you run out of ENQUEUE_RESOURCES if you insert lots of rows this way without committing. If you do, you need to modify the init.ora parameter ENQUEUE_RESOURCES to be high enough (you'll get an error message about ENQUEUE_RESOURCES if you hit this). You might instead add a flag to the trigger to allow people to turn the check on and off. If I was going to insert hundreds/thousands of records, I might not want this check enabled for example.
We can 'see' our locks in the V$LOCK table, as well as the number of primary keys hashed to (the lock) it. For example, using our DEMO table from above with the trigger in place:
tkyte@TKYTE816> insert into demo values ( 1 ); 1 row created. tkyte@TKYTE816> select sid, type, id1 2 from v$lock 3 where sid = ( select sid from v$mystat where rownum = 1 ) 4 / SID TY ID1 ---------- -- ---------- 8 TX 589913 8 TM 30536 8 UL 827 tkyte@TKYTE816> begin 2 dbms_output.put_line 3 ( dbms_utility.get_hash_value( to_char(1), 0, 1024 ) ); 4 end; 5 / 827 PL/SQL procedure successfully completed.
Notice the UL lock, our user lock, with an ID1 of 827. It just so happens that 827 is the hash value of TO_CHAR(1), our primary key.
To complete this example, we need to discuss what would happen if your application permits an UPDATE to the primary key. Ideally, you would not UPDATE a primary key, but some applications do. We would have to consider what would happen if one session updates the primary key:
tkyte@TKYTE816> update demo set x = 2 where x = 1; 1 row updated.
and another session attempts to INSERT a row with that newly updated primary key value:
tkyte@TKYTE816> INSERT INTO DEMO VALUES (2); This second session will block once again. The issue here is that every process that can modify the primary key is not yet participating in our modified locking scheme. In order to solve this issue, the case whereby you UPDATE the primary key, we need to modify the times our trigger will fire to be:
before insert OR UPDATE OF X on demo If the trigger we coded fires before any INSERT, or the UPDATE of the column X, our expected behavior will be observed (and the UPDATE will become non-blocking as well).
DBMS_LOCK exposes the internal Oracle locking mechanism for our applications to exploit. As demonstrated above, we can use this functionality to implement our own custom locking that goes above, and beyond the supplied functionality. We reviewed potential uses for this facility such as a serialization device for accessing a shared resource (an OS file for example), or as a method to coordinate various conflicting processes. We took an in-depth look at using DBMS_LOCK as a tool to prevent blocking INSERTs. This example demonstrated how to use DBMS_LOCK, and how to see your locks in the V$LOCK table itself. Lastly, we closed with the importance of ensuring all sessions coordinate their activities with regards to your custom locking, by discussing how an UPDATE of a primary key could subvert our non-blocking insert logic.