Обсуждение: BUG #15875: Unexpected serializable isolation error when INSERTing into a table

The following bug has been logged on the website:

Bug reference:      15875
Logged by:          Henri Cook
Email address:      github@henricook.com
PostgreSQL version: 10.6
Operating system:   docker image, postgres:10.6
Description:

Hi all,

When running the SQL below we get a serialisation error at the end of
Transaction 2. Because of the presence of the primary key PG isn't doing a
seq scan to perform the selects and manual inspection of pg_locks would seem
to imply it's not a page lock issue either. Can anyone shed some light on
why this query causes an isolation issue? The exact isolation error is:

```
ERROR:  could not serialize access due to read/write dependencies among
transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.
```

Obviously in this case a retry will probably succeed, as per the docs - but
i'm not sure it should be necessary?

Thanks in advance for your attention

Henri


Steps to recreate:

```
DROP TABLE IF EXISTS foo;
CREATE TABLE foo(id int PRIMARY KEY);
INSERT INTO foo (id) VALUES (44);
INSERT INTO foo (id) VALUES (33);
```
-- Transaction 1:
    ```
    BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SELECT id FROM foo WHERE id = 44;
    ```
-- Transaction 2:
    ```
    BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SELECT id FROM foo WHERE id = 33;
    INSERT INTO foo (id) VALUES (1);
    COMMIT;
    ```

-- Transaction 1:
   ```
    INSERT INTO foo (id) VALUES (2);
     <observe serialisation error>
   ```

On Wed, Jun 26, 2019 at 10:05 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> -- Transaction 1:
>     ```
>     BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>     SELECT id FROM foo WHERE id = 44;

For me, this acquires an SIReadLock on the page because it's an index
only scan.  Then ...

> -- Transaction 2:
>     ```
>     BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>     SELECT id FROM foo WHERE id = 33;

... this does the same, on the same page, so the transactions form a
cycle when they write.  You can see the page locks in the pg_locks
view.  In other words, you can get bogus serialisation failures when
you access nearby keys.  It seems like we could probably fix this in a
future release by switching to tuple locks (or rather TID locks) for
index only scans, as briefly discussed a while ago but not yet
followed up:

https://www.postgresql.org/message-id/CAEepm%3D2QbqQ_%2BKQQCnhKukF6NEAeq4SqiO3Qxe%2BfHza5-H-jKA%40mail.gmail.com

-- 
Thomas Munro
https://enterprisedb.com

On Wed, Jun 26, 2019 at 10:49 PM Henri Cook <github@henricook.com> wrote:
> My thanks for such a speedy reply and your analysis. This sounds like it would be really useful for preventing
'unnecessary'retries. Is there a page/mailing list/group for feature requests where I or we could formalise and track
it?

The first step is coincidentally already proposed for v13, for an
unrelated reason: https://commitfest.postgresql.org/23/2169/ .  That'd
let us predicate-lock heap tuples given only the TID from an index,
where we currently have to lock the whole heap page.  Then it should
be fairly easy to make the change to nodeIndexonlyscan.c (after we
convince ourselves that it's correct).

After thinking more about this case, though, I'm not sure it'd help
much.  We also take a page-level lock in the index itself
(nbtsearch.c), and that's probably unavoidable.  In this case 44 and
33 are on the same index page.

Hmm.  Well... it might not be totally unavoidable.  Perhaps we could
take a predicate lock on the heap tuple and also on the index tuple
(rather than index page), in the specific case that it is an = search
on a unique index that found an all-visible match.  Otherwise take a
page lock on the index as now.  Not sure if that's logically sound,
and there are probably some tricky details...

-- 
Thomas Munro
https://enterprisedb.com