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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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