Re: Help understanding SIReadLock growing without bound on completed transaction

["Mike Klaas" <mike@xxxxxxxxxxxxxx>]

On Fri, May 22, 2020 at 3:15 PM, Thomas Munro <thomas.munro@xxxxxxxxx> wrote:

Predicate locks are released by ClearOldPredicateLocks(), which releases SERIALIZABLEXACTs once they are no longer interesting. It has a conservative idea of what is no longer interesting: it waits until the lowest xmin across active serializable snapshots is >= the transaction's finishedBefore xid, which was the system's next xid (an xid that hasn't been used yet*) at the time the SERIALIZABLEXACT committed. One implication of this scheme is that SERIALIZABLEXACTs are cleaned up in commit order. If you somehow got into a state where a few of them were being kept around for a long time, but others committed later were being cleaned up (which I suppose must be the case or your system would be complaining about running out of SERIALIZABLEXACTs), that might imply that there is a rare leak somewhere in this scheme. In the past I have wondered if there might be a problem with wraparound in the xid tracking for finished transactions, but I haven't worked out the details (transaction ID wraparound is both figuratively and literally the Ground Hog Day of PostgreSQL bug surfaces).

Thanks for the detailed reply, Thomas.  Is SERIALIZABLEXACT transaction ID wraparound the same as global xid wraparound?  The max transaction age in the db is ~197M [1] so I don't think we've gotten close to global wraparound lately.

Would it be helpful to cross-post this thread to pgsql-bugs or further investigate on my end

-Mike

[1] superhuman@production=> select datname, datfrozenxid, age(datfrozenxid) from pg_catalog.pg_database;

    datname    | datfrozenxid |    age    

---------------+--------------+-----------

 cloudsqladmin |   4173950091 | 169089900

 template0     |   4266855294 |  76184697

 postgres      |   4173951306 | 169088685

 template1     |   4266855860 |  76184131

 superhuman    |   4145766807 | 197273184