Re: Serializable Isolation without blocking

2010/1/8 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>:

> Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> wrote:
>
>> I assume here that PG's non-SI-compatible behavior of not always
>> rollbacking any transaction that writes to a non-last version will
>> be disabled in serializable mode.
>
> Can that currently happen in PostgreSQL's snapshot isolation?!?  I
> thought that was a READ COMMITTED issue.  If I've missed something
> there, I need to understand what.  Anybody?

Ah woops, you are right. The manual says in [1]:

---- >8 ----
13.2.2. Serializable Isolation Level

[..]

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands
behave the same as SELECT in terms of searching for target rows: they
will only find target rows that were committed as of the transaction
start time. However, such a target row might have already been updated
(or deleted or locked) by another concurrent transaction by the time
it is found. In this case, the serializable transaction will wait for
the first updating transaction to commit or roll back (if it is still
in progress). If the first updater rolls back, then its effects are
negated and the serializable transaction can proceed with updating the
originally found row. But if the first updater commits (and actually
updated or deleted the row, not just locked it) then the serializable
transaction will be rolled back with the message

ERROR: could not serialize access due to concurrent update
because a serializable transaction cannot modify or lock rows changed
by other transactions after the serializable transaction began.
---- 8< ----

Sorry for the noise,

Nicolas

[1] <URL:http://www.postgresql.org/docs/8.4/static/transaction-iso.html#XACT-SERIALIZABLE>