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
