Re: Serialization errors despite KEY SHARE/NO KEY UPDATE

[Sorry for the delay in responding -- was on vacation.]

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote:
> On 10/2/15 11:44 AM, Olivier Dony wrote:
>> On 10/02/2015 12:28 AM, Jim Nasby wrote:
>>> On 9/29/15 9:47 AM, Olivier Dony wrote:

>>>> -- Setup tables
>>>> CREATE TABLE users ( id serial PRIMARY KEY,
>>>> name varchar,
>>>> date timestamp );
>>>> CREATE TABLE orders ( id serial PRIMARY KEY,
>>>> name varchar,
>>>> user_id int REFERENCES users (id) );
>>>> INSERT INTO users (id, name) VALUES (1, 'foo');
>>>> INSERT INTO orders (id, name) VALUES (1, 'order 1');
>>>>
>>>>
>>>> -- Run 2 concurrent transactions: T1 and T2
>>>> T1 T2
>>>> |-----------------------------|----------------------------------|
>>>> BEGIN ISOLATION LEVEL
>>>> REPEATABLE READ;
>>>>
>>>> UPDATE orders
>>>> SET name = 'order of foo',
>>>> user_id = 1
>>>> WHERE id = 1;
>>>>
>>>> BEGIN ISOLATION LEVEL
>>>> REPEATABLE READ;
>>>>
>>>> UPDATE users
>>>> SET date = now()
>>>> WHERE id = 1;
>>>>
>>>> COMMIT;
>>>>
>>>> UPDATE orders
>>>> SET name = 'order of foo (2)',
>>>> user_id = 1
>>>> WHERE id = 1;
>>>>
>>>> T1 fails with:
>>>> ERROR: could not serialize access due to concurrent update
>>>> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
>>>
>>> This isn't a locking failure, it's a serialization failure. I'm not sure
>>> why it's happening though... is there an index on date?
>>
>> I don't think so. I can reproduce the problem with the queries quoted
>> above, and the only index that seems to be present is the PK

> I'm not sure. Perhaps Kevin Grittner (author of serializable patch) can
> shed some light.

This has nothing to do with the serializable patch[1], but it is
related to the new multixact code in 9.3. I thought I remembered
that this was a regression from 9.2 behavior which was fixed in a
patch after being reported, and sure enough tests of the tips of
branches showed that prior to 9.3 T2 blocked until T1 committed,
rather than generating a serialization failure, while on 9.5 and
master there was no blocking or error.

Using `git bisect` to see where this was fixed shows:

commit 05315498012530d44cd89a209242a243374e274d
Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Date: Tue Jul 29 15:41:06 2014 -0400

Avoid uselessly looking up old LOCK_ONLY multixacts

Commit 0ac5ad5134f2 removed an optimization in multixact.c that skipped
fetching members of MultiXactId that were older than our
OldestVisibleMXactId value. The reason this was removed is that it is
possible for multixacts that contain updates to be older than that
value. However, if the caller is certain that the multi does not
contain an update (because the infomask bits say so), it can pass this
info down to GetMultiXactIdMembers, enabling it to use the old
optimization.

Pointed out by Andres Freund in 20131121200517(dot)GM7240(at)alap2(dot)anarazel(dot)de

I don't know whether, after being in 9.5 for over a year it could
be considered for a back-patch to 9.3 and 9.4; I leave that to
Álvaro and others who have been repairing the multixact problems to
determine.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1] The serializable patch has no effect if there are not
serializable transactions in use, and it doesn't generate errors
"due to concurrent update" in any new situations.