How to wrap multiple writes in a transaction, so a failed signup or order leaves nothing half-finished

On the day a payment provider times out mid-signup, the app either rolls everything back or keeps a user row with no workspace. Knowing how to wrap multiple writes in a transaction comes down to this: related writes between one BEGIN and one COMMIT on one connection, network calls outside, and the rollback proved with an injected failure.

What is a database transaction, and what atomic means in practice

A database transaction is a group of statements the database treats as one unit: either all of them take effect at COMMIT or none do. PostgreSQL’s tutorial calls it bundling multiple steps into a single, all-or-nothing operation. Atomicity is the A in ACID, and it is the property that prevents half-finished signups and orders.

The rest of that definition in PostgreSQL’s tutorial on transactions is the part that matters for an app: “The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.” Atomic multi-step writes are one of the checks in a wider data consistency checklist for SaaS.

The four ACID letters, in PostgreSQL’s glossary terms. Atomicity: all of a transaction’s operations complete as a single unit, or none do. Consistency: the data stays in compliance with integrity constraints, such as a foreign key or a NOT NULL column. Isolation: a transaction’s effects are not visible to other transactions before it commits, and PostgreSQL’s default isolation level is Read Committed. Durability: once committed, the changes survive a crash.

The statement you did not wrap is still a transaction. In the tutorial’s words, “each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it”. That is exactly why two separate inserts are not atomic: each one commits the moment it succeeds, so the second failing cannot touch the first. Here is the same two-write flow both ways, where $1 stands for the new user’s id as a driver would pass it and owner_id is a NOT NULL column:

-- Both writes land, or neither does
begin;
insert into profiles (user_id, display_name) values ($1, 'New user');
insert into workspaces (owner_id, name) values ($1, 'My workspace');
commit;

-- Same flow, and the second insert fails
begin;
insert into profiles (user_id, display_name) values ($1, 'New user');
insert into workspaces (owner_id, name) values (null, 'My workspace');
rollback; -- the profile row is discarded too

After the error in the second block, Postgres puts the transaction in an aborted state, and nothing more runs in it until you roll back. The transaction lives in the database session, not in your code, so every write in it has to travel over the same connection; the node-postgres docs put it plainly: “You must use the same client instance for all statements within a transaction.” Inside a transaction, a savepoint lets you discard the work after a marked point while keeping what came before it. Database courses add a vocabulary of transaction states and transaction types; for an app, what counts is whether the writes are open, committed or rolled back.

What goes wrong without it

A signup half finished after an error is the version founders notice first: the auth user exists, the profile or workspace does not, and the person lands in an app with nothing in it, or one that crashes. The same shape turns up wherever one business action writes more than once. These five flows are where I would look first in any SaaS, my working rule rather than a complete list:

In my reading, AI-generated code produces this in a simple way: each write is a separate awaited client call with its own try and catch. It reads fine in review. It is not atomic, because every call is its own commit, and the catch around the third call cannot undo the first two.

One app I audited in June and July 2026, an ops SaaS, created each new workspace with about 13 separate writes and no transaction, so a failure mid-sequence left a tenant nobody could log into. Its batch writes also dropped unprocessed items without an error, so onboarding could land in a half-filled workspace. The lesson I take from it: separate writes are separate commits, and only one transaction, or one database function, turns them into one.

Across the third-party apps I audited in June and July 2026, the Data Integrity & Safety pillar averages 51.6 out of 100, scored on 20 of the 21 apps. They are a selected set, not a random sample, so the average describes them and is no rate for AI-built apps in general.

Wrapping the writes stops new damage; it does nothing for rows earlier failures already left behind, like an auth user with no profile. Finding and removing those is the work of a database cleanup checklist. If the damage is an overwrite rather than a half-write, the path is different: what you can still get back after overwriting data.

How to wrap multiple writes in a transaction on Postgres, Supabase, Prisma and Drizzle

Wrapping multiple writes in a transaction takes three rules: every related write runs between one BEGIN and one COMMIT on the same connection, any error triggers ROLLBACK so none of them persist, and nothing slow or external happens inside. In an ORM, that is one callback passed to its transaction function, with every query made through the transaction client.

