Re: SERIALIZABLE with parallel query

On Wed, Feb 22, 2017 at 2:01 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I don't think I know enough about the serializable code to review
> this, or at least not quickly, but it seems very cool if it works.
> Have you checked what effect it has on shared memory consumption?

I'm not sure how to test that. Kevin, can you provide any pointers to
the test workloads you guys used when developing SSI? In theory shmem
usage shouldn't change, since the predicate locks are shared by the
cooperating backends. It might be able to use a bit more by creating
finer grain locks in worker A that are already covered by coarse
grained locks acquired by worker B or something like that, but it
seems unlikely if they tend to scan disjoint sets of pages.

Here is a rebased patch.

I should explain the included isolation test. It's almost the same as
the SERIALIZABLE variant that I submitted for
https://commitfest.postgresql.org/13/949/. That is a useful test here
because it's a serialisation anomaly that involves a read-only query.
In this version we run that query (s3r) in a parallel worker, and the
query plan comes out like this:

Gather (cost=1013.67..1013.87 rows=2 width=64)
Workers Planned: 1
Single Copy: true
-> Sort (cost=13.67..13.67 rows=2 width=64)
Sort Key: id
-> Bitmap Heap Scan on bank_account (cost=8.32..13.66 rows=2 width=64)
Recheck Cond: (id = ANY ('{X,Y}'::text[]))
-> Bitmap Index Scan on bank_account_pkey
(cost=0.00..8.32 rows=2 width=0)
Index Cond: (id = ANY ('{X,Y}'::text[]))

A dangerous cycle is detected, confirming that reads done by the
worker participate correctly in predicate locking and conflict
detection:

step s2wx: UPDATE bank_account SET balance = -11 WHERE id = 'X';
ERROR: could not serialize access due to read/write dependencies
among transactions

It's probably too late for this WIP patch to get the kind of review
and testing it needs for PostgreSQL 10. That's OK, but think it might
be a strategically good idea to get parallel SSI support (whether with
this or some other approach) into the tree before people start showing
up with parallel write patches.

--
Thomas Munro
http://www.enterprisedb.com