One lock order across partition schemas

Issue Systemorph/MeshWeaver.Plugins#1397. On 2026-09-06 the memex-cloud database migration Job failed twice before succeeding, both times identically:

SqlState: 40P01
MessageText: deadlock detected
Where: SQL statement "ALTER TABLE user_effective_permissions RENAME TO user_effective_permissions_old"
       PL/pgSQL function rebuild_user_effective_permissions() line 214 at SQL statement
       SQL statement "SELECT advancedbusinessrules.rebuild_user_effective_permissions()"
       PL/pgSQL function mw_auth_mirror_heal_batch(text,integer) line 695 at EXECUTE

memex (137 partition schemas) migrated first time; memex-cloud (209) did not. The retry hides it — backoffLimit: 6 restarts the batch from the first schema, so the successful attempt re-walked 109 schemas two earlier attempts had already done, and the Job still goes green.

Identifying the counterparty, by elimination

The client-side trace deliberately withholds the "Process N waits for …; blocked by process M" detail (Detail redacted as it may contain sensitive data), so the second transaction has to be derived rather than read.

A deadlock needs a cycle: some other transaction must hold a lock on advancedbusinessrules.user_effective_permissions and be waiting on one the heal already held.

• Not another rebuild. rebuild_user_effective_permissions()'s first statement is PERFORM pg_advisory_xact_lock(hashtext('meshweaver_uep_rebuild')) — one global, transaction-scoped lock shared by every schema's rebuild (the 2026-07-19 fix, pinned by ConcurrentUepRebuildTests). Rebuilds queue; they cannot interleave. That also rules out public.trg_group_changed(), which reaches the same tables only through a rebuild.

• Not a provisioner. ensure_partition_schema takes pg_advisory_xact_lock('meshweaver_partition_provision_' || name) before touching any catalog row, and its documented order is partition → uep, never the reverse.

• A READER, then — the cross-schema fan-out, which takes ACCESS SHARE on every branch's mesh_nodes and user_effective_permissions in ONE statement. The same pods logged it, in the same window:

  [CrossSchema] SLOW mesh_nodes: 3092ms — 1/0 rows across 209 of 209 partition schema(s)
  [CrossSchema] SLOW access:     4019ms — 32/0 rows across 93 of 137 partition schema(s)
     query nodeType:AccessAssignment path:- scope:Exact filtered — this query FANNED OUT
  

The mechanism: two acquirers, two orders

PostgreSQL locks every relation a statement names at parse time, in the order the statement names them, and holds them to commit. So the branch order of a 209-arm UNION ALL is its lock order.

The fan-out's branch set is born in three SELECT DISTINCT statements, none of which stated an order — GetSchemasWithTableAsync, GetSchemasHavingTableAsync and, on the genuinely unanchored route, GetPartitionReadableSchemasAsync, whose result replaces the branch set in EnumerateFanOutAsync. An unordered SELECT may return sorted rows; it may not be relied on to.

The cycle is then: reader holds S_late, waits for S_early ⨯ heal holds S_early, waits for S_late. With 209 schemas and a rolling deploy, an overlap is likely rather than exotic — which is exactly why the 137-schema instance survived and the 209-schema one did not. It scales with partition count, so it gets worse on its own.

The cure: one total order, on both sides

Put every multi-schema acquirer in ascending schema-name order and the cycle is unconstructible, not merely unlikely:

the reader holds a prefix [1…m] and waits on m+1; the heal holds an ascending run [k…j] and waits on j+1. A cycle needs m ≥ j+1 and m+1 ≤ j — a contradiction.

That is a lock-ordering discipline, the textbook cure for a deadlock. It is not a retry, not a wider lock_timeout, and not a gate serializing the callers.

PartitionSchemaLockOrder (in MeshWeaver.Hosting.PostgreSql) states the order and is applied:

• in PostgreSqlSqlGenerator.GenerateCrossSchemaSelectQuery — the one seam every cross-schema UNION passes through (PostgreSqlCrossSchemaQueryProvider and PostgreSqlStorageAdapter), so no caller can hand it an un-canonical branch set;
• in the three branch-set producers, so the narrowing lists and the logs agree with it;
• in public.trg_group_changed(), which now sweeps ORDER BY n.nspname.

mw_auth_mirror_heal_batch already walked ascending; it is the order everything else was aligned to, and PartitionSchemaLockOrderPostgresTests.TheAuthMirrorHeal_WalksSchemasAscending pins that it stays so — aligning readers to a walk that later changed would be meaningless.

Ordinal, deliberately. Both SQL walks order by information_schema.tables.table_schema and pg_namespace.nspname, which are of type name — collation "C", i.e. plain byte order. StringComparer.Ordinal is that same order for the ASCII names a partition schema can have. A culture-aware comparison places a_b and ab the other way round from Postgres and would reopen the cycle on exactly the pairs nobody writes a test for.

What this composes with

This is the third lock-order rule on this mesh, and it replaces neither of the first two:

1. partition → uep, never uep → partition (the provisioning proc);
2. one global advisory lock serializing every rebuild (2026-07-19);
3. ascending schema name, across schemas (this page).

The 2026-09-03 mitigation stays too and is not the fix: SET LOCAL lock_timeout = '2s' inside the heal batch, plus a retry of that batch from the same cursor, makes the heal the side that yields (55P03) instead of the side that dies. That bounds the blast radius of contention; ordering removes the cycle that caused it.

What is still open

The unanchored callers themselves. nodeType:AccessAssignment path:- scope:Exact is not on unanchored-queries.allow, so on a production host (UnanchoredQueryPolicy.ServeAndReport) it fans out over every schema and is reported at Error. Ordering makes that fan-out deadlock-free; it does not make it cheap. Anchoring it — and the mesh_nodes shape logged beside it — remains the work that removes the 209-schema statement altogether, and it is tracked where the fan-out census lives (UnanchoredQueryPolicy, and core's Doc/Architecture/CrossSchemaFanOutElimination).

A cheaper swap. rebuild_user_effective_permissions() still takes three ACCESS EXCLUSIVE locks to swap one table (RENAME ⨯ 3). Fewer acquisitions would shorten the window, which is worth having — but it is a smaller window, not a closed cycle, so it is not a substitute for the order.

How the order is verified

src/MeshWeaver.Hosting.PostgreSql.Test/PartitionSchemaLockOrderTests.cs observes the ORDERS — not a timing, and not a deadlock race, which would be a flake generator either way:

• the branch order in the generated statement follows the schema list (the load-bearing link between "list order" and "lock order"), and comes out canonical for a scrambled input, mesh_nodes arms and content arms alike;
• the group-recompute sweep, extracted from the deployed function body rather than restated, returns partition schemas ascending against a real Postgres whose creation order is the reverse — and the body installed in the database is checked to be that same text;
• the heal's per-batch cursor comes back strictly ascending.

Measured on the change: 7 passed / 0 failed with it, 4 passed / 3 failed with the src/ half reverted (the two generator tests, and the sweep — which walked lockorder_h … lockorder_a).