Re: BUG #15875: Unexpected serializable isolation error whenINSERTing into a table

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