How to Use BEGIN and COMMIT in PostgreSQL for Transactions

In PostgreSQL, use BEGIN to start a transaction block and COMMIT to save all changes made during the transaction. These commands help group multiple operations so they either all succeed or all fail together.

Syntax

The BEGIN command starts a transaction block, and COMMIT ends it by saving all changes. If you want to undo changes, use ROLLBACK instead of COMMIT.

• BEGIN; — starts a transaction
• COMMIT; — saves changes made in the transaction
• ROLLBACK; — cancels changes made in the transaction

sql

BEGIN;
-- SQL statements here
COMMIT;

Example

This example shows how to use BEGIN and COMMIT to insert two rows into a table as one transaction. If both inserts succeed, the changes are saved together.

sql

CREATE TABLE accounts (id SERIAL PRIMARY KEY, name TEXT, balance INT);

BEGIN;
INSERT INTO accounts (name, balance) VALUES ('Alice', 100);
INSERT INTO accounts (name, balance) VALUES ('Bob', 150);
COMMIT;

SELECT * FROM accounts;

Output

 id | name  | balance 
----+-------+---------
  1 | Alice |     100
  2 | Bob   |     150
(2 rows)

Common Pitfalls

One common mistake is forgetting to COMMIT after BEGIN, which leaves the transaction open and locks resources. Another is mixing implicit and explicit transactions, which can cause unexpected behavior.

Wrong way (missing COMMIT):

sql

BEGIN;
INSERT INTO accounts (name, balance) VALUES ('Charlie', 200);
-- Forgot COMMIT here

-- The changes are not saved until COMMIT is called

Quick Reference

Command	Purpose
BEGIN;	Start a new transaction block
COMMIT;	Save all changes made in the transaction
ROLLBACK;	Undo all changes made in the transaction
SAVEPOINT name;	Create a savepoint within a transaction
ROLLBACK TO SAVEPOINT name;	Undo changes to a savepoint

Key Takeaways

Use BEGIN to start a transaction and COMMIT to save changes in PostgreSQL.

Always COMMIT or ROLLBACK to close a transaction and release locks.

Transactions group multiple SQL commands so they succeed or fail together.

For partial undo, use SAVEPOINT and ROLLBACK TO SAVEPOINT inside transactions.

Avoid mixing implicit and explicit transactions to prevent locking issues.