Grails locking rows for update in PostgreSQL with NOWAIT

One day I needed to lock a record in grails application for update so other transactions does not mess with it while I am updating it. I started digging through the doc and found a section about locking in official documentation. It was pretty straightforward: you just need to use *lock() *method. So I did this:

def message = Message.lock(messageId)
processMessage(message)

And this was working fine. In this basic case. My actual scenario was more complicated, it involved constantly polling database for new messages to process which are not been taken by other workers.

Problem with domain lock() method that if somebody already locked this record, your transaction will wait for that guy to release that lock. In scenario when you are polling database for new messages to process this will definitely mean after some wait you will get message that somebody already start processing.

Solution is simple: we should try to get lock on first record and fail immediately if this is not possible and try to lock next record. And in PostgreSQL you can use syntax SELECT … FOR UPDATE NOWAIT to do this.

A little bit of googling showed me how to do this in grails. It was involving setting lock type directly in Hibernate transaction like this:

import org.hibernate.LockMode

// Some code skipped

Message.withTransaction {
  try {
    def msg = Message.withCriteria {
      eq "id", Long.valueOf(messageId)
      delegate.criteria.lockMode = LockMode.UPGRADE_NOWAIT
    }
    
    // Now msg is locked
  } catch (Exception e) {
    // If NOWAIT fails, exception will be caught here, deal with it
  }
}

But it did not work. Threads were still waiting for other transaction to release message to discover that it is actually being processed by someone. I enabled SQL debug output and saw that query ends with simple FOR UPDATE without NOWAIT.

Long story short: the reason for that that PostgreSQLDialect in PostgreSQL JDBC driver does not support NOWAIT locking. I was using latest driver version: 9.1-901.jdbc4.

Luckily, this problem can be easily solved by extending standard dialect. So, to be able to use FOR UPDATE NOWAIT in PostgreSQL in grails you need to do this:

Create your own dialect in src/java folder, or use mine:

package com.binarybuffer.postgres;

import org.hibernate.LockMode;
import org.hibernate.dialect.PostgreSQLDialect;

/**
 * Simple extension to standard {@link PostgreSQLDialect} adding support for
 * {@link LockMode#UPGRADE_NOWAIT} lock mode which is achieved using
 * "FOR UPDATE NOWAIT" syntax in PostgreSQL
 * 
 * @author kenota
 * 
 */
public class NoWaitPgDialect extends PostgreSQLDialect {
  @Override
  public String getForUpdateNowaitString() {
    return "for update nowait";
  }
}

Update your *DataSource.groovy *to use this dialect:

dataSource {
    pooled = true
    driverClassName = "org.postgresql.Driver"
    dialect = "com.binarybuffer.postgres.NoWaitPgDialect"
    username = "kenota"
    password = ""
}

And thats it. Now it will work as expected.

Binary Buffer

Grails locking rows for update in PostgreSQL with NOWAIT