-1/0 virtualtransaction
Hi - Does anyone have any pointers on what a virtualtransaction of '-1/0' means? I'm using SSI and an example is locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode| granted | fastpath --+--+--+-+---++---+-+---+--++-++-+-- page |16384 | 468238 | 19216 | || | | | | -1/0 | | SIReadLock | t | f This is incredibly hard to search for. I see these for page, tuple and (perhaps a clue?) one relation, and I'm seeing the page and tuple locks accumulate over time. Eventually this results in an "out of shared memory" error. Any help is appreciated. Thanks, Mike
Re: -1/0 virtualtransaction
On Mon, Apr 26, 2021 at 6:16 PM Tom Lane wrote: > Hmm, that's an invalid VXID, which would ordinarily mean that nothing > is holding the lock. There is a passing mention in mvcc.sgml that > SIRead locks sometimes need to be held past transaction end, so maybe > what you're looking at is such a lock that is no longer associated > with a specific transaction. I have to disclaim knowing much of > anything about the SSI code, though. > Hmm, yeah. I had seen discussion of this "held past transaction end" but didn't know what to make of it. The "Serializable Snapshot Isolation in PostgreSQL" paper ( https://drkp.net/papers/ssi-vldb12.pdf) makes me think this is a reasonable line of inquiry. Thanks, Mike
Re: -1/0 virtualtransaction
Hi Laurenz - On Tue, Apr 27, 2021 at 2:56 AM Laurenz Albe wrote: > Not sure, but do you see prepared transactions in "pg_prepared_xacts"? > No, the -1 in the virtualtransaction ( https://www.postgresql.org/docs/11/view-pg-locks.html) for pg_prepared_xacts was another clue I saw! But, it seems more or less a dead end as I have nothing in pg_prepared_xacts. Thanks for the idea, though. I still need to put more effort into Tom's idea about SIReadLock hanging out after the transaction, but some evidence pointing in this direction is that I've reduced the number of db connections and found that the '-1/0' locks will eventually go away! I interpret this as the db needing to find time when no overlapping read/write transactions are present. This doesn't seem completely correct, as I don't have any long lived transactions running while these locks are hanging out. Confusion still remains, for sure. Mike
Re: -1/0 virtualtransaction
On Fri, Apr 30, 2021 at 7:12 AM Thomas Munro wrote: > But do you have lots of short overlapping transactions so that there > is never a moment where there are zero transactions running? Yeah, that almost certainly explains it. Thanks very much for the explanation about the summarized locks. > The number of SERIALIZABLEXACT objects is (max_connections + > max_prepared_transactions) * 10. So, you could try increasing > max_connections (without increasing the actual number of connections) > to see if you can get to a point where you don't see these invalid > virtual xids, and then maybe it'll be able to clean up locks more > aggressively. Aha! I hadn't considered that some parameter besides max_pred_locks_per_transaction would come into play. I'll give this a shot. Thanks, Mike
Re: -1/0 virtualtransaction
In case this helps anyone else, I found a simple way to get a rough idea of what's going on, which is to run: select (select count(distinct virtualtransaction) from pg_locks) as tx_with_locks, (select count(*) from pg_stat_activity where state = 'active') as active_tx, (select count(*) from pg_locks where virtualtransaction = '-1/0') as summarized_locks; I disabled the part of my application that seemed to be causing problems with too many writes (a background cleanup task) and then triggered it from a separate process. I can see the number of transactions with locks climbing when it hits a problematic item, while the number of active transactions (of course) stays low. Mike On Fri, Apr 30, 2021 at 4:53 PM Mike Beachy wrote: > > On Fri, Apr 30, 2021 at 7:12 AM Thomas Munro wrote: > > But do you have lots of short overlapping transactions so that there > > is never a moment where there are zero transactions running? > > Yeah, that almost certainly explains it. > > Thanks very much for the explanation about the summarized locks. > > > The number of SERIALIZABLEXACT objects is (max_connections + > > max_prepared_transactions) * 10. So, you could try increasing > > max_connections (without increasing the actual number of connections) > > to see if you can get to a point where you don't see these invalid > > virtual xids, and then maybe it'll be able to clean up locks more > > aggressively. > > Aha! I hadn't considered that some parameter besides > max_pred_locks_per_transaction would come into play. I'll give this a > shot. > > Thanks, > Mike
