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.
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?
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.
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
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.
Concern:30 seconds locking period Performance issue
Answer: This was just an example, the actual locking period is configuration and default is 5 seconds.
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.