PostgreSQL Transaction

A database transaction is a single unit of work made up of one or more SQL operations. PostgreSQL transactions are ACID-compliant: Atomicity guarantees all-or-nothing execution; Consistency enforces data rules; Isolation controls visibility between concurrent transactions; Durability makes committed changes permanent even after a crash.

Starting, committing, and rolling back transactions

Every statement in PostgreSQL runs inside a transaction. When you do not use BEGIN, each statement is implicitly committed. To group statements, start an explicit transaction:

BEGIN;

INSERT INTO accounts (name, balance)
VALUES ('Alice', 10000);

-- Changes are visible in this session but not yet to others
SELECT id, name, balance FROM accounts;

COMMIT;  -- now permanently written to disk

To undo everything since BEGIN, use ROLLBACK:

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

-- Something went wrong — undo the change
ROLLBACK;

SELECT * FROM accounts;

 id | name  |  balance
----+-------+----------
  1 | Bob   | 10000.00
  2 | Alice | 10000.00

All three forms of each statement are equivalent: BEGIN / BEGIN WORK / BEGIN TRANSACTION and COMMIT / COMMIT WORK / COMMIT TRANSACTION and ROLLBACK / ROLLBACK WORK / ROLLBACK TRANSACTION.

A complete bank transfer example

The classic use case for transactions is a fund transfer: both the debit and the credit must succeed together or not at all.

CREATE TABLE accounts (
  id      INT GENERATED BY DEFAULT AS IDENTITY,
  name    VARCHAR(100) NOT NULL,
  balance DEC(15, 2) NOT NULL CHECK (balance >= 0),
  PRIMARY KEY (id)
);

INSERT INTO accounts (name, balance)
VALUES ('Bob',   10000),
       ('Alice', 10000);

-- Transfer $500 from Bob to Alice
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

SELECT * FROM accounts;

 id | name  |  balance
----+-------+----------
  1 | Bob   |  9500.00
  2 | Alice | 10500.00

If the second UPDATE fails (for example, because Alice does not exist), calling ROLLBACK undoes the debit from Bob as well, keeping the balances consistent.

Production tips for transactions

• Keep transactions short. Long-running transactions hold locks, block autovacuum, and increase the chance of conflicts under concurrent load.
• Never leave a transaction open in application code without explicit error handling; always ROLLBACK on exceptions.
• Use SAVEPOINT name and ROLLBACK TO SAVEPOINT name to undo part of a transaction without abandoning the whole thing.
• Set idle_in_transaction_session_timeout to automatically cancel sessions that hold an open transaction without doing work.
• In application frameworks (psycopg2, JDBC, etc.), check whether auto-commit is enabled before assuming explicit transaction control is active.