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.