Re: Select For Update and Left Outer Join

On Jul 11, 2011, at 10:44 AM, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> 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.

I find these responses to be a bit off point. Not everyone can or will want to use SERIALIZABLE.  The OP's point is
thatwe - 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