The table is the overview; the code in the sections under it follows each project’s own documentation.

Supabase: why two client calls cannot share a transaction, and the rpc route

Supabase’s JavaScript client does not group multiple queries into one transaction, so two inserts sent from the client can never share one. The supported route is a Postgres function called through rpc, which reaches the database as one statement and so runs as one transaction, or a server-side connection that uses a driver or an ORM.

Supabase says so in its JavaScript reference, in the entry for rollback(), a dry-run modifier that PostgREST applies to one request: “The JS caller has no handle on the transaction: supabase-js does not group multiple queries into one transaction. For multi-statement transactional logic, use a database function (supabase.rpc(…)).” So the multi-step operation moves into a Postgres function, and the app calls it with supabase.rpc. Here auth.uid() is Supabase’s helper that returns the id of the user making the request:

create or replace function public.create_workspace(workspace_name text)
returns uuid
language plpgsql
security invoker
as $$
declare
  new_id uuid;
begin
  insert into public.workspaces (name, owner_id)
  values (workspace_name, auth.uid())
  returning id into new_id;
  insert into public.memberships (workspace_id, user_id, role)
  values (new_id, auth.uid(), 'owner');
  return new_id;
end;
$$;

The app then makes one call, supabase.rpc('create_workspace', { workspace_name: 'My workspace' }), which returns the new workspace id as data or the database error as error.

Why this is atomic, as I read the PostgreSQL docs: the call arrives as one statement, every statement runs inside a transaction, and an error raised anywhere in the function body means none of its writes take effect. The trap is an exception block that catches the error and carries on: PL/pgSQL rolls back only the changes inside that block, and writes made before it are not rolled back. As I read that, the function then returns normally and the call commits a half-finished result. If you catch an error to log it, raise it again.

On security, Supabase’s database functions guide says: “It is best practice to use security invoker (which is also the default). If you ever use security definer, you must set the search_path.” The same guide says a security invoker function is executed as the user calling it, not as its creator, so, as I read it, its inserts meet the same row-level security policies as the client’s would.

The other route is a server: an Edge Function or a backend that connects to Postgres with a driver or an ORM, which is the next section. The browser-only first rung needs no terminal. In the Supabase dashboard, go to the SQL editor, click New Query, paste the function, and click Run (or cmd+enter). Supabase’s guide notes a function can be run with a select query as well as from the client libraries, but test this one from your app while signed in. Dashboard queries run as the postgres role, and auth.uid() returns the id of the user making the request, so in the SQL editor, as I read those two lines, there is no user for it to return.

Prisma, Drizzle and a plain driver: one callback, one connection

Prisma’s transactions docs now default to Prisma ORM 8, so check your version before copying code. The docs say “Prisma ORM 8 has no $transaction, so both of the Prisma ORM 7 forms become db.transaction(async (tx) => …)”, and inside the callback you “query through tx instead of db”. In Prisma ORM 7 the two forms were the sequential array, prisma.$transaction([...]), and the interactive callback, whose timeout defaults to 5000 ms and maxWait to 2000 ms. The version 8 page states its own conditions: it runs on PostgreSQL, SQLite and MongoDB “but there is no MySQL support”, the MongoDB client has no db.transaction(...) yet, and there is no transaction timeout, so a transaction stays open for as long as your callback runs. Prisma’s warning about side effects is the one to remember: “Database writes roll back; emails don’t. If a later statement throws, the record disappears but the email was already sent.”

Drizzle’s transactions docs use the same callback shape. tx.rollback() throws an exception that rolls the transaction back, and a nested tx.transaction(...) becomes a savepoint. The same rule applies as in Prisma, as I read Drizzle’s example: every query inside the callback goes through tx. Here is one two-write flow in each, Prisma ORM 8 first:

// Prisma ORM 8
const workspace = await db.transaction(async (tx) => {
  const ws = await tx.orm.public.Workspace.create({ name, ownerId: userId })
  await tx.orm.public.Membership.create({ workspaceId: ws.id, userId, role: 'owner' })
  return ws
})

