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?
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: