Oracle function concurrency

Question

I currently have an INSERT TRIGGER which in Oracle 10g runs a custom defined function that generates a funky alpha-numeric code that is used as part of the insert.

I really need to make sure that the function (or even trigger) is thread safe so that if two users activate the trigger at once, the function used within the trigger does NOT return the same code for both users.

The flow in the trigger is as follows:

START

1. determine if we need to continue based on business logic
2. run the custom function to get new code
3. use the returned code as an insert into a different table

END

The main issue is if while step 2 is running, a separate thread fires the trigger, which also gets into step 2, and returns the same code as the first thread. (I understand that this is a very tight situation, but we need to handle it).

I have thought of two main ways of doing this:

The currently best way that I have thought of so far is to lock the table used in the trigger in "exclusive mode" at the very start of the trigger, and do not specify the NOWAIT attribute of the lock. This way each subsequent activation of the trigger will sort of "stop and wait" for the lock to be available and hence wait for other threads to finish with the trigger.

I would love to lock the table any deny reading of the table, but I could seem to find out how to do this in Oracle.

My idea is not ideal, but it should work, however i would love to hear from anyone who may have better ideas that this!

Thanks a lot for any help given.

Cheers, Mark

Answer 1

No need to work out the exclusivity here. Oracle does that by managing your transactions.

The key is that each invocation of your "custom defined function" needs to return a unique code.

That means NOT using the system date/time, but something else to ensure uniqueness.

I recommend this:

select sys_guid() from dual;

Use sys_guid() to salt your function, and all should be well in trigger land.

Just PLEASE don't try to update a different table with this value, or else you'll need to deal with mutating tables and such.

Edit: Obviously, as a couple others have mentioned, using a sequence in the trigger to seed your function is another good suggestion as Oracle enforces the uniqueness. Using it as a seed though may produce a predictable result, so be careful if the "custom defined function" is a password reset or something like that.

Answer 2

"You don't need to know at all what it is, its just a code that the function generates" It must be time based then, because anything else and we WOULD need to know to be able to give an appropriate answer. Not sure whether on youur throughput. You could look at DBMS_LOCK.REQUEST as 1a, DBMS_LOCK.SLEEP as 2a and DBMS_LOCK.RELEASE as 2b. That could ensure that each lock is held for one second so that only one insert can happen in any one second.

If it is sequence based Oracle ensures you won't get the same sequence twice

If it depends on session/package state (eg incrementing within a session), then a session can only have one insert happening at a time (though it could be a multi-row insert...select or even a multi-table insert).

If it depends on database state (ie the function executes queries), you need to think in transactions, not statements. You can still use DBMS_LOCK, but you'll need to release the lock manually after commit, to ensure that the waiting session actually does wait until the new data is committed and visible.

Answer 3

One solution is to use an Oracle sequence to feed to your custom code that generates the unique string. Oracle provides sequences for just this reason - so methods generating unique ID's are thread-safe. Look here for documentation for Oracle 9i (the version I use).