0

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.

  • This seems like a bad idea architecturally. If you want to implement pessimistic locking, lock the row. If you want to implement optimistic locking, do that. Creating a separate table and managing your own locks does not sound sensible. If you really, really want to do that, your job could send a message via Oracle AQ that goes to a JMS queue on the middle tier or your job could delete the row from the lock table while the middle tier is subscribed to changes to that table. But neither of these approaches would strike me as particularly reasonable. – Justin Cave Oct 5 '15 at 18:11
  • Explicit locking seems required as the entity / resource in the DB gets updated over period of time. (Eg: In case of cell phone user movement: (1)Subscriber is locked (2)port out request sent to service provider (3) Request fulfilled by relevant parties (4)sub resource is unlocked. So row level locking may not be an option. But I like your suggestions where Oracle either removes row from the table that qualifies as an event or sends message using Oracle AQ. I will try these approaches and update the result. Thanks – Sauchin Oct 5 '15 at 20:44
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.

  • I have replied as an edit to the question itself as reply was too big to fit in reply section – Sauchin Oct 5 '15 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.