wiki-cafe/member-console
1
1
Fork 0
Code Issues 204 Pull Requests Packages Projects 1 Releases Activity
Files
main
member-console/docs/database-locks.md
T
cgalo5758 9f103c13ef Record a first lifecycle request under one lock and one read 
RecordFirstLifecycleRequest took the instance lock and read the latest
generation, then called RecordLifecycleRequest, which took the lock and
read the latest generation again. It now checks its address, locks and
reads once, refuses a later generation of an instance that already
holds a request, reads the content, and passes the latest generation to
decide, the per-instance decision RecordLifecycleRequest also calls
(recordNew above the latest, answerAddress otherwise). decide takes a
*Queries and opens nothing. The person is still parsed after the
first-request check, so a malformed person on a used instance under a
later generation still answers a conflict.

A new create, an identical repeat and a create above the ceiling now run
two statements fewer: the second LockLifecycleInstance and
LatestLifecycleGeneration. The three rows of the lifecycle answers
golden ("first: a new request", "first: an identical repeat", "first:
above the ceiling on an empty instance") lose that pair; they were
edited by hand before the run and the test passed without -update, so
every other row is byte-equal. Answers, rows, errors and writes do not
change unless an instance's latest generation moves between the
parent's two reads. Inserts happen only under the instance lock, and
status and acknowledgement updates leave the generation alone, so the
one writer that can move it is DeleteUnstartedLifecycleRequest, which
takes no instance lock. If it commits between the parent's two reads, a
double submit's repeat is recorded as a new create there and answered a
conflict here, from the one read. That ordering needs a commit between
two statements of one transaction and is not probed.

docs/database-locks.md, "Lifecycle intake", said the second lock was
harmless; it now describes the single lock and read, and says the delete
of an unstarted request runs outside the instance lock. The comment
"taking it again ... is harmless" is deleted with the second lock.

How it was shown to change no output: the packages
internal/integration and internal/integrations/fedwiki/... gave the same
302 test and subtest names with the same results at the parent and here
(301 pass, 1 skip, the replay recorder, skipped on both sides); the
Temporal extraction (activities, workflows, workflow bodies, payload
types) is byte-equal; FedWiki's recorded histories replay; the sites
walkthrough, which creates a site with a double submit, passes on both
sides; the project lint prints the same on both sides; the multiset of
lifecycle.go's string literals is equal (20 formats, no literal added or
removed); the lock-key query, the single address read in heldAt, the
unchanged sameLifecycleContent and the two spec and answer types are
unchanged. 9 break records, 8 per side (one break's code moved into
decide, so it has an old and a new record), each caught by the old test
at the parent and the new test here. Cognitive
complexity (pinned golangci-lint): RecordLifecycleRequest 8 to 6,
RecordFirstLifecycleRequest 7 to 9, decide 1.

Unprobed: the race with an unstarted request's delete, for the reason
above; every database error after the first statement, because no test
makes one fail and nothing else; the *sql.Conn path, which inOwnTx
treats as a transaction as before and no caller uses.
2026-10-11 18:58:35 -05:00

341 lines
23 KiB
Markdown
Raw Permalink Blame History

---
title: "Database Locks"
audience: [developer]
summary: "Every advisory lock and serializing row lock the console takes, how each key is built, and the order each path takes them in."
---
# Database Locks
This page describes the locks as the code takes them today. It records what
each lock serializes, who takes it, and the order each path takes several.
It does not describe a target design. Two pieces of planned work change the
page: #186 gives every advisory key one family prefix and writes one lock
order, and #165 adds a lock on the usage row under the FedWiki workspace
lock. Whoever lands either edits this page in the same change.
The code names functions and sqlc queries; so does this page. Line numbers
are left out because they move.
Every advisory lock is transaction-scoped (`pg_advisory_xact_lock`), so
Postgres releases it at commit or rollback. No code takes a session-level
advisory lock, `LOCK TABLE`, or an explicit `FOR SHARE`.
## The locks
### Advisory locks
| Lock | Key | Mode | Taken by | Serializes |
|---|---|---|---|---|
| Materialization rendezvous | `hashtextextended('entitlement_materialization', 0)` | shared for a transaction that materializes a pool; exclusive for a rule change and for a change to a ladder's tiers (tier add, tier delete, tier removal, the reorder's renumbering) | the `entitlements.Materializing` and `entitlements.RuleChange` transaction kinds (`BeginMaterializing`, `BeginRuleChange`), as the transaction's first statement | A rule change or a tier change against every materializing transaction: each one finishes before the change or starts after it, so a conferral reads one set of rules and one order of each ladder. Materializing transactions do not wait on each other here. |
| Subscription | `hashtext(<Stripe subscription id>)::bigint` | exclusive | `fulfillment.ReconcileSubscription` | Two reconciles of one subscription (the checkout return and a webhook), so they converge instead of racing the exclusion constraint on subscription periods. |
| Invoice | `hashtext(<Stripe invoice id>)::bigint` | exclusive | `invoiceReconcile.converge` in `internal/integrations/stripe/workflows` | Two reconciles of one invoice (two events, or an event and the sweep), so the second finds what the first wrote. |
| Product sync | `hashtext(<product id>)::bigint` | exclusive | `catalogsync.Sync` in `internal/integrations/stripe/catalogsync` | Two sync clicks for one product, so the mapping writes and the outbox enqueue happen once. |
| FedWiki workspace | `hashtext(<workspace id>)::bigint` | exclusive | `withWorkspaceTx` in `internal/integrations/fedwiki/web`, for the status-change and swap handlers | One workspace's rotations of its active site, so two tabs cannot interleave the cooldown check and stamp or the choice of the site to park. |
| Lifecycle instance | `hashtextextended('lifecycle:' \|\| <instance id>, 0)` | exclusive | the `LockLifecycleInstance` query, run by `integration.RecordLifecycleRequest`, `RecordFirstLifecycleRequest` and the FedWiki activity helper `finishAllWith` | One instance's requests: the latest-generation check and the insert are one step, and an outcome is recorded against a request that is still accepted. |
| Desired-state address | `hashtextextended('desired-state:' \|\| <connection id> \|\| ':' \|\| <recipient kind> \|\| ':' \|\| <recipient id>, 0)` | exclusive | the `LockDesiredStateAddress` query, run by `lockAddress` in `internal/workflows/desiredstate` | Every write to the content or generation of the record at one (connection, recipient) address, and to its projection, including an address that has no record yet. |
| Domains registry | the constant `registryAllocationLock` (the ASCII bytes of "domains") | exclusive | `domains.Registry.WithLock` | Every mutating allocation in the registry, one lock for the whole namespace, because the disjointness rules span rows no database constraint can compare. |
### Row locks
| Lock | Row | Mode | Taken by | Serializes |
|---|---|---|---|---|
| Pool row | `core.resource_pools` row of one pool | `FOR UPDATE` | the functions `core.confer`, `core.end_conferral`, `core.align_conferral_shape`, `core.update_conferral_bounds` and `core.settle_obligation`; `LockPools` (through the `LockResourcePool` query), called by `RevokeGrantTx`, `ManageGrantTx`, `ResumeReplacedGrantsTx`, `settlePool`, `EnactDefault` and `RemoveTier`; and `LockPoolOfGrant` (through the `LockGrantPool` query), called by `ExpireGrantTx` and `ExtendWaitingGrantTx` | Everything that changes a pool's positions or entitlements: conferral, endings, grant acts, tier removal, a default change, rule-change settlement and grant resumption. |
| Entitlement set row and rule row | `core.entitlement_sets` row and `core.entitlement_set_rules` row | `FOR UPDATE` | `core.commit_rule_change` (the set row also by the `LockEntitlementSet` query, once per batch) | A rule change against a writer that changes the set or the rule without holding the rendezvous. |
| Change obligation row | `core.entitlement_set_change_obligations` row of one (change, pool) | `FOR UPDATE` | `core.settle_obligation` | Two passes settling one owed pool, so each effect is written once. |
| Grant lineage head | the `core.grants` row no other grant extends | `FOR UPDATE OF g` | the `GetGrantLineageHeadForUpdate` query, run by `RevokeGrantTx`, `ExtendWaitingGrantTx` and `ManageGrantTx` | Acts on one lineage, so an extension that committed while another act waited is visible to it. |
| Usage row | `core.numeric_entitlement_usage` row of one (pool, resource key) | `FOR UPDATE` in `LockNumericUsage`; the row lock of the statement in `AtomicIncrementUsage`, `AtomicDecrementUsage`, `RaiseNumericUsageTo` and `BoundNumericUsage` | `usage.BoundWorkspace` takes the explicit lock; the atomic statements lock the row for the length of their transaction | The counter against a boot repair that counts rows and then writes the counter. |
| FedWiki site row | `fedwiki.sites` row of one domain | `FOR UPDATE` | the `LockSiteStatusByDomain` query, run by `sweepLocally`, `RecordSiteStatusActivity` and `RecordSwapActivity` | Two paths that move one site out of the active status (a member's request and the downgrade sweep), so only the first to commit releases the site's slot. |
| Desired-state record | `core.desired_state_records` row | `FOR UPDATE` | the `LockDesiredStateRecord` query (after the address lock); `StampDesiredStateRecordsDispatched`, which locks the records in identifier order | The record's content and generation within one recompute; the dispatch stamp against another stamp. |
| Desired-state notice | `core.desired_state_outbox` rows | `FOR UPDATE SKIP LOCKED` | the `ClaimDesiredStateOutbox` query | Two relay passes, so each claims different rows; a pass skips rows another still holds. Triggers write the rows through `core.insert_desired_state_notice` inside the transaction that changed the source, and they take no lock beyond the insert. |
| Resource-key row | `core.resource_keys` row of one key | the update lock of `SetResourceKeyProvider` | `integration.RegisterProviders` at boot, stamping keys in byte order | A stamp that changes a key's provider against an open entitlement insert of that key, which holds the row's `FOR KEY SHARE` through the foreign key. The materializer writes a pool's keys in byte order too, so the two wait in one direction. |
## How advisory keys are built
Postgres takes one 64-bit key for an advisory lock, so every lock above maps
its identity onto a bigint. The code builds those keys four ways.
1. **`hashtextextended` of a fixed name.** The rendezvous. One key for the
whole database.
2. **`hashtextextended` of a prefixed string.** The lifecycle instance
(`lifecycle:` and the instance id) and the desired-state address
(`desired-state:`, the connection, the recipient kind and the recipient
id). The prefix keeps the two families from sharing keys with each other.
3. **`hashtext` of a bare id, widened to bigint.** The subscription, the
invoice, the product sync and the FedWiki workspace. `hashtext` returns 32
bits, so these four families share one 32-bit key space with no prefix
between them; a collision serializes two unrelated objects briefly and
cannot lose a write. The ids differ in shape (Stripe ids against UUIDs),
which makes a cross-family collision unlikely, not impossible.
4. **A literal constant.** The domains registry. Its value is larger than any
32-bit hash, so it cannot collide with family 3.
## The order each path takes locks
Each path below lists its locks in the order it takes them. "Rendezvous"
means the shared lock a transaction of the `entitlements.Materializing` kind
(`BeginMaterializing`) takes as its first statement; the `entitlements.RuleChange`
kind takes the exclusive one. Those two kinds are what set
`SET LOCAL lock_timeout = '5s'`, which bounds every later lock wait in the
transaction. A plain transaction sets no timeout, so its lock waits last until
its context ends. A wait past the bound surfaces as SQLSTATE 55P03. The rule
change, tier removal and default change render it as the refusal for a change
in progress; the other paths show their own failure copy. Materializing
transactions run at READ COMMITTED.
### Rule change and drain
- **Commit** (`RuleChangeBatchCommit`): the exclusive rendezvous
(`BeginRuleChange`), then the set row (`LockEntitlementSet`, once for the
batch), then, for each delta in resource-key order, the set row again (the
same lock) and the rule row inside `core.commit_rule_change`. At or below
the sync cap the same transaction then takes each carrying pool in ascending
`pool_id` order (`ListPoolsCarryingSet`): `settlePool` locks the pool row
through `LockPools`, one pool per call, then the obligation row and the pool
row again inside `core.settle_obligation`, once per act.
- **Drain** (`DrainChange`, called by the commit's own drain above the cap and
by the poller's activity): one short transaction per owed pool, opened
through `BeginMaterializing`. It takes the rendezvous, the pool row
(`settlePool`, through `LockPools`), then the obligation row of each owed act
in the batch's order. A failed pool
rolls back, and `recordObligationFailure` records the attempt in a second
transaction that takes the rendezvous alone.
### Grant acts
All of these run in a transaction from `BeginMaterializing`: the operator
handlers, `ExpireGrantActivity` and the demo seed.
- **Issue or confer a grant** (`ConferGrantTx`, which `ConferGrant`,
provisioning and `ReapplyDefaultsForPool` call): the rendezvous, the grant
insert, then the pool row inside `core.confer`, then materialization.
- **Revoke** (`RevokeGrantTx`): the rendezvous, the organization's default
pool row (`LockPools`), the lineage head row, then
`core.end_conferral` (its pool locks are already held), resumption (the pool
row again), the floor restoration (`core.confer`) and materialization.
- **Expire** (`ExpireGrantTx`): the rendezvous, then the pool row through
`LockPoolOfGrant`, then the grant update. It does not take the lineage head
row. A grant that names no organization has no pool, so the lock,
resumption and materialization are skipped.
- **Extend a waiting grant** (`ExtendWaitingGrantTx`): the rendezvous, the
pool row (`LockPoolOfGrant`), the lineage head row.
- **Manage** (`ManageGrantTx`): the rendezvous, the pool row (`LockPools`), the
lineage head row, then either `ExtendGrantTx` (inside it
`core.confer` and `core.end_conferral`, whose pool lock is already held) or
`ExtendWaitingGrantTx` (the same pool row again).
- **Resume replaced grants** (`ResumeReplacedGrantsTx`): called inside the
transactions above and in the subscription reconcile. It takes the pool row
first and reads the resumable grants only after the lock is held, so an
expiry, revocation, mark change or extension is either wholly visible to it
or waits for it. Each resumed grant calls `core.confer`.
### Subscription reconcile
`ReconcileSubscription` reads the subscription from Stripe before it opens a
transaction. The transaction then takes the rendezvous, then the subscription
advisory lock, then the rest by branch.
- **Active:** `reconcileItems` confers a missing product (`core.confer`),
changes a quantity (`core.update_conferral_bounds`) and ends a surplus
provision (`core.end_conferral`). Each of those functions takes the pool row
of the provision it touches, so the pool row is first taken at the first of
those calls. Materialization follows. `convergeScheduledChanges` then
writes the scheduled-change ledger. A spent Stripe subscription schedule
it finds is released after the commit (`releaseSpentSchedule`), with no
lock held. When the release succeeds and the schedule held a downgrade
row still scheduled, `supersedeReleasedDowngrades` opens a second
transaction, takes the subscription advisory lock and supersedes those
rows; a released schedule that held none opens no second transaction. It
takes no rendezvous and no pool row: it writes only scheduled-change
rows.
- **Ended:** `core.end_conferral` by subscription (the pool row of each
provision it ends), then, only when something ended, resumption (the pool
row), then the floor restoration (`ReapplyDefaultsIfVacant`, which confers
through `core.confer`), then materialization.
- **Suspended:** `core.sync_source_status`, which takes no pool lock, then
materialization with no pool row lock held.
### Tier add, tier removal, default change and reorder
The tier add, the tier removal, the tier delete and the reorder's
renumbering run in transactions from `BeginRuleChange`, which takes the
exclusive rendezvous: each changes a ladder's tiers, and `core.confer` reads a
ladder's ranks and a product's shape in separate statements, so no conferral
may run while one of them commits (#213). The default change and each
organization's enactment run in transactions from `BeginMaterializing`.
- **Tier add** (`entitlements.AddTier`): the exclusive rendezvous, the tier
insert, then for each live provision of the product in creation order
`core.align_conferral_shape` (the pool row of that provision) and
materialization. The pools are locked in provision order, not in `pool_id`
order.
- **Tier removal with holders** (`entitlements.RemoveTier`): the
exclusive rendezvous, then the pool row of every holder through
`LockPools`, which sorts and deduplicates them, so they lock in ascending
`pool_id` order, then the holders are read again under the locks, the tier
is deleted and the ranks renumbered. Then, for each pool in the same order,
the incumbents end (`core.end_conferral`), the remaining positions align
(`core.align_conferral_shape`), the floor is restored when the disposition is
migrate, and the pool materializes.
- **Tier delete without holders** (`entitlements.DeleteTier`): the exclusive
rendezvous only, with no pool lock, around the tier delete and the
renumbering.
- **Default change** (`entitlements.ChangeOrgTypeDefault` and `EnactDefault`):
the default is saved outside any lock beyond the row update. Then each
organization is its own transaction: the rendezvous, the organization's
default pool row (`LockPools`), then the bucket's work
(`ReapplyDefaultsIfVacant`, or `ConferGrantTx` for a grandfathered
incumbent, or `core.end_conferral`, `ReapplyDefaultsIfVacant` and
materialization for a migration). A failure on one organization leaves the
others alone.
- **Reorder** (`entitlements.ReorderTiers`): the exclusive rendezvous around
the renumbering, committed with no pool lock. When the rank-0 tier changed,
each affected organization then runs `EnactDefault` under the shared
rendezvous, as a default change does.
### Invoice reconcile
`invoiceReconcile.converge` opens a plain transaction, with no rendezvous, and
takes the invoice advisory lock. It writes invoice, line and payment rows
under that lock and touches no pool.
### Product sync
`catalogsync.Sync` opens a plain transaction, takes the product advisory
lock, re-reads the price and product mappings, writes the pending mappings
and enqueues the outbox rows. It takes no pool lock and no rendezvous. A sync
that commits nothing returns with its transaction open, and
`SyncProductToStripe` rolls it back after it has rendered its answer, so the
lock is held until then.
### FedWiki at the handler
`withWorkspaceTx` takes the workspace advisory lock, then runs the handler's
statements. A swap (`swapActiveSite`) and a rotation with a cooldown
(`changeStatus`) then record a lifecycle request, which takes the lifecycle
instance lock of the activated site. A swap records a second request for the
parked site, which takes that instance's lock after the first. The swap policy
row is upserted last. The handler takes no usage-row lock and no site-row
lock. A status change without a cooldown skips the workspace lock and records
its request in a transaction of its own, taking only the instance lock.
### When recording an outcome
The workflow activities that finish a lifecycle request run
`finishAllWith`: the lifecycle instance locks, in the order the handler
recorded the requests (the activated site first, then the parked site), then
the activity's own writes.
- `RecordSiteStatusActivity`: the site row (`LockSiteStatusByDomain`), then the
usage row through `AtomicDecrementUsage` when the site leaves the active
status.
- `RecordSwapActivity`: the site row of the parked site, then the site row of
the activated site, then the usage row (`AtomicIncrementUsage` or
`AtomicDecrementUsage`, by what each site's status calls for).
- `RecordSiteDeletedActivity`: the usage row through `AtomicDecrementUsage`
when the deleted row held a slot.
So a transaction that holds a site row and the usage row takes the site row
first.
### In the downgrade sweep and at boot
- **Park** (`parkSite`): the farm call first, with no lock held, then one
transaction (`sweepLocally`) that takes the site row and, in the same
transaction, decrements the usage row.
- **Reactivate** (`reactivateSite`): the usage increment as a statement of its
own, before the farm call, then a second transaction that takes the site row.
The two never overlap, so they form no cycle.
- **Boot repair** (`usage.BoundWorkspace`): one transaction that takes the
usage row with `LockNumericUsage`, counts the site slots without locking any
site row, then writes the bounded counter.
- **Provider registration** (`RegisterProviders`): the resource-key rows in
byte order.
- **Domains reconciliation at boot** (`reconcile_domains.go` in the FedWiki
package): one `Registry.WithLock` transaction per orphaned placement or
adopted site.
### Lifecycle intake
`RecordLifecycleRequest` takes the instance lock first, then reads the latest
generation, then inserts. `RecordFirstLifecycleRequest` takes the same lock
once and reads the latest generation once: it refuses a later generation of an
instance that already holds a request, then decides as
`RecordLifecycleRequest` does. Called with a `*sql.DB`, each opens its own
transaction; called with a transaction, each uses the caller's, which is how
the handler records under the workspace lock.
`DeleteUnstartedLifecycleRequest`, which removes a request whose workflow never
started, takes no instance lock, so a repeat that races it may be answered from
either side of the delete.
### Desired-state relay
- **Claim** (`ProcessDesiredStateOutbox`): `ClaimDesiredStateOutbox` is one
statement that locks the pending rows `SKIP LOCKED` and leases them. The
relay never takes the rendezvous and never locks a source row.
- **One recompute** (`recomputeAddress`, and the same sequence in `readBackItem`
and `writePushAnswer`): one READ COMMITTED transaction that takes the address
advisory lock, then the record row (`LockDesiredStateRecord`), then computes
the content in a new statement, then updates or inserts the record. Every
transaction that writes a record's content or generation, or a projection,
takes the address lock first.
- **Dispatch stamp** (`StampDesiredStateRecordsDispatched`): one statement that
locks the records in identifier order before it writes. It takes no address
lock.
### Registry
`domains.Registry.WithLock` opens a plain transaction and takes the registry
lock before anything else. Every composite operation, including the FedWiki
create, release and sync projections, runs on the `*Tx` it passes, so the lock
is taken once. The comment on `registryAllocationLock` states the rule for a
transaction that would touch both the registry and a usage row: registry lock
first. No transaction does both today: the usage increment and decrement are
workflow activities that run their own statements, apart from the transaction
that allocates or releases the name.
## Rules every path keeps
1. A transaction that materializes a pool takes the shared rendezvous as its
first statement, through `BeginMaterializing`. A rule change or an
operator's tier change (the tier add, the tier delete, the tier removal,
the reorder's renumbering) takes the exclusive one through
`BeginRuleChange`, and may materialize under it; the demonstration seed
adds its ladder's tiers on the plain handle, outside the rendezvous.
`core.assert_rendezvous` raises
`materialization_rendezvous_missing` when a function that materializes or
settles runs without it.
2. The pool row is locked before positions or entitlements change, except in
the suspended branch of the subscription reconcile (see "Where the paths
differ"). The five conferral and settlement functions lock the row
themselves, so a caller that goes straight to one of them still holds it.
3. A transaction that locks several pools takes them in ascending `pool_id`
order, through `LockPools`. The rule change and the tier removal do; tier add does not (see "Where
the paths differ"). The deferred drain locks one pool per transaction.
4. The grant lineage head row is locked after the pool row, never before.
5. A transaction that holds a site row and the usage row takes the site row
first.
6. Lifecycle instance locks of one transaction are taken in the order the
requests were recorded.
7. A desired-state write takes the address lock, then the record row, then
computes content in a new statement.
8. Every advisory lock belongs to its transaction, so a commit or a rollback
releases it, and an error never leaves one behind.
## Where the paths differ
- **The plan-ending paths take the pool lock at different points** (#180).
Revocation and expiry lock the pool before they write. The subscription
reconcile's ended branch ends positions first, so the pool row is taken
inside `core.end_conferral`, and takes it again for resumption. Each path
then runs resumption, the floor restoration and materialization in the same
order.
- **Advisory keys are built four ways** (#186). The keys follow the four
conventions above.
- **The suspended branch of the reconcile materializes without a pool row
lock.** It holds the shared rendezvous and the subscription lock only.
- **Tier add locks pools in provision order**, where tier removal sorts them.
- **The FedWiki usage counter has no explicit lock in the request paths.** The
atomic statements lock the row for their own duration. #165 adds a usage-row
lock under the workspace lock, and its position in the order above belongs on
this page when it lands.
Reference in New Issue View Git Blame Copy Permalink