Skip site navigation (1) Skip section navigation (2)

Re: incoherent view of serializable transactions

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Peter Eisentraut" <peter_e(at)gmx(dot)net>,<pgsql-hackers(at)postgresql(dot)org>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: incoherent view of serializable transactions
Date: 2008-12-29 15:16:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
>>> Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On Tuesday 23 December 2008 16:51:03 Kevin Grittner wrote:
>> If you look at the serializable queries in the original post for
>> thread, it's not hard to see that this standard is not met.  The
>> insert of receipt 3 appears to happen before the update of the
>> record, since it has the old deposit date.  The transaction which
>> selects from both tables sees the update to the control record, so
>> must come after that.  Yet it doesn't see the results of the first
>> transaction.  There is no sequence of serial execution which is
>> consistent with the behavior.
> I am not sure yet whether or not your complaint is valid, but your
> are not very rigid.
> Serializability is not defined in terms of what is visible, but what
> state of the database is.
I don't read the standard that way.
I'll repeat the relevant part of the standard:
"The execution of concurrent SQL-transactions at isolation level
SERIALIZABLE is guaranteed to be serializable. A serializable
execution is defined to be an execution of the operations of
concurrently executing SQL-transactions that produces the same
effect as some serial execution of those same SQL-transactions. A
serial execution is one in which each SQL-transaction executes to
completion before the next SQL-transaction begins."
There is no end-to-end sequence in which the transactions listed in my
original email can be run that will produce the same effect that you
get within PostgreSQL, at least if you consider the results of a
SELECT statement to be significant.  (I do.)
> If you can order the transactions without overlap so 
> that the state of the database is the same as in your original
schedule, the 
> schedule is serializable.  It is not of concern what was "visible" in

> between.
On what do you base that assertion?
> You may, however, be able to transform that argument to proving 
> that a phantom read is possible, which is how the SQL standard
> defines serializability.
No it isn't.  The above quoted text is.
> Also note that discussing what is visible necessarily implies the
> of 
> another transaction that does the reading, and that transaction does
> appear to be defined in your arguments.
It was this one, in my original post:
start transaction isolation level serializable ;
select * from ctl;
-- (deposit_date shows as 2008-12-23)
select * from receipt;
-- (Only receipts 1 and 2 show for 2008-12-22.)

In response to

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2008-12-29 15:27:51
Subject: Re: incoherent view of serializable transactions
Previous:From: Nikhil SontakkeDate: 2008-12-29 15:02:13
Subject: Re: plpgsql: numeric assignment to an integer variable errors out

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group