Transactions

Transactions ensure that multiple SQL operations either all succeed or all roll back. Use transactions for balance changes, order creation, inventory deduction, batch imports, and other consistency-sensitive flows.

Basic usage

begin;

insert into public.orders (user_id, status)
values ('user-1', 'pending');

insert into public.order_logs (user_id, action)
values ('user-1', 'create_order');

commit;

If any step fails, run rollback.

begin;

update public.accounts
set balance = balance - 100
where id = 1;

update public.accounts
set balance = balance + 100
where id = 2;

rollback;

Transactions in Node.js

All statements in a transaction must use the same database connection. Do not execute them separately on the pool.

const client = await pool.connect();

try {
  await client.query("begin");
  await client.query("update accounts set balance = balance - $1 where id = $2", [100, 1]);
  await client.query("update accounts set balance = balance + $1 where id = $2", [100, 2]);
  await client.query("commit");
} catch (error) {
  await client.query("rollback");
  throw error;
} finally {
  client.release();
}

Isolation levels

PostgreSQL supports multiple isolation levels. Most applications can use the default read committed.

begin isolation level repeatable read;
-- execute queries
commit;

For concurrent updates to balances, inventory, or quotas, combine transactions with row locks or unique constraints.

select id, stock
from public.products
where id = 1
for update;

Common issues

• Long transactions occupy connections and locks, affecting other requests.
• Waiting for external APIs inside a transaction increases lock duration.
• Very large batch imports can be expensive to roll back. Commit by batch.
• DDL operations can take strong locks. Schedule them during maintenance windows.

Relationship with HTTP API

A single HTTP API write is usually completed as one database operation. For consistency across multiple SQL statements, wrap logic in an RPC function or use a server-side PostgreSQL direct connection.

For RPC examples, see Call RPC.