PostgreSQL Transactions: Guide to BEGIN, COMMIT & ROLLBACK

Starting a Transaction

A transaction wraps multiple database operations into a single unit of work. If one operation fails, the entire transaction is rolled back, preventing partial updates that could leave data inconsistent.

Connect to the bank database and check the current accounts:

sudo user postgres psql
\c bank

SELECT * FROM accounts;

The table shows Carla, Alice, and Bob with their balances.

Transferring Money with a Transaction

To send $200 from Alice to Bob, begin a transaction:

BEGIN;

The asterisk in the prompt indicates you are now inside a transaction.

Deduct $200 from Alice's account:

UPDATE accounts SET balance = balance - 200 WHERE id = 'ACC001';

Add $200 to Bob's account:

UPDATE accounts SET balance = balance + 200 WHERE id = 'ACC002';

Check the temporary state:

SELECT * FROM accounts;

The balances appear updated, but this is not yet permanent. To make the changes permanent, commit:

COMMIT;

The asterisk disappears from the prompt. Verify the changes are now permanent:

SELECT * FROM accounts;

Rolling Back a Failed Transaction

If an error occurs during a transaction, you can roll back to the state before the transaction began. This prevents any partial changes from being applied.

Start a new transaction:

BEGIN;

Update Alice's balance:

UPDATE accounts SET balance = balance - 200 WHERE id = 'ACC001';

If something goes wrong in your application, roll back:

ROLLBACK;

Check that Alice's balance has returned to its original value:

SELECT * FROM accounts;

ACID Properties

Every PostgreSQL transaction satisfies ACID:

• Atomic: If one part of the transaction fails, the whole transaction fails.
• Consistent: Constraints (check, unique, foreign key, primary key) always hold.
• Isolation: Uncommitted changes are not visible to other transactions.
• Durable: After commit, the change persists on disk.

Isolation in Action

Open a second terminal and connect to the same database:

psql
\c bank

In the first terminal, begin a transaction and update Alice's balance without committing:

BEGIN;
UPDATE accounts SET balance = balance - 67 WHERE id = 'ACC001';
SELECT * FROM accounts;

The first terminal shows Alice's balance as 933.

In the second terminal, query the accounts table:

SELECT * FROM accounts;

Alice's balance remains unchanged because the uncommitted draft is isolated from the second session.

Now commit in the first terminal:

COMMIT;

Run the query again in the second terminal:

SELECT * FROM accounts;

The updated balance is now visible and durable.