From f7f64db598733f5258e9d4be304aadd62e54af30 Mon Sep 17 00:00:00 2001 From: Thomas Munro Date: Tue, 7 Mar 2023 11:36:44 +1300 Subject: [PATCH v3 2/2] Optimize cross-database SERIALIZABLE safe snapshots. SERIALIZABLE READ ONLY [DEFERRABLE] transactions can benefit from "safe snapshots", where they detect that no serializable anomalies could be created, so we can silently drop to the cheaper REPEATABLE READ (in other words from SSI to SI). Since a transactions connected to different databases can't access each others' data at all, except for catalogs where SSI doesn't apply anyway, there is no point in waiting for transactions in other databases. Filter them out while populating our possibleUnsafeConflicts lists. This means that non-DEFERRABLE safe snapshots might opt out immediately or sooner, and DEFERRABLE safe snapshots might not have to wait as long to get started, in scenarios where more than one database is using SERIALIZABLE transactions. Discussion: https://postgr.es/m/17116-d6ca217acc180e30%40postgresql.org --- src/backend/storage/lmgr/predicate.c | 28 +++++++++++++++++------ src/include/storage/predicate_internals.h | 1 + 2 files changed, 22 insertions(+), 7 deletions(-) diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c index 99386d36a2..c4b983ea55 100644 --- a/src/backend/storage/lmgr/predicate.c +++ b/src/backend/storage/lmgr/predicate.c @@ -1213,6 +1213,7 @@ InitPredicateLocks(void) PredXact->OldCommittedSxact->flags = SXACT_FLAG_COMMITTED; PredXact->OldCommittedSxact->pid = 0; PredXact->OldCommittedSxact->pgprocno = INVALID_PGPROCNO; + PredXact->OldCommittedSxact->database = InvalidOid; } /* This never changes, so let's keep a local copy. */ OldCommittedSxact = PredXact->OldCommittedSxact; @@ -1571,7 +1572,7 @@ GetSafeSnapshotBlockingPids(int blocked_pid, int *output, int output_size) dlist_foreach(iter, &PredXact->activeList) { SERIALIZABLEXACT *sxact = - dlist_container(SERIALIZABLEXACT, xactLink, iter.cur); + dlist_container(SERIALIZABLEXACT, xactLink, iter.cur); if (sxact->pid == blocked_pid) { @@ -1813,6 +1814,7 @@ GetSerializableTransactionSnapshotInt(Snapshot snapshot, sxact->xmin = snapshot->xmin; sxact->pid = MyProcPid; sxact->pgprocno = MyProc->pgprocno; + sxact->database = MyDatabaseId; dlist_init(&sxact->predicateLocks); dlist_node_init(&sxact->finishedLink); sxact->flags = 0; @@ -1832,7 +1834,17 @@ GetSerializableTransactionSnapshotInt(Snapshot snapshot, { othersxact = dlist_container(SERIALIZABLEXACT, xactLink, iter.cur); - if (!SxactIsCommitted(othersxact) + /* + * We can't possibly have an unsafe conflict with a transaction in + * another database. The only possible overlap is on shared + * catalogs, but we don't support SSI for shared catalogs. The + * invalid database case covers 2PC, because we don't yet record + * database OIDs in the 2PC information. We also filter out doomed + * transactions as they can't possibly commit. + */ + if ((othersxact->database == InvalidOid || + othersxact->database == MyDatabaseId) + && !SxactIsCommitted(othersxact) && !SxactIsDoomed(othersxact) && !SxactIsReadOnly(othersxact)) { @@ -1842,9 +1854,10 @@ GetSerializableTransactionSnapshotInt(Snapshot snapshot, /* * If we didn't find any possibly unsafe conflicts because every - * uncommitted writable transaction turned out to be doomed (which - * should be rare), then we can "opt out" immediately. This is a - * variation of the opt out for PredXact->WritableSxactCount == 0. + * uncommitted writable transaction turned out to be in another + * database or doomed (which should be rare), then we can "opt out" + * immediately. This is a variation of the opt out for + * PredXact->WritableSxactCount == 0. */ if (dlist_is_empty(&sxact->possibleUnsafeConflicts)) { @@ -3564,8 +3577,8 @@ ReleasePredicateLocks(bool isCommit, bool isReadOnlySafe) * xmin and purge any transactions which finished before this transaction * was launched. * - * For parallel queries in read-only transactions, it might run twice. - * We only release the reference on the first call. + * For parallel queries in read-only transactions, it might run twice. We + * only release the reference on the first call. */ needToClear = false; if ((partiallyReleasing || @@ -4875,6 +4888,7 @@ predicatelock_twophase_recover(TransactionId xid, uint16 info, sxact->vxid.localTransactionId = (LocalTransactionId) xid; sxact->pid = 0; sxact->pgprocno = INVALID_PGPROCNO; + sxact->database = InvalidOid; /* a prepared xact hasn't committed yet */ sxact->prepareSeqNo = RecoverySerCommitSeqNo; diff --git a/src/include/storage/predicate_internals.h b/src/include/storage/predicate_internals.h index 142a195d0e..7ce2882196 100644 --- a/src/include/storage/predicate_internals.h +++ b/src/include/storage/predicate_internals.h @@ -116,6 +116,7 @@ typedef struct SERIALIZABLEXACT uint32 flags; /* OR'd combination of values defined below */ int pid; /* pid of associated process */ int pgprocno; /* pgprocno of associated process */ + Oid database; /* which database is this transaction in? */ } SERIALIZABLEXACT; #define SXACT_FLAG_COMMITTED 0x00000001 /* already committed */ -- 2.39.1