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:
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:
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:
- Update your *DataSource.groovy *to use this dialect:
And thats it. Now it will work as expected.