Locking and Issues - Oracle Database Transactions and Locking Revealed - page 19

Blocked Inserts
There are 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.

■ 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 (for example, 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.”

Note

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 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 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):

■ 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.

Note

SCOTT@ORA12CR1> create table demo ( x int primary key );
Table created.

SCOTT@ORA12CR1> 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 );