2

Our app is mostly using optimistic locking using Hibernate’ s versioning support. We are planning to implement pessimistic locking in one particular scenario. I don’t have much experience with pessimistic locking so please excuse if this question sounds naïve.

When a user shows intention for updating an entry - we lock the corresponding DB row using “select for update”. Now, if this user takes a long time to commit his changes are forgets about it after locking, how do we unlock this lock using some timeout/rollback mechanism? So that the row doesn’t stays locked for a very long time and disallowing all other users to edit it.

I doubt if this will be handled at Weblogic-JTA-Spring transaction mechanism we are using – where we already have a transaction timeout of 30 mins. (??)

So, should this rollback be handled directly at Oracle level. If yes, then how? Please advise on best way to handle this so that such locks don’t stay lingering around for too long.

haps10
  • 3,496
  • 6
  • 32
  • 38
  • If the session is closed at the database level, then all row locks are discarded. What is the session timeout at this level? – fge Jan 24 '13 at 04:24
  • ok I did some search around and got this: SELECT PROFILE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'IDLE_TIME'; Which on our DB says UNLIMITED - i think this means no session timeout. right? – haps10 Jan 24 '13 at 04:49
  • You _could_ pass the equivalent of a callback function to your method that polls the time it's taken to execute. Once it reaches x seconds you rollback the session... or something like that. – Ben Jan 24 '13 at 08:59

2 Answers2

2

Locks will be released only when the transaction ends. The transaction will end either when an explicit commit or rollback is issued to the database or when the database session is terminated (which does an implicit rollback). If your middle tier is already set to rollback any transactions that are open for more than 30 minutes, that would be sufficient to release the locks.

If you have a Java application running in a Weblogic application server, however, it strikes me as unusual for pessimistic locking to be appropriate. First, I assume that you are using a connection pool in the middle tier. If that is the case, then one database connection from the connection pool would need to be held by the middle tier for the length of the transaction (up to 30 minutes in this case). But allowing one session to hold open a particular database session for an extended period of time defeats the purpose of having a connection pool. Normally, dozens if not hundreds of application sessions can share a single connection from the connection pool-- if you are going to allow pessimistic locking, you're now forcing a 1:1 relationship between application sessions and database sessions for those sessions.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks. Yes, I underestand we cannot have best of both worlds. Have Weblogic manage our jdbc sessions and then single out some to manage ourselves for pessimistic locking. – haps10 Feb 06 '13 at 10:32
0

There are numerous cases that optimistic locking cannot replace pessimistic locking. Lock timeout is handled in the database. Refer to this page about how to configure it in Oracle

Can Oracle's default object lock timeout be changed?

Community
  • 1
  • 1
Water Guo
  • 79
  • 5