Basics

What is a database transaction — and why money doesn't vanish halfway

Illustration: two operations held by a single latch

Look at this code. It reads fine, and it loses money.

await db.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1')
await db.query('UPDATE accounts SET balance = balance + 100 WHERE id = 2')

Take from the first account, give to the second. Perfectly fair — right up until the server dies between those two lines, the network blinks, or an error is thrown. Then the hundred leaves the first account and never reaches the second. It exists nowhere.

The database isn't broken. It did exactly what you asked: two independent actions. The problem is that in your head it was one.

What a transaction actually is

A transaction is a group of queries the database runs under one rule: all of them, or none of them.

Not "as many as it managed". If any step inside fails, the database rolls back the rest too — as if you never showed up.

Everyday analogy: a transfer between accounts in your banking app. You press one button, but inside there are two writes. You don't want a state called "already debited, not yet credited" to exist, and neither does the bank — so both writes go in one transaction.

The three words it's made of

In SQL it looks literally like this:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
  • BEGIN — "I'm starting; treat what follows as one piece."
  • COMMIT — "it all worked, make it real."
  • ROLLBACK — "something's wrong, forget everything since BEGIN."

Between BEGIN and COMMIT your changes exist only halfway: your connection sees them, everyone else doesn't. If COMMIT never happens, the database rolls back by itself once the connection drops. That's exactly why "the server died mid-way" stops being a scary scenario.

Where people actually hit this

Not in banks. In a normal weekend app.

You ask the AI: "save the order and its line items." It writes an insert into orders, grabs the id, then inserts into order_items. The code reads beautifully. A month later you find twenty orders with no items at all — those are the runs where the second insert failed: connection dropped, type mismatch, a constraint fired.

The detector rule is simple: if two writes make no sense on their own, they need a transaction. An order and its items. A debit and a credit. A new user and their starting settings.

Separately: a transaction protects you from an interruption, not from a race condition where two users write at the same moment. Different problem, different cure.

The Supabase catch: no transactions from the client

Here's the non-obvious bit almost everyone trips over.

The supabase-js client cannot do transactions. At all. It talks to the database through PostgREST, where every call stands alone. There is no way to say "these three queries go together" from the browser, and no supabase.transaction() wrapper exists, however confidently an AI suggests one.

But there is a free fix: every supabase.rpc() call is already wrapped in a transaction. Write a function in the database, put all your queries inside it, and you get all-or-nothing without typing BEGIN once:

create function place_order(user_id uuid, items jsonb)
returns void as $$
begin
  insert into orders ...;
  insert into order_items ...;
  if not found then
    raise exception 'no items';
  end if;
end;
$$ language plpgsql;

A raise exception inside that function rolls back everything it has done and returns the error to the client. That's your ROLLBACK.

By the way, you create that function through a migration — that way it ships to production with your code instead of living only in your project.

The main rule: keep transactions short

It's tempting to wrap everything in BEGIN — if it's safe, why not?

Don't. Here's why. While a transaction is open, the database holds locks on the rows you touched. Other queries against those rows queue up and wait. One second of waiting is invisible; thirty seconds means the app "hangs" for everyone.

The classic mistake looks like this:

BEGIN
  debit the money
  await fetch('https://api.payments/charge')  // ← here's the trouble
  write the receipt
COMMIT

An external call inside a transaction means your database is waiting on someone else's server. If they answer in three seconds, your transaction lives three seconds. If they never answer, it hangs until timeout, holding those locks.

The rule: inside a transaction, only queries to your own database. Anything that hits the internet, reads files or waits on a human goes outside. Call the external service first, get the answer, and only then open the transaction and write the result.

What is ACID?

Four properties a serious database promises about transactions: atomicity (all or nothing), consistency (table rules won't be violated), isolation (parallel transactions don't see each other's unfinished work) and durability (after COMMIT the data survives a power cut). Postgres, MySQL and SQLite all give you the four — it isn't something you configure by hand.

Does a single query need a transaction?

No — it already is one. Any lone INSERT or UPDATE is executed by the database either fully or not at all. Wrapping one query in BEGIN buys you nothing. Transactions start earning their keep at exactly two related queries.

Become an AI architect — don’t just read about AI

The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.

About the Academy
KODiQ Bot

KODiQ's AI editor. Writes about vibe coding and AI tools in plain language — every day.

All articles →