Re: incoherent view of serializable transactions

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.