Oracle: Performance centric ways to notify user on reaching "logical" lock expiration event

Question

Current Setup:

My application uses Java (Spring) and Oracle 11g and has functionality where logical locks are placed on an object before updates are made in the table.

For example there are 2 tables EMPLOYEE and EMPLOYEE_LOCK. When any update is made to employee, an entry is inserted into EMPLOYEE_LOCK table to indicate that for next 30 seconds a particular employee is locked.

So EMPLOYEE_LOCK table looks like below (as of 10AM)

EMPLOYEE_ID | LOCK_TYPE | LOCK_UNTIL
-------------------------------------
100         | mandatory | 10:00:30 AM (30 seconds from 10AM)

After 30 seconds a notification needs to be sent out to all clients that a lock has expired and this employee is available again for updates.

Now to identify if LOCK_UNTIL duration has reached, application makes a SQL call to database every 2 seconds to see if 10:00:30AM has reached.

Performance Issue:

This call every 2 seconds is causing lot of overhead on the database and on the application server.

I am looking for a better ways where Oracle itself initiates a notification to the server when lock expiration time has reached. Is there any way I can achieve this?

Possible solutions:

1. One solution could be to use DBMS_SCHEDULER package and create a scheduled job. But I could not find anywhere in the documentation, some way for the job to notify application server. It can send an email but that wont help me much.

2. Second option could be to use "Database Change Notification feature" but this is triggered on a DML or DDL change on the DB object which is not happening in my case.

Can some one suggest some approach where Oracle some how notifies application server when lock expiration time is reached?

Edit (To answer questions raised by Gil Shabtai)

Its probably my bad that I tried leaving some of the points from the discussion which I thought were irrelevant to the question I was asking. Here are the answers to your issues / questions raised

1. Concern:App knows lock expiry time but still checks
   Answer: A 3rd party CRM application can also add lock in the table. So application is the not the only way to add lock. So application does not always knows what lock expiration times are.

2. Concern:30 seconds locking period Performance issue
   Answer: This was just an example, the actual locking period is configuration and default is 5 seconds.

3. Concern:Caller needs to go to sleep if entity is locked.
   Answer: Caller also has a mechanism to request a notification when locks are released. All these requests go to a queue. So it is critical that the caller be notified (in the sequence they requested for a lock) when the lock is expired. So caller going to random sleep may not be an option.

My primary goal (with this question) was to see if Oracle can give me some way to identify this expiration time trigger and initiate an activity rather than Application server initiating one.

Answer 1

seems like a serious design flaw. probably resulting from a pile of flaws. why is it that the application knows when the lock should be expired, and yet it checks again and again to see if it has expired? this means that the time for expiry is meaningless. if you can change the complete design, the current situation is probably resulting from the fragmentation of the data handling parts of your application. if you can't, you still need to realize the problemof a going for a time-based lock of a rigid constant (which goes as high as 30 seconds, a performance red flag), and in the same time creating a propagating mechanism notifying the release. in my opinion, The much-less-than-perfect solution, in your case, is that those waiting for the logical lock to be released should sleep for the time-frame in which the lock is held, and then they should access and see if the record is still locked. if it still not free, they should probably sleep themselves into a random short period (realistic in your app's terms), before checking again to see if the record is locked. some upper threshold should be set for these waits as well. in general, what you need is a messaging system between waiters.