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

On Wed, Jun 26, 2019 at 10:05 PM PG Bug reporting form
<noreply(at)postgresql(dot)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