Here is a feature idea that emerged from a pgsql-bugs thread[1] that I am kicking into the next commitfest. Example:
s1: \c db1 s1: CREATE TABLE t (i int); s1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; s1: INSERT INTO t VALUES (42); s2: \c db2 s2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; s2: SELECT * FROM x; I don't know of any reason why s2 should have to wait, or alternatively, without DEFERRABLE, why it shouldn't immediately drop from SSI to SI (that is, opt out of predicate locking and go faster). This change relies on the fact that PostgreSQL doesn't allow any kind of cross-database access, except for shared catalogs, and all catalogs are already exempt from SSI. I have updated this new version of the patch to explain that very clearly at the place where that exemption happens, so that future hackers would see that we rely on that fact elsewhere if reconsidering that. [1] https://www.postgresql.org/message-id/flat/17368-98a4f99e8e4b4402%40postgresql.org
From 2156a432b3629d1b7f8bf2fe42f641f8845baf48 Mon Sep 17 00:00:00 2001 From: Thomas Munro <thomas.mu...@gmail.com> Date: Thu, 9 Mar 2023 18:12:17 +1300 Subject: [PATCH] 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 diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c index 23461b46f6..343a1497f3 100644 --- a/src/backend/storage/lmgr/predicate.c +++ b/src/backend/storage/lmgr/predicate.c @@ -489,6 +489,13 @@ static void ReleasePredicateLocksLocal(void); /* * Does this relation participate in predicate locking? Temporary and system * relations are exempt. + * + * Note that GetSerializableTransactionSnapshotInt() relies on the latter + * exemption when considering separate databases to be fully partitioned from + * each other for the purposes of the safe snapshot optimization. If SSI is + * ever applied to system catalogs, and in particular shared catalogs, + * databases would not no longer be sufficiently isolated and that would need + * to be reconsdered. */ static inline bool PredicateLockingNeededForRelation(Relation relation) @@ -1213,6 +1220,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; @@ -1795,6 +1803,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; @@ -1814,7 +1823,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)) { @@ -1824,9 +1843,9 @@ GetSerializableTransactionSnapshotInt(Snapshot snapshot, /* * If we didn't find any possibly unsafe conflicts because every - * uncommitted writable transaction turned out to be doomed, then we - * can "opt out" immediately. See comments above the earlier check for - * PredXact->WritableSxactCount == 0. + * uncommitted writable transaction turned out to be doomed or in + * another database, then we can "opt out" immediately. See comments + * above the earlier check for PredXact->WritableSxactCount == 0. */ if (dlist_is_empty(&sxact->possibleUnsafeConflicts)) { @@ -3564,8 +3583,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 +4894,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.2
