Tip: creating a lock with lock option nowait in grails

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Tip: creating a lock with lock option nowait in grails

Luis Muniz-2
Hi,

The pessimistic locking implementation in grails uses the lock mode UPGRADE, which means that if a row is locked, the thread will hang for as long as some kind of timeout happens in the database, or at connection level.
For h2 the default lock timeout is set to 1s, which can be convenient for many cases, but low for many other cases. The way to change the default lock timeout in h2 is this:

new Sql(dataSource).executeUpdate('SET DEFAULT_LOCK_TIMEOUT 300000')

in milliseconds

However, my experience shows that on a oracle database, these locks are kept for a very long time, potentially locking up table rows and creating deadlocks in the future. As a lock is only released upon transaction completion, when your application is killed, the lock remains in the database for the misery of your future self.

This means that if you are using oracle, you might wish to know how to create a lock in nowait mode. This means that if the lock is taken, the query will fail with an exception and let you know instead of hanging your thread indefinitely.

My solution is using the underlying criteria object from the hibernate criteria builder and calling the hibernate api directly:

import com.b2boost.mr.domain.*
import org.hibernate.LockMode
 
 
Message.withTransaction {
Message.withCriteria{
    ....
//    instead of lock true, which will create a lock with LockMode UPGRADE
//  lock true
    delegate.criteria.lockMode=LockMode.UPGRADE_NOWAIT
  }
}

This will throw an exception and let you know if the row you are trying to acquire is locked



By the way, to find out whether your oracle database has row exclusive locks, you can execute this query:

select 'Session  '|| s.sid ||' with serial# '|| s.serial# ||' created an exclusive Row lock at '|| to_char(s.prev_exec_start,'dd-mm-yyyy hh24:mi:ss') ||' by executing '|| sqla.sql_text  from      
sys.v_$session s inner join v$sqlarea sqla on s.prev_sql_id=sqla.sql_id
inner join sys.dba_lock lck on s.sid=lck.session_id
where s.username='MR'
      and lck.mode_held like 'Row-X%'
order by 1 desc

You can kill a session with

alter system kill session '<session_id>,<serial#>'

It took me some time to gather all the info, because I was quite new to this stuff, so i figured i'd drop it in here for future reference.

Enjoy,

Luis
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Tip: creating a lock with lock option nowait in grails

littlej
When I try:

     ReportLock.withCriteria {
                    delegate.criteria.lockMode = LockMode.UPGRADE_NOWAIT
     }

It gives:

MissingPropertyException: No such property: LockMode for class: grails.orm.HibernateCriteriaBuilder
Loading...