grails/hibernate: add pessimistic locking on using creteria

Question

I tried to add a pessimistic locking in my creteria as it shown in the doc http://grails.org/doc/latest/guide/GORM.html#locking but I had an exception:

"ERROR util.JDBCExceptionReporter - Feature not supported: "FOR UPDATE && JOIN"; SQL statement: ... org.hibernate.exception.GenericJDBCException: could not execute query"

I tried to add the lock in two places:

def ParentInstance = Parent.createCriteria().get {
    Childs {
            idEq(ChildInstance.id)
            lock true
    }

And

def ParentInstance = Parent.createCriteria().get {
    Childs {
            idEq(ChildInstance.id)      
    }
    lock true               
}

Additional question: Is it the right way to use the pessimistic locking the an association?

Thank you

Domain

class Parent{   
     static hasMany = [Childs:Child]    
}

class Child{

}

Datasource.groovy

        dataSource {
            pooled = true
            driverClassName = "org.h2.Driver"
            username = "sa"
            password = ""
        }
        hibernate {
            cache.use_second_level_cache = true
            cache.use_query_cache = false
            cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
        }
        // environment specific settings
        environments {
            development {
                dataSource {
                    dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
                    url = "jdbc:h2:myApp_prodDb;MVCC=TRUE"
                }
            }
            test {
                dataSource {
                    dbCreate = "update"
                    url = "jdbc:h2:mem:myApp_testDb;MVCC=TRUE"
                }
            }
            production {
                dataSource {
                    dbCreate = "update"
                    url = "jdbc:h2:myApp_prodDb;MVCC=TRUE"
                    pooled = true
                    properties {
                       maxActive = -1
                       minEvictableIdleTimeMillis=1800000
                       timeBetweenEvictionRunsMillis=1800000
                       numTestsPerEvictionRun=3
                       testOnBorrow=true
                       testWhileIdle=true
                       testOnReturn=true
                       validationQuery="SELECT 1"
                    }
                }
            }
        }

Answer 1

Depending on how you form your query, Hibernate will execute different queries. The way your query is written Hibernate will perform a join - this can be good for performance because it means your joined entities will already be pre-fetched in the 1 query. For locking however this is bad as every joined table would have to be locked and this can have quite an effect on deep hierarchies. Thus your database does not allow it (I'm not even sure if any other does).

You will have to perform your query without joins. Depending on your domain class implementation a simple Child.parent.id could be done without touching the database and then your query becomes a simple Parent.lock(Child.parent.id). It's hard to say without seeing the actual domain classes.

What you can always do is get the Parent in a non-locking query and then call the lock() method on the returned instance. I would suggest you take a look at this excellent article on locking things in GORM for more information.