Re: Implementing Incremental View Maintenance

Hi Thomas,

Thank you for your comment!

On Sat, 5 Sep 2020 17:56:18 +1200
Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> + /*
> + * Wait for concurrent transactions which update this materialized view at
> + * READ COMMITED. This is needed to see changes committed in other
> + * transactions. No wait and raise an error at REPEATABLE READ or
> + * SERIALIZABLE to prevent update anomalies of matviews.
> + * XXX: dead-lock is possible here.
> + */
> + if (!IsolationUsesXactSnapshot())
> + LockRelationOid(matviewOid, ExclusiveLock);
> + else if (!ConditionalLockRelationOid(matviewOid, ExclusiveLock))
>
> Could you please say a bit more about your plans for concurrency control?
>
> Simple hand-crafted "rollup" triggers typically conflict only when
> modifying the same output rows due to update/insert conflicts, or
> perhaps some explicit row level locking if they're doing something
> complex (unfortunately, they also very often have concurrency
> bugs...). In some initial reading about MV maintenance I did today in
> the hope of understanding some more context for this very impressive
> but rather intimidating patch set, I gained the impression that
> aggregate-row locking granularity is assumed as a baseline for eager
> incremental aggregate maintenance. I understand that our
> MVCC/snapshot scheme introduces extra problems, but I'm wondering if
> these problems can be solved using the usual update semantics (the
> EvalPlanQual mechanism), and perhaps also some UPSERT logic. Why is
> it not sufficient to have locked all the base table rows that you have
> modified, captured the before-and-after values generated by those
> updates, and also locked all the IMV aggregate rows you will read, and
> in the process acquired a view of the latest committed state of the
> IMV aggregate rows you will modify (possibly having waited first)? In
> other words, what other data do you look at, while computing the
> incremental update, that might suffer from anomalies because of
> snapshots and concurrency? For one thing, I am aware that unique
> indexes for groups would probably be necessary; perhaps some subtle
> problems of the sort usually solved with predicate locks lurk there?

I decided to lock a matview considering views joining tables.
For example, let V = R*S is an incrementally maintainable materialized
view which joins tables R and S. Suppose there are two concurrent
transactions T1 which changes table R to R' and T2 which changes S to S'.
Without any lock, in READ COMMITTED mode, V would be updated to
represent V=R'*S in T1, and V=R*S' in T2, so it would cause inconsistency.
By locking the view V, transactions T1, T2 are processed serially and this
inconsistency can be avoided.

I also thought it might be resolved using tuple locks and EvalPlanQual
instead of table level lock, but there is still a unavoidable case. For
example, suppose that tuple dR is inserted into R in T1, and dS is inserted
into S in T2. Also, suppose that dR and dS will be joined in according to
the view definition. In this situation, without any lock, the change of V is
computed as dV=dR*S in T1, dV=R*dS in T2, respectively, and dR*dS would not
be included in the results. This causes inconsistency. I don't think this
could be resolved even if we use tuple locks.

As to aggregate view without join , however, we might be able to use a lock
of more low granularity as you said, because if rows belonging a group in a
table is changes, we just update (or delete) corresponding rows in the view.
Even if there are concurrent transactions updating the same table, we would
be able to make one of them wait using tuple lock. If concurrent transactions
are trying to insert a tuple into the same table, we might need to use unique
index and UPSERT to avoid to insert multiple rows with same group key into
the view.

Therefore, usual update semantics (tuple locks and EvalPlanQual) and UPSERT
can be used for optimization for some classes of view, but we don't have any
other better idea than using table lock for views joining tables. We would
appreciate it if you could suggest better solution.

> (Newer papers describe locking schemes that avoid even aggregate-row
> level conflicts, by taking advantage of the associativity and
> commutativity of aggregates like SUM and COUNT. You can allow N
> writers to update the aggregate concurrently, and if any transaction
> has to roll back it subtracts what it added, not necessarily restoring
> the original value, so that nobody conflicts with anyone else, or
> something like that... Contemplating an MVCC, no-rollbacks version of
> that sort of thing leads to ideas like, I dunno, update chains
> containing differential update trees to be compacted later... egad!)

I am interested in papers you mentioned! Are they literatures in context of
incremental view maintenance?

Regards,
Yugo Nagata

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