What Is a Database Transaction? All or Nothing, Explained
A transaction makes several database writes succeed or fail together. Here is the SQL, the Node pattern, and the half-finished-write bug that AI-generated code often ships.
A database transaction is a group of SQL statements that the database treats as one unit. Either every statement in the group takes effect, or none of them does. If something fails halfway through, the database puts everything back the way it was before the group started.
That is the whole idea, and it matters more than it sounds. Most real actions in an app touch more than one row: an order and its line items, a payment and a balance, a signup and a welcome record. Without a transaction, a crash between step one and step two leaves your data in a state no one intended.
The classic example: moving money
The PostgreSQL documentation explains transactions with a bank transfer, and it is still the clearest picture. Moving $100 from Alice to Bob takes at least two updates: subtract from one account, add to the other.
BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
COMMIT;If the server dies after the first update and before COMMIT, nothing is saved. Alice keeps her money. Without the BEGIN and COMMIT, the database would have saved the first update on its own, and $100 would have vanished from the system.
The PostgreSQL tutorial on transactions also notes that other sessions cannot see a transaction's half-done work. They see the old balances, then the new ones, never the middle.
The four commands to know
BEGIN opens a transaction block.
COMMIT makes every change in the block permanent, and visible to other sessions, all at once.
ROLLBACK throws away every change in the block. Use it when a check fails, for example when a balance would go negative.
SAVEPOINT marks a spot inside the block.
ROLLBACK TOthat name undoes only what came after it, and the earlier work survives.
PostgreSQL quietly wraps every single statement in its own transaction when you do not open one yourself. That is why a lone UPDATE is safe, and why the danger only starts when you need two statements to belong together.
What the database promises
The PostgreSQL page describes three guarantees, usually grouped under the acronym ACID:
Atomicity. Seen from outside, the transaction happens completely or not at all.
Durability. Once the database reports the transaction complete, its updates are already logged to permanent storage.
Isolation. Open transactions are invisible to other transactions until they finish.
The remaining letter, consistency, is the textbook promise that your own rules (constraints such as a foreign key or a "balance cannot be negative" check) hold before and after. The database enforces the rules you declare. It cannot guess the ones you forgot to write.
Where AI-written code goes wrong
A coding assistant asked to "create an order and its items" will often produce two separate calls: insert the order, then loop and insert the items. Each call is its own tiny transaction. If the second one fails on a bad product id, you now have an order with no items, and the code that charged the card has already run.
The fix is a transaction, and in Node with node-postgres it has one catch. A transaction lives on a single connection, so you must check one client out of the pool and run every statement on it:
const client = await pool.connect();
try {
await client.query('BEGIN');
const { rows } = await client.query(
'INSERT INTO orders (customer_id) VALUES ($1) RETURNING id', [customerId]);
for (const item of items) {
await client.query(
'INSERT INTO order_items (order_id, product_id, qty) VALUES ($1, $2, $3)',
[rows[0].id, item.productId, item.qty]);
}
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}The node-postgres pooling guide says pool.query() may run each call on any free client, which is exactly why it is the wrong tool inside a transaction. It also stresses that you must always release a checked-out client, even after an error, or the pool eventually runs dry. That is the finally block above.
Keep transactions short and boring
An open transaction holds on to the rows it has changed, and other sessions that want the same rows have to wait. So keep the block to database work only. Do not call an email service, a payment API or an AI model in the middle of one. Do those before or after, and use an idempotency key so a retry cannot charge twice.
Transactions also do not cure every concurrency problem. Two requests reading the same balance and both writing it back is still a race condition unless you lock the row or write the update as a single balance = balance - 100 statement, as the example above does.
A prompt to give your coding agent
Find every place in this codebase where one user action writes to more than
one table or row. For each, wrap the writes in a single transaction on one
checked-out connection, roll back on any error, and release the connection in
a finally block. List the places you changed and the ones you left alone.Ask for the list. The "left alone" half tells you whether the agent actually searched.
For the bigger picture on structuring an app's data layer, start with the guide to building an app with AI, and read how to prompt AI to design a database schema before the first table exists. Running out of connections is a separate trap, covered in database connection limits.
FAQ
What is the difference between COMMIT and ROLLBACK?
COMMIT saves every change made since BEGIN and makes it visible to other sessions. ROLLBACK discards every change since BEGIN, as if the block never ran.
Do I need a transaction for a single INSERT?
No. PostgreSQL treats each single statement as its own transaction, so a lone insert either happens or it does not. You need an explicit transaction when two or more statements must stand or fall together.
Does an ORM or Supabase client use transactions automatically?
Not across separate calls. Each call is usually its own transaction. Look for the tool's transaction helper, or put the multi-step logic in a database function so it runs as one unit.
What happens to a transaction if my app crashes?
If the connection drops before COMMIT, the database rolls the transaction back. The half-done work is discarded, which is the point.
Can a transaction be too long?
Yes. While it is open, rows it touched stay unavailable to conflicting writers, and a forgotten open transaction can stall other requests. Keep them to a few statements and no outside network calls.
How did this land?
About the author

Senior Editor, AI & Product
Cecilia leads the Swarmz editorial desk. She has spent a decade turning complex AI and product topics into writing people actually finish, and she owns the blog's quality bar.


