Re: Select For Update and Left Outer Join

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
> On 11.07.2011 05:45, Patrick Earl wrote:
>> The ability to lock on outer joins is quite useful.  I've even
>> been contacted to ask if I was aware of any progress in this
>> area.
> 
> 9.1 has a truly serializable isolation level, so I would suggest
> using that instead of SELECT FOR UPDATE.
Heikki beat me to the big point, but I'll elaborate a bit.
First, 9.1 is in beta testing, and will probably be released this
summer 
Next, when using this feature be sure to use transactional
annotations and set things up so that a transaction which fails with
SQLSTATE 40001 is retried from the start.  If you use serializable
transactions consistently, you can drop all FOR UPDATE and FOR SHARE
clauses, and most likely all explicit locks.  (In our in-house
testing I've so far found one place where we needed to take an
explicit lock on a dummy table we created just to control access to
a sequence -- sequences don't follow normal transactional
semantics.)
Third, review this section, and consider the performance tips there:
http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-SERIALIZABLE
I'd be interested in hearing how it goes.
-Kevin