There are a few times when an INSERT will block. The most common scenario is when you have a table with a primary key or unique constraint placed on it and two sessions attempt to insert a row with the same value. One of the sessions will block until the other session either commits (in which case the blocked session will receive an error about a duplicate value) or rolls back (in which case the blocked session succeeds). Another case involves tables linked together via referential integrity constraints. An INSERT into a child table may become blocked if the parent row it depends on is being created or deleted.
Blocked INSERTs typically happen with applications that allow the end user to generate the primary key/unique column value. This situation is most easily avoided by using a sequence or the SYS_GUID() built-in function to generate the primary key/ unique column value. Sequences/SYS_GUID() were designed to be highly concurrent methods of generating unique keys in a multiuser environment. In the event that you cannot use either and must allow the end user to generate a key that might be duplicated, you can use the following technique, which avoids the issue by using manual locks implemented via the built-in DBMS_LOCK package.
Note The following example demonstrates how to prevent a session from blocking on an insert statement due to a primary key or unique constraint. It should be stressed that the fix demonstrated here should be considered a short- term solution while the application architecture itself is inspected. This approach adds obvious overhead and should not be implemented lightly. A well-designed application would not encounter this issue (e.g., you wouldn’t have transactions that last for hours in a concurrent environment). This should be considered a last resort and is definitely not something you want to do to every table in your application “just in case.”
With inserts, there’s no existing row to select and lock; there’s no way to prevent others from inserting a row with the same value, thus blocking our session and causing an indefinite wait. Here is where DBMS_LOCK comes into play.
To demonstrate this technique, 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. The trigger will use DBMS_UTILITY.GET_HASH_VALUE 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 1024, meaning we will hash our primary keys into one of 1024 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 that, so if someone else tries to insert a record into our table with the same primary key, that person’s lock request will fail (and the error resource busy will be raised):
Note To successfully compile this trigger, execute permission on DBMS_LOCK must be granted directly to your schema. The privilege to execute DBMS_LOCK may not come from a role.
$ sqlplus scott/tiger@PDB1
SQL> create table demo ( x int primary key ); Table created.
SQL> create or replace trigger demo_biferbefore insert on demofor each row declare
l_lock_id number;resource_busy exception;pragma exception_init( resource_busy, -54 );begin l_lock_id :=dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024 );if ( dbms_lock.request( id => l_lock_id,lockmode => dbms_lock.x_mode,timeout => 0,release_on_commit => TRUE ) not in (0,4) )thenraise resource_busy;end if;end;/
Trigger created.
SQL> insert into demo(x) values (1);1 row created.
Now, to demonstrate us catching this blocking INSERT problem in a single session, we’ll use an AUTONOMOUS_TRANSACTION so that it seems as if this next block of code was executed in another SQL*Plus session. In fact, if you use another session, the behavior will be the same. Here we go:
SQL> declare pragma autonomous_transaction;begin insert into demo(x) values (1);commit;end;/
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at “SCOTT.DEMO_BIFER”, line 14
ORA-04088: error during execution of trigger ‘SCOTT.DEMO_BIFER’
ORA-06512: at line 4
The concept here is to take the supplied primary key value of the table protected by 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.
After hashing, we take that value and use DBMS_LOCK to request that lock ID to be exclusively locked with a timeout of ZERO (this returns immediately if someone else has locked that value). If we timeout or fail for any reason, we raise ORA-54 Resource Busy. Otherwise, we do nothing—it is OK to insert, we won’t block. Upon committing our transaction, all locks, including those allocated by this DBMS_LOCK call, will be released.
Of course, if the primary key of your table is an INTEGER and you don’t expect the key to go over one billion, you can skip the hash and just use the number as the lock ID.
You’ll need to play with the size of the hash table (1024 in this 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 it will be influenced by the number of concurrent insertions as well. You might also add a flag to the trigger to allow people to turn the check on and off. If I were going to insert hundreds or thousands of records, for example, I might not want this check enabled.
Leave a Reply