0

We have an application in java that is distributed and deployed on more than 20 machines. So we use DB locks to handle concurrency at the application level.
The database used is oracle here and we are using dbms_lock package. First, we call allocate_unique function from that package to get a unique id/lock handle. Using this we call dbms_lock.request function to lock. Then after the activity is complete, we use dbms_release to release the lock. Sample pseudo-code :

try{
1. take the connection (from apache db pool library)
2. take lock using the above mentioned steps 
3. perform db activities like stored proc/queries 
4. commit the transaction 
}
catch(Exception e){
1. do rollback
}
finally{
1. release lock
2. release the connection back to pool
}

Now the code works absolutely fine in all the scenarios even in exception handling. However, the problem is when there is a network issue, the DB connection is lost. Ideally, locks are released in case of commit and rollback. In this case, since the connection is lost, neither commit nor rollback would work. Also in finally, since the connection is lost, releasing the lock and connection would fail.
So in this case, we had to kill the lock manually asking dba to kill it at the database level. So is there a way to handle this gracefully?
Note: there is a parameter at oracle called idle-time which we can set at init file or through session modification, but we cannot use it since we use the connection pooling. So the connections which are lying there would also get killed . We don't want to kill the connections , only want to release the lock(after say for 15 mins) and the associated connection to be killed after releasing the lock (if possible).
There is a timeout in dbms_lock.request, but this timeout is different. It is used only if the requested lock, cannot be allocated within the time specified and gives an error, we cannot allocate you the lock. But once if the lock is achieved, there is no scope for the provision of timeout in that package if the lock is not released after some interval of time.

New Comment : Hey, i was going through the link https://programmer.help/blogs/clearing-dead-connections-using-sqlnet.expire_time.html

  1. Understanding the parameters of SQLNET.EXPIRE_TIME Use parameter SQLNET.EXPIRE_TIME to specify a time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination.

Now in java, we have connection pool. If there is abnormal termination, we java has lost the handle of the connection. So whenever new request comes, the new connection will be created. So when oracle sends a probe to verify, will this be counted that the server is active? or it will only consider the old connection which is lost?

stephen fernandes
  • 103
  • 1
  • 13
  • 1
    I would contend that you *do* want to kill the session, because if it is holding a lock, then the connection pool manager will still think that this session is in use by the Java thread that initiated the lock. That session wont be released back to the pool. Also check out max_idle_blocker_time parameter to guard against sessions holding locks that block others – Connor McDonald Jan 10 '22 at 10:42
  • @ConnorMcDonald Thanks for your reply. I think what you have said will work definitely. I have gone through the oracle documentation and it is available from 19c :( . We are on 12 version as of now. Is it possible to give a timeout that if the lock is not released, release the lock? – stephen fernandes Jan 11 '22 at 07:44
  • SQLNET.EXPIRE_TIME to specify a time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination . Will this help? – stephen fernandes Jan 11 '22 at 08:21

0 Answers0