Re: When are Predicate Locks Freed

On Fri, Dec 13, 2019 at 5:00 AM Dave Halter <davidhalter88@gmail.com> wrote:
> 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.

Right.  Even though T1 has committed, T2 and T3 can create a
"dangerous cycle" by touching data that T1 accessed, meaning that they
can't all be allowed to commit because what they've seen isn't
consistent with any serial ordering.  So the ghost of T1 continues to
affect other transactions, and predicate.c needs to figure out the
correct poltergeist duration.

> 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.

Garbage collection happens in ClearOldPredicateLocks() when a
SERIALIZABLE transactions end.  This is triggered every time the
oldest serializable transaction finishes (according to the "xmin"):

        /*
         * Check whether it's time to clean up old transactions. This
can only be
         * done when the last serializable transaction with the oldest
xmin among
         * serializable transactions completes.  We then find the "new oldest"
         * xmin and purge any transactions which finished before this
transaction
         * was launched.
         */

> 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.

Do you have long running serializable transactions?  They would
prevent cleanup of any number of short serializable transactions that
start later.  Can you tell us a bit about your workload, number of
concurrent queries, range of query durations?  Do you see a lot of
files under pg_serial (this is where finished transactions get spilled
to disk, and with the large numbers you're reporting, perhaps there
would be some of that happening too).