Re: Select For Update and Left Outer Join

On Jul 11, 2011, at 10:44 AM, "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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.

I find these responses to be a bit off point. Not everyone can or will want to use SERIALIZABLE. The OP's point is that we - particularly Tom - have argued in the past that we shouldn't allow this because it's too ill-defined and/or confusing. Evidently our competition does not agree, and I think that's a point worth noting.

...Robert