// Drizzle ORM
const workspace = await db.transaction(async (tx) => {
  const [ws] = await tx.insert(workspaces).values({ name, ownerId: userId }).returning()
  await tx.insert(memberships).values({ workspaceId: ws.id, userId, role: 'owner' })
  return ws
})

One driver condition matters on Neon. Drizzle’s Neon page says querying over HTTP “is faster for single, non-interactive transactions”, and “If you need session or interactive transaction support” the WebSocket-based neon-serverless driver is the one to use. An app on drizzle-orm/neon-http checks its driver before relying on a db.transaction callback.

Without an ORM, the pattern from the node-postgres docs is: check out one client with pool.connect(), send BEGIN on it, run the writes on that same client, send COMMIT, send ROLLBACK in the catch, and call client.release() in the finally. The docs are blunt about the usual mistake: “Do not use transactions with the pool.query method.”

Cloud Firestore has two atomic tools, runTransaction and batched writes, and per Firestore transactions and batched writes, “Transactions never partially apply writes.” In a transaction, “Read operations must be executed before write operations”, and the function might run more than once if a concurrent edit affects a document it reads. The documented limits: a large batch “might exceed the limit on transaction size”, and security rules allow 20 document access calls for the whole atomic operation.

SQL Server and Oracle spell the statements differently; the rule of one unit on one connection is the same.

Rollback database changes when a step fails: what rolls back, and what never can

Some steps cannot be rolled back because they happen outside the database: a charge, an email, a file upload, a call to another API. The pattern is to commit a pending record first, make the external call with an idempotency key, then record the result, with a job that repairs anything left pending.

ROLLBACK cancels the updates made since BEGIN, and that is all it reaches. A rollback of database changes when a step fails undoes rows, never effects: a card charge, a sent email, an object in storage, a model call or a row in a second database all stay done, which is what Prisma’s email sentence above describes. Here is how I place each step, my working rule:

The pattern in order, as my working rule: write a pending record and commit; make the external call with an idempotency key so a retry cannot double it; record the outcome in a second short transaction; and run a scheduled job that finds records stuck in pending past a threshold and either completes or cancels them. Stripe’s idempotent requests are the documented example: send an Idempotency-Key header, and Stripe saves the status code and body of the first request for that key and returns the same result for later requests with it, “including 500 errors”. Designing the receiving side is its own job: how to make a webhook handler idempotent.

Signup follows the same pattern. The auth user is created by the provider in its own request, outside any transaction your code opens, so the profile and workspace go in one transaction keyed to the new id, and a repair job looks for auth users with no profile. Supabase’s documented trigger on new users is different: as I read its warning, “If the trigger fails, it could block signups”, the trigger’s writes succeed or fail together with the auth user. Never hold a transaction open across a network call.

The webhook version of this failure, a handler that threw after acknowledging the event, has its own fix: a Stripe webhook that does not update the database. Rollback after a failed schema change is a different subject, covered in how transaction rollback differs from migration rollback. And once a transaction has committed, ROLLBACK cannot reach it; getting committed rows back is how to undo a DELETE in Postgres.

Keep it short: pools, timeouts and retries

A transaction holds its locks until it ends: row-level locks “are released at transaction end or during savepoint rollback”, and a transaction that wants a conflicting lock waits for it indefinitely unless a deadlock is detected or a timeout is set. PostgreSQL’s manual calls it “a bad idea for applications to hold transactions open for long periods of time (e.g., while waiting for user input)”. So my working rule is the fewest statements that must succeed together, no user think-time, and no network calls between BEGIN and COMMIT.

A transaction-mode pooler assigns your app a server connection only for the length of one transaction, so a transaction still works as long as all of its statements run through it; the rest of what changes is in connection pooling for Postgres.

