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

Re: incoherent view of serializable transactions

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Cc: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>, "Greg Stark" <greg(dot)stark(at)enterprisedb(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: incoherent view of serializable transactions
Date: 2009-01-03 22:20:10
Message-ID: 200901040020.11302.peter_e@gmx.net (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tuesday 30 December 2008 19:28:01 Kevin Grittner wrote:
> Here we go.  I've labeled the transactions consistently with new
> thread I started trying to characterize the full scope of issues and
> workarounds.

OK, I believe it now. :-)

The missing predicate locking is again at fault here.  Because this ...

> -- connection 1 (start of T0)
> start transaction isolation level serializable;
> insert into receipt values (3, (select deposit_date from ctl where k =
> 'receipt'), 4.00);

... would lock ctl where k = 'receipt' ...

> -- connection 2 (T1)
> start transaction isolation level serializable;
> update ctl set deposit_date = date '2008-12-23' where k = 'receipt';
> commit transaction;

... and then this would have to wait ...

> -- connection 2 (TN version 2)
> start transaction isolation level serializable;
> insert into receipt_totals
>   select r.deposit_date, c.deposit_date, sum(amount)
>     from ctl c join receipt r
>       on ( r.deposit_date < c.deposit_date
>        and not exists
>            (
>              select * from receipt r2
>                where r2.deposit_date < c.deposit_date
>                  and r2.deposit_date > r.deposit_date
>            )
>          )
>     group by r.deposit_date, c.deposit_date;
> commit transaction;

... and this could never be executed before T0 commits.

In response to

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2009-01-03 22:29:22
Subject: Re: Frames vs partitions: is SQL2008 completely insane?
Previous:From: Peter EisentrautDate: 2009-01-03 22:06:14
Subject: Re: incoherent view of serializable transactions

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