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.