Two timeouts are worth setting, both listed in PostgreSQL’s client connection settings. idle_in_transaction_session_timeout terminates a session that sits idle inside an open transaction for longer than the limit; lock_timeout aborts any statement that waits longer than the limit for a lock. Both take milliseconds when you give no unit, and both default to zero, which disables them. The same page advises against setting lock_timeout in postgresql.conf because it would affect all sessions, so set it for the role or the session that runs your app’s writes.

Retries for serialization failures depend on the isolation level. The default is Read Committed, and at the Repeatable Read and Serializable levels the manual says applications “must be prepared to retry transactions due to serialization failures”, and on that error “abort the current transaction and retry the whole transaction from the beginning”. Errors that mention a read-only transaction are a different problem with their own causes: cannot execute INSERT in a read-only transaction.

Postgres show locks: what is holding the row, and how to see it

Postgres locks are listed in the pg_locks view, which PostgreSQL’s docs name for examining outstanding locks; joined to pg_stat_activity, it shows each session’s query. The pg_blocking_pids function returns the sessions blocking a given process, which answers the usual question of what a stuck query is waiting on.

In the pg_locks view, granted is true if a lock is held and false if it is awaited, and the pid column joins to pg_stat_activity for the session’s details. Row-level locks normally do not appear there as rows; a session waiting on one usually shows up waiting on the holder’s transaction ID. The same docs say “It is better to use the pg_blocking_pids() function” to identify which process a waiting process is blocked behind, rather than joining pg_locks against itself. The function returns the process IDs of the sessions blocking a given process, and the manual warns that “Frequent calls to this function could have some impact on database performance”, so use it for a diagnosis, not a dashboard polled every second.

This query lists every blocked session next to the session blocking it, with both queries and how long each has been going. It is written from the PostgreSQL docs; the fuller variants on the PostgreSQL wiki’s lock monitoring page self-join pg_locks instead.

select
  blocked.pid                  as blocked_pid,
  now() - blocked.query_start  as waiting_for,
  blocked.query                as blocked_query,
  blocking.pid                 as blocking_pid,
  blocking.state               as blocking_state,
  now() - blocking.xact_start  as blocking_transaction_age,
  blocking.query               as blocking_last_query
from pg_stat_activity as blocked
cross join lateral unnest(pg_blocking_pids(blocked.pid)) as b(pid)
join pg_stat_activity as blocking on blocking.pid = b.pid
where blocked.datname = current_database();

The state behind most app lock trouble, as I read it, is idle in transaction: code opened a transaction, then waited on something else, often the external call the previous section moved out. For an idle session, query shows the last statement it ran, which usually points at the code path. Supabase’s connection guide describes the state as one that “holds locks and blocks table cleanup for as long as it stays open, and almost always means the application forgot to commit or roll back.”

How rows lock, in brief: FOR UPDATE “prevents them from being locked, modified or deleted by other transactions until the current transaction ends”, and every UPDATE or DELETE takes a row-level lock of its own. Readers are not blocked by those locks; writers to the same row are. The related searches have short answers. AccessShareLock is the lock a plain SELECT takes on the tables it reads, so it is rarely the culprit. LOCK TABLE is rarely what application code should reach for, in my reading, because the row locks your writes already take are enough. More detail on modes is in PostgreSQL’s explicit locking chapter.

Clearing a stuck session is for a database you own. pg_cancel_backend “Cancels the current query of the session”, and pg_terminate_backend “Terminates the session”; each is allowed to a role that is a member of the target session’s role or has the privileges of pg_signal_backend, and only superusers can stop superuser sessions. Know what termination does first: for an idle in transaction session, canceling does nothing, and in Supabase’s words “Terminating the session is the only way to force the transaction closed and release its locks.” The writes that transaction had not committed never take effect, which is the atomic guarantee working as intended. Then run select pg_terminate_backend(pid); with the blocking pid from the query above in place of pid.

On a managed database, the provider’s SQL editor runs the same query. On Supabase: SQL editor, New Query, paste, Run. Supabase’s connection guide also says the dashboard’s Database Connections page shows session states, blocking chains and a way to terminate a stuck session without writing SQL.

How to verify it

