Grails usage of withTransaction and pessimistic Locking

Question

I was working on a Grails app and using console plugin to execute code at runtime. And while trying to save an object got the exception HibernateOptimisticLockingFailureException .

Then after googling got to know to use withTransaction which has worked fine.

Show.withTransaction {
   Show s=Show.get(1111) // any show id
   s.name=s.name+'1'
   s.save()
}

I have also tried lock (pessimistic locking) which has not worked and thrown exception of optimistic locking:

Show s=Show.lock(1111)
s.name=s.name+'1'
s.save(flush:true)

why the pessimistic locking snippet has not worked?

For more details :

class Content {

   User createdBy
   User lastUpdatedBy
   Date dateCreated
   Date lastUpdated

   static constraints = {
      createdBy nullable: true
      lastUpdatedBy nullable: true
   }

   static mapping = {
      tablePerHierarchy(false)
   }

}

class Show extends Content {

   String name
   ShowCategory category
   ShowType showType

   static hasMany = [images: Image]

   static constraints = {
      name blank: false, unique: true
      showType nullable: true
      images nullable: true, blank: true
      category nullable: true
   }

   static mapping = {
      table("some_table_show")
      images cascade: 'all-delete-orphan'
      name index: 'name_idx'
      images cache: true
   }

   def afterInsert() {
      Plug.cacheService.deleteCache() // some redis cache usage
   }

   def afterUpdate() {
      Plug.cacheService.deleteCache() // some redis cache usage
   }

}

Exception after using lock (pessimistic locking):
org.springframework.orm.hibernate3.HibernateOptimisticLockingFailureException: Object of class [com.myApplication.Show] with identifier [1111]: optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [com.myApplication.Show#1111]
    at com.myApplication.cacheService.deleteCache(CacheService.groovy:286)
    at com.myApplication.Show.afterUpdate(Show.groovy:161)
    at org.grails.datastore.mapping.engine.event.AbstractPersistenceEventListener.onApplicationEvent(AbstractPersistenceEventListener.java:46)
    at Script1.run(Script1.groovy:17)
    at org.grails.plugins.console.ConsoleService.eval(ConsoleService.groovy:57)
    at org.grails.plugins.console.ConsoleService.eval(ConsoleService.groovy:37)
    at org.grails.plugins.console.ConsoleController$_closure2.doCall(ConsoleController.groovy:61)
    at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:198)
    at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [com.myApplication.Show#1111]
    ... 12 more

Answer 1

In general withTransaction is a hack and should be avoided, primarily because it allows you do splatter transactional code all over your codebase instead of using tiers and separating concerns. Use transactional services instead, and annotate either the class or individual methods depending on which methods do what. The net effect is the same since the same underlying Spring functionality is used in withTransaction and in transactional services.

It'd be difficult to provide help without more specific information about what your code looked like and the error message that you saw. If you can reproduce that it would help a lot.

You should try to use optimistic locking in general if there's not a high likelihood of concurrent updates, and deal with the occasional collisions when they happen. Use pessimistic locks where needed of course, but be paranoid about using them because they will affect scalability. And as I mentioned in a comment for the other answer, all locking must be done in a transaction, otherwise the lock will be immediately freed.

It's moderately likely to see a Hibernate optimistic locking error/exception when using pessimistic locking. If you look at the SQL Hibernate generates for updates, it's usually something like update <tablename> set foo=?, bar=?, ... where id=? and version=?. The where clause is overdetermined - it's sufficient to just have the id there, since it's either an id that corresponds to an existing row or it isn't. But it looks at the JDBC update count after running that SQL, and if it's zero, the version value must have been off, and it assumes that this must have been caused by someone else editing the file and incrementing the version.

But weird things can affect the version. Hibernate considers collections to be a sort of property, so if you add an item to a collection that will increment the version of the owner. This can be unexpected, e.g. adding a new Book for an Author will update the Author's version. Weirder still, adding a new many-to-many mapping, e.g. granting a role to a user, versions both the role and the user. This is because the user's roles collection property changed, but also the role's users collection changed. This can wreak havoc because you shouldn't have to lock the user and the role to grant a role to a user, and you can't lock the elements of a collection.

Answer 2

Pessimistic locking relies on the database to implement the locking behaviour. I have observed that it works as documented with MySQL and Microsoft SQL Server, but not with the H2 database used in Grails development environment.