Grails Pessimistic locking with executeUpdate()

Question

I can't find any info on how to use pessimistic locking with an executeUpdate() command in grails. Is this possible?

[Update] As per Burt's suggestion, this is the resulting query I have. Note that I have used the deprecated UPGRADE instead PESSIMISTIC_WRITE since this option is not there.

def session = sessionFactory.currentSession
def query = session.createQuery("UPDATE SensorProcessed s SET s.batch=:batch WHERE s.device.id=:deviceId AND s.batch.id=:batchId AND s.sensor.id=:sensorId AND s.rollupKey=:rollupKey")
    query.setLockMode ("s", LockMode.UPGRADE)
    query.setParameter("batch",ignored)
    query.setParameter("deviceId",device.id)
    query.setParameter("batchId",batch.id)
    query.setParameter("sensorId",sensor.id)
    query.setParameter("rollupKey",rollupKey)
    def updatedRows = query.executeUpdate()

Thanks,
Abraham.

I am getting stale object exceptions when trying to do an update, but only very rarely. I found that this generally happens when a different service is updating the same table. I wanted this second service to update it with pessimistic lock — Abe, May 28 '11 at 07:46

score 1 · Accepted Answer · answered May 27 '11 at 15:28

1

It's not supported, but it's simple enough to get access to the Hibernate API and use it there. Add a dependency injection for the session factory to your service or controller:

def sessionFactory

and then in your method replace the executeUpdate call with a regular Hibernate update:

def session = sessionFactory.currentSession
def query = session.createQuery("your HQL")
query.setLockMode alias, LockMode.PESSIMISTIC_WRITE
// set query variable values
query.executeUpdate()

answered May 27 '11 at 15:28

Burt Beckwith

75,342
5
143
156

The PESSIMISTIC_WRITE is not there in the LockMode class in grails, hence it cannot be used. Are there any alternatives? Is UPGRADE good enough? – Abe May 28 '11 at 09:30
Yes, sorry about that - use UPGRADE in 1.3 and it'll be PESSIMISTIC_WRITE in 1.4 with the upgrade of Hibernate to 3.6. – Burt Beckwith May 28 '11 at 18:17

score 0 · Answer 2 · answered May 27 '11 at 12:44

0

I just saw this:

http://grails.org/doc/latest/guide/single.html#5.3.5 Pessimistic and Optimistic Locking

note that you will have to make sure your DB supports Pessimistic locking.

answered May 27 '11 at 12:44

hvgotcodes

118,147
33
203
236

Grails Pessimistic locking with executeUpdate()

2 Answers2