Atomic writes are verified by injecting a failure between the first and second write in a test environment and checking that no rows from the first write remain. For flows with an external step, the check is that the documented recovery job returns the record to a consistent state.

Run these in a test or staging database, never in production:

1. 01 List every flow that writes more than once, starting from the five flows in the table under What goes wrong without it, and note the order of its writes.
2. 02 For each flow, add a test-only failure between the first and second write inside the transaction: a thrown error behind a flag in application code, or a raise exception line in a test copy of the Postgres function. For signup, put it between the profile and the workspace, because the auth user is created outside the transaction and step 5 covers it.
3. 03 Run the flow with the flag on and confirm the app reports the error instead of a success.
4. 04 Query for the rows the first write inside the transaction would have made, as a role that sees every row, such as the Supabase SQL editor, not a client under row-level security, which can return zero rows for rows that exist. Pass: zero rows. Keep the query and its empty result.
5. 05 For a flow with an external step, fail it after the external call and confirm the recovery job brings the record to a consistent state within the window it promises. Keep the record as it was before and after.
6. 06 Remove the flag, or keep the test as a permanent automated check that runs on every change.

In application code, step 2 looks like this; the flag name is yours to choose:

await db.transaction(async (tx) => {
  const ws = await tx.orm.public.Workspace.create({ name, ownerId: userId })
  if (process.env.FAIL_AFTER_FIRST_WRITE === 'on') {
    throw new Error('injected failure between writes')
  }
  await tx.orm.public.Membership.create({ workspaceId: ws.id, userId, role: 'owner' })
})

In a Postgres function, the same failure is a raise exception 'injected failure'; line between the two inserts, which normally aborts the current transaction. A second, cheaper check for ORM code: search every transaction callback for a query made on the global client (db. or prisma.) instead of tx., since Prisma’s docs say queries on db run outside the open transaction and commit immediately. Per flow, keep three things: the test name, the date it ran, and the result.

In the Production Hardening Sprint, deliverable 4.4 is verified this way: inject a failure between writes and verify atomic rollback or the documented recovery behavior.

Where the sprint does this

Deliverable 4.4 wraps related database changes in transactions and protects workflows spanning several writes, and its check is the injected failure in the section above. Deliverable 13.1, the production readiness report, delivers the result for every scope item, the work completed, and its verification evidence, and it is verified by accounting for all 123 IDs, keeping failures visible until resolved and explaining genuine non-applicable items. Building new product features or modules, completing unfinished core features or business workflows, and rebuilding core functionality that does not yet perform its intended job are outside the sprint. The app’s current framework and hosting setup are the starting point, and we refactor or replace components where the production work requires it. The exact wording is deliverable 4.4 in the published scope.

Common questions about transactions, rollback and locks

What is a concurrent transaction?

A concurrent transaction is one that runs at the same time as another. PostgreSQL hides an open transaction’s changes from the others until it commits, and at the default Read Committed level a query sees only data committed before it began. That is why a signup still in progress is invisible to every other request until its COMMIT, and why a half-finished one that rolls back was never seen at all.

What happens if you issue a ROLLBACK statement?

Every update the transaction made since BEGIN is discarded, and the row-level locks it held are released. Issued outside a transaction block, PostgreSQL’s reference says ROLLBACK “emits a warning and otherwise has no effect”.

What is pg_advisory_lock?

pg_advisory_lock is a PostgreSQL function that takes an exclusive session-level advisory lock: a lock with a meaning your application defines and the database does not enforce, held until you release it or the session ends, even if the transaction that took it rolls back. The transaction-level form, pg_advisory_xact_lock, is released automatically when the transaction ends. One use, my example, is making a scheduled job run only once at a time. Behind a transaction-mode pooler, use the transaction-level form: PgBouncer lists session-level advisory locks as “Never” under transaction pooling.

How do rows lock in PostgreSQL?

Rows lock when a transaction updates or deletes them or selects them FOR UPDATE, and other transactions that try to change or lock the same rows wait until that transaction ends. Plain reads carry on, because row-level locks “do not affect data querying”.