Re: Lock table, Select for update and Serialization error

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of sudhir
>Sent: dinsdag 22 mei 2007 19:28
>To: Tom Lane
>Cc: Albe Laurenz; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Lock table, Select for update and
>Serialization error

[note: text reordered]

>> The critical point here is that LOCK TABLE commands at the
>start of a
>> serializable transaction are performed *before* taking the
>> transaction's snapshot (the snap happens at the first regular DML
>> command).  They not only protect against post-snap changes
>as shown by
>> Albe's example, but against uncommitted changes that were
>made before
>> transaction start (by making the serializable xact wait until those
>> changes are committed or aborted before it takes its snap).
[end reorder]
>It is not necessary that LOCK TABLE will be the first statement.
>(assuming serializable isolation level is snapshot isolation
>in postgres) For serializable transaction, snapshot should be
>taken when the 'BEGIN'
>statement is executed, and not when LOCK TABLE succeeds.

Tom is correct, the snapshot is taken at the first DML statement, NOT at
transaction start (the "begin" statement). Test it yourself.
Your 'should' be might be the expected behaviour, but its not the
implemented behaviour.

As Tom is point out, the LOCK TABLE as the first statement is to prevent
serializable errors from happening.

>Hence, uncommitted changes should be invisible to serializable
>transaction.

Uncommited changes are at all times only and only visible to the
transaction that made those changes. No other transactions, of any
isolation level, can see uncommited changes from other transactions.
Remember, postgres uses the MVCC model.

- Joris Dobbelsteen