Prevent duplicate donations by making PostgreSQL arbitrate request identity, not by checking first in FastAPI. Store a stable idempotency key under a unique constraint, write the donation and its ledger entries in one short transaction, and give each request its own SQLAlchemy session.
Use PostgreSQL’s default Read Committed isolation for straightforward constraint-backed inserts. For rules spanning several rows—such as campaign caps or conditional balances—choose a narrowly scoped row lock or Serializable isolation and retry the entire transaction when PostgreSQL reports a serialization failure.
The concurrency design in one view
A reliable donation write has four boundaries:
- Request identity: the client supplies a stable key for one logical donation attempt.
- Database arbitration: PostgreSQL enforces
UNIQUEon that key and applies an explicit conflict policy. - Atomic persistence: the donation row and its append-only ledger entries commit together.
- Request isolation: every FastAPI request or unit of work uses its own SQLAlchemy session.
This is an operational donation log unless your organization has separately defined a formal double-entry accounting model. Currency treatment, restricted gifts, refunds, chargebacks, donor privacy, retention, receipts, and audit requirements need their own policy and schema decisions.
Model request identity and ledger rows
Use a stable key for one logical donation
Require the caller or payment workflow to generate an idempotency key that remains unchanged when the same operation is retried. Do not generate a fresh key after a timeout when the original request may already have committed.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →#1 Best Overall
CREATE TABLE donations (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
request_key text NOT NULL UNIQUE,
amount_cents bigint NOT NULL CHECK (amount_cents > 0),
currency text NOT NULL,
donor_reference text,
status text NOT NULL,
provider_object_id text UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE ledger_entries (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
donation_id bigint NOT NULL REFERENCES donations(id),
account_code text NOT NULL,
amount_cents bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);The unique request key is the concurrency boundary. The foreign key ties every ledger entry to a donation. If you maintain a derived balance or campaign summary, update it in the same transaction or recompute it from the ledger rather than trusting a separately timed application update.
Decide what a duplicate means
When a request reuses a key, compare the material parameters with the recorded request. Return the existing result when they match. Reject the reuse—commonly with a conflict response—when the amount, currency, campaign, or other identity-defining value differs. This response policy belongs to the application; PostgreSQL supplies the atomic uniqueness decision.
Why “SELECT first, then INSERT” fails
Two requests can both execute a preliminary SELECT and see no row before either has inserted. Each then attempts an INSERT. Application timing has not protected the invariant; only a database constraint can do that.
Use a constraint-backed insert with an explicit conflict action instead:
Recommended Free Tools
Rank #2
INSERT INTO donations (request_key, amount_cents, currency, status)
VALUES (:request_key, :amount_cents, :currency, 'recorded')
ON CONFLICT (request_key) DO NOTHING
RETURNING id, request_key, amount_cents, currency, status;If the statement returns a row, this transaction created the donation. If it returns no row, select the existing donation by request_key, compare its parameters, and return its already-recorded outcome when equivalent. PostgreSQL’s ON CONFLICT processing provides an atomic insert-or-update outcome under concurrency; use the conflict target that matches the unique constraint and verify syntax against the PostgreSQL release you deploy.
For an upsert where replacement is genuinely intended, PostgreSQL also supports ON CONFLICT ... DO UPDATE. Do not use a no-op update merely to hide a conflicting request if updates could trigger audit columns, notifications, or other side effects.
Understand PostgreSQL’s visibility rules
PostgreSQL uses multiversion concurrency control (MVCC). Under Read Committed—the default isolation level—each statement sees rows committed before that statement began. Two successive SELECT statements in one transaction can therefore observe different committed states.
That behavior is why a constraint-backed insert is safer than a multi-statement existence check. It is also why a broader invariant must be designed around a single atomic statement, an explicit lock, or Serializable isolation instead of assuming that earlier reads remain valid.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteScope SQLAlchemy sessions to requests
One engine and pool per application process
Create the SQLAlchemy engine and connection pool once for each FastAPI application process. A session is mutable, stateful transaction machinery; never share one Session across concurrent threads or one AsyncSession across concurrent asyncio tasks. The SQLAlchemy concurrency rule is “Session per thread, AsyncSession per task.”
Use a yield dependency for request cleanup
FastAPI’s documented relational-database pattern uses a dependency with yield to provide a session and close it after the request. The tutorial demonstrates SQLModel (built on SQLAlchemy) with SQLite; its dependency lifetime is useful, but PostgreSQL connection settings, schema types, and migrations are deployment-specific. Production systems should run migrations before application startup rather than create tables as a startup side effect.
from collections.abc import Generator
from fastapi import Depends, FastAPI
from sqlalchemy.orm import Session
app = FastAPI()
def get_session() -> Generator[Session, None, None]:
with SessionLocal() as session:
try:
yield session
except Exception:
session.rollback()
raise
@app.post("/donations")
def create_donation(payload: DonationRequest,
session: Session = Depends(get_session)):
with session.begin():
# Execute the INSERT ... ON CONFLICT statement here.
# Insert related ledger rows before leaving the block.
passKeep the transaction short. Perform validation that does not require locks before entering it, write all related donation and ledger rows inside it, commit only after every write succeeds, roll back on failure, and close the session. With SQLAlchemy’s async extension, inject a separate AsyncSession into each concurrently running task.
Write the donation and ledger atomically
A typical unit of work is:
- Validate the request and canonicalize values such as currency and the idempotency key.
- Begin a database transaction.
- Run the unique-key insert with its explicit conflict action.
- If this request won the insert, append all required ledger entries and any same-transaction summary update.
- If the key already exists, load the recorded row, verify parameter equivalence, and avoid creating a second ledger entry.
- Commit once; return the committed representation.
Never acknowledge a donation before the database commit. If the process dies before commit, the caller can retry the same key. If it dies after commit but before the response reaches the caller, the same key retrieves the existing result instead of creating another donation.
Choose coordination for broader invariants
Uniqueness is a narrow invariant. Rules involving a set of rows need stronger coordination.
| Approach | Use when | Strength | Costs and failure modes |
|---|---|---|---|
Unique constraint plus ON CONFLICT |
One key or one row must be unique | Database-arbitrated, simple outcome | Does not by itself enforce multi-row limits |
| Single atomic update or constraint | The rule can be expressed in one statement | Short transaction and low coordination scope | Requires careful statement design |
| Explicit blocking lock | Contention centers on a known row or resource, such as one campaign | Clear serialization point | Waiting, deadlock risk, and lock-scope management |
| Serializable transaction | Correct ordering depends on a broader read/write set | Database rejects unsafe interleavings | Serialization failures require complete transaction retries |
Campaign caps and conditional balances
If a campaign has one row containing the remaining allocation, lock that row with SELECT ... FOR UPDATE, check the available amount, decrement it, and write the donation and ledger rows in the same transaction. Keep the lock scope narrow and acquire multiple locks in a consistent order to reduce deadlock risk.
If the rule depends on a broad predicate—such as whether a set of donations collectively crosses a limit—Serializable isolation may be clearer. PostgreSQL can abort a transaction with a serialization failure when concurrent work cannot be safely ordered. A higher isolation level is not a substitute for defining the invariant and its writes.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Retry Serializable transactions correctly
Retry the entire transaction from its beginning, not just the statement that failed. Every retry must reread its inputs, redo all database writes, and use side effects that are safe to repeat.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsfor attempt in range(MAX_ATTEMPTS):
try:
with SessionLocal() as session:
with session.begin():
session.execute(text(
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
))
# Re-read required rows, apply the invariant,
# write donation and ledger rows, then commit.
break
except Exception as exc:
if not is_serialization_failure(exc) or attempt + 1 == MAX_ATTEMPTS:
raise
sleep_with_bounded_backoff(attempt)Bound the number of attempts and use backoff. Do not send an email, issue a receipt, enqueue an irreversible job, or charge a provider inside the retry loop unless that side effect is itself idempotent or deferred until after the database outcome is known.
Coordinate with a payment provider
Provider idempotency and local ledger uniqueness protect different boundaries. For a provider that supports idempotency keys, reuse the same key when retrying the same supported create or update request. Providers retain keys and enforce parameter matching according to their own endpoint-specific rules, which can change.
Store the provider’s object identifier locally under a unique constraint. If a network failure leaves the provider result ambiguous, reconcile the original operation using the same key or provider lookup before creating any new logical donation. Never assume a provider key replaces the local request-key constraint or the transaction that links a donation to its ledger entries.
Quick Recap
Test the race, not just the happy path
- Send two concurrent requests with the same request key and identical parameters; assert that exactly one donation and one set of ledger entries exist and both callers receive the same logical result.
- Send concurrent requests with the same key but different amounts; assert that one wins and the other is rejected as a parameter mismatch.
- Force a client timeout after commit and retry with the original key; verify that no second donation appears.
- Run concurrent campaign-cap requests and verify that the invariant holds under the selected atomic update, lock, or Serializable design.
- Inject a serialization failure and confirm that the complete unit of work retries, while non-repeatable external side effects do not run twice.
- Kill or roll back a transaction after the donation insert and verify that orphan ledger entries cannot remain.
Deployment checklist
- Apply schema changes through migrations before serving traffic.
- Create the unique constraint on the stable request key and a unique constraint for any provider object identifier.
- Use one engine and pool per process; one session per request or unit of work.
- Keep donation and ledger writes in one transaction and commit only after all succeed.
- Use an explicit
ON CONFLICTpolicy instead of a read-before-write race. - Define the duplicate-response policy for equivalent and mismatched payloads.
- For multi-row invariants, document the lock order or Serializable retry policy.
- Monitor transaction aborts, lock waits, deadlocks, duplicate-key conflicts, and reconciliation cases.
- Document accounting, privacy, retention, refund, chargeback, and restricted-fund requirements separately from concurrency mechanics.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
