Tuesday, June 28, 2011

select for update … skip locked in PostgreSQL


At PGCon’11 I’ve told that select for update … skip locked is highly needed in PostgreSQL specially for using  with various async messaging queues. Well, there is still no Oracle-like skip locked, but transactional behavior for advisory locks was introduced in PostgreSQL 9.1 - http://www.postgresql.org/docs/9.1/static/explicit-locking.html 

In version 9.0 and earlier advisory locks were not fully transactional: once acquired within transaction, lock will still be held even after calling rollback.  So now looks possible to loop through your records trying to acquire advisory lock meaning if it fails your row already locked and you need to skip it.