When are Predicate Locks Freed

From: Dave Halter <davidhalter88(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: When are Predicate Locks Freed
Date: 2019-12-12 16:00:03
Message-ID: CAA=HWYgJRB8yjDy3_HVXHJCdZCfYYt2bbVfEBdV0ANpOBfHkhA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello PostgreSQL Community

I have worked quite a bit with serializable transactions. I'm trying
to understand when predicate locks are freed. I read the whole
README-SSI [1], but I'm still not sure when a predicate lock gets
dropped.

What I learned from that README is that predicate locks are not freed
at the moment where a transaction passes, but only at a later stage.
This makes sense, because some concurrent transactions might be
invalidated by the predicate locks that are not needed anymore for a
transaction that was just committed.

What I don't understand is: We have only short transaction (<1s), but a

select count(*) from pg_locks where mode = 'SIReadLock';

would vary by a lot. It typically went up all the way to 300k. At this
point it would drop to almost zero (always <10k) and rise again. This
cycle pretty consistently happened every three minutes. (I think it
looked a bit more like 2 minutes 50 seconds, but I wasn't measuring it
exactly). Is there a "garbage collection" happening every few minutes?
Since we scaled up our service by quite a bit, we increased
`max_pred_locks_per_transaction`. Now we are at 1.0m to 1.3m
predicate locks with the same ~3 minute cleanup period.

Does anybody have any insights in why this might be happening? I would
also be very interested in good explanations about when predicate
locks are actually freed. I tried reading the C code, but couldn't
really figure out where that was happening.

A very happy PostgreSQL user.
Thanks for your continued efforts!
~ Dave

[1]: https://github.com/postgres/postgres/blob/db2687d1f3787aa8113b3dbb358153feee30c64c/src/backend/storage/lmgr/README-SSI

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2019-12-12 16:17:35 Re: Backup and Restore
Previous Message Grigory Smolkin 2019-12-12 15:39:16 Re: Backup and Restore