Re: Implementing Incremental View Maintenance

On Wed, 25 Nov 2020 18:00:16 +0300
Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:

>
>
> On 25.11.2020 16:06, Yugo NAGATA wrote:
> > On Wed, 25 Nov 2020 15:16:05 +0300
> > Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> >
> >>
> >> On 24.11.2020 13:11, Yugo NAGATA wrote:
> >>>> I wonder if it is possible to somehow use predicate locking mechanism of
> >>>> Postgres to avoid this anomalies without global lock?
> >>> You mean that, ,instead of using any table lock, if any possibility of the
> >>> anomaly is detected using predlock mechanism then abort the transaction?
> >> Yes. If both transactions are using serializable isolation level, then
> >> lock is not needed, isn't it?
> >> So at least you can add yet another simple optimization: if transaction
> >> has serializable isolation level,
> >> then exclusive lock is not required.
> > As long as we use the trigger approach, we can't handle concurrent view maintenance
> > in either repeatable read or serializable isolation level. It is because one
> > transaction (R= R+dR) cannot see changes occurred in another transaction (S'= S+dS)
> > in such cases, and we cannot get the incremental change on the view (dV=dR*dS).
> > Therefore, in the current implementation, the transaction is aborted when the
> > concurrent view maintenance happens in repeatable read or serializable.
>
> Sorry, may be I do not correctly understand you or you do not understand me.
> Lets consider two serializable transactions (I do not use view or
> triggers, but perform correspondent updates manually):
>
>
>
> create table t(pk integer, val int);
> create table mat_view(gby_key integer primary key, total bigint);
> insert into t values (1,0),(2,0);
> insert into mat_view values (1,0),(2,0);
>
> Session 1: Session 2:
>
> begin isolation level serializable;
> begin isolation level serializable;
> insert into t values (1,200);                         insert into t
> values (1,300);
> update mat_view set total=total+200  where gby_key=1;
> update mat_view set total=total+300 where gby_key=1;
> <blocked>
> commit;
> ERROR:  could not serialize access due to concurrent update
>
> So both transactions are aborted.
> It is expected behavior for serializable transactions.
> But if transactions updating different records of mat_view, then them
> can be executed concurrently:
>
> Session 1: Session 2:
>
> begin isolation level serializable;
> begin isolation level serializable;
> insert into t values (1,200);                         insert into t
> values (2,300);
> update mat_view set total=total+200  where gby_key=1;
> update mat_view set total=total+300 where gby_key=2;
> commit;                                                      commit;
>
> So, if transactions are using serializable isolation level, then we can
> update mat view without exclusive lock
> and if there is not conflict, this transaction can be executed concurrently.
>
> Please notice, that exclusive lock doesn't prevent conflict in first case:
>
> Session 1: Session 2:
>
> begin isolation level serializable;
> begin isolation level serializable;
> insert into t values (1,200);                         insert into t
> values (1,300);
> lock table mat_view;
> update mat_view set total=total+200  where gby_key=1;
> lock table mat_view;
> <blocked>
> commit;
> update mat_view set total=total+300 where gby_key=1;
> commit;
> ERROR:  could not serialize access due to concurrent update
>
>
> So do you agree that there are no reasons for using explicit lock for
> serializable transactions?

Yes, I agree. I said an anomaly could occur in repeatable read and serializable
isolation level, but it was wrong. In serializable, the transaction will be
aborted in programmable cases due to predicate locks, and we don't need the lock.

However, in repeatable read, the anomaly still could occurs when the table is
defined on more than one base tables even if we lock the view. To prevent it,
the only way I found is aborting the transaction forcedly in such cases for now.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>