Grails save() tries to create new object when it should update

Question

In my service code, I am trying to create or update a Person domain object:

@Transactional
    def someServiceMethod(some params....) {
        try{
            def person = Person.findByEmail(nperson.email.toLowerCase())
            if (!person) {
                person = new Person()
                person.properties = nperson.properties

            } else {
                // update the person parameters (first/last name)
                person.firstName = nperson.firstName
                person.lastName = nperson.lastName
                person.phone = nperson.phone
            }

            if (person.validate()) {
                person.save(flush: true)
                //... rest of code
            }
            // rest of other code....
           } catch(e) {
                log.error("Unknown error: ${e.getMessage()}", e)
                e.printStackTrace()
                return(null)
    }

Now above code OCCASIONALLY when trying to save a Person object with already existing email throws following exception:

Hibernate operation: could not execute statement; SQL [n/a]; Duplicate entry '[email protected]' for key 'email_UNIQUE'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '[email protected]' for key 'email_UNIQUE'

This is very strange because I am already finding the person by email and hence the save() should try to update the record instead of creating the new one.

I was wondering why is this happening!

EDIT:

I am on grails 2.4.5 and Hibernate plugin in BuildConfig is: runtime ':hibernate4:4.3.8.1'

EDIT2:

My application is on multiple servers hence synchronized block won't work

Answer 1

If this is concurrency issue, here is what we do in such case. We have a lot of concurrent background processes which work on the same tables. If there is such operation it indeed is in synchronized block, so code may look like:

class SomeService {
    static transactional = false //service cannot be transactional

    private Object someLock = new Object() //synchronized block on some object must be used

    def someConcurrentSafeMethod(){
        synchronized(someLock){
            def person = Person.findByEmail(nperson.email.toLowerCase())
            ...
            person.save(flush: true) // flush is very important, must be done in synchronized block
        }
    }
}

There are few important points to make this working (from our experience, not official):

• Service cannot be transactional - if service is transactional, transaction is commited after the method returns value and synchronization inside method will not be enough. Programmatic transactions may be another way
• synchronized method is not enough synchronized def someConcurrentSafeMethod() will not work - probably because service is wrapped in proxy
• Session MUST be flushed inside synchronized block
• every object which will be saved, should be read in synchronized block, if you pass it from external method, you may run into optimistic locking failed exception

UPDATED

Because application is deployed on distributed system, above will not solve the issue here (still may help others). After discussion we had on Slack, I just summarize potential ways to do that:

• pessimistic locking of updated objects and lock of whole table for inserts (if possible)
• moving 'dangerous' database related methods to single server with some API like REST and calling it from other deployments (and using synchronized approach from above)
• using multiple save approach - if operation fails, catch exception and try again. This is supported by integration libraries like Spring Integration or Apache Camel and is one of enterprise patterns. See request-handler-advice-chain for Spring Integration as an example
• use something to queue operations, for example JMS server

If anyone has more ideas please share them.