Basic Commands for Working with Transactions: BEGIN, COMMIT, ROLLBACK

If you want all the changes in your database to be under control and done right, PostgreSQL uses the concept of a transaction. It's a group of SQL operations that run as a single unit. If something goes wrong, you need to roll back the changes. That's where the BEGIN, COMMIT, and ROLLBACK commands come in.

Transaction commands aren't just for order — they play a key role in protecting your data from mistakes and crashes. When you run a series of SQL operations, it's super important to make sure everything works right: if one command fails, the database should go back to its original state. That's one of the main reasons we even need transaction commands — they help keep your data consistent.

Plus, transactions give you what's called atomicity: either all the changes go through together, or none of them do. This way, you never get a situation where the database is "half-updated" — like, the money is gone, but the product didn't get added to the order.

And of course, transactions give you flexibility. You can build complex chains of actions, control when to lock them in with COMMIT or roll them back with ROLLBACK, and even do targeted rollbacks using SAVEPOINT. All this makes working with the database not just safe, but also manageable.

The BEGIN Command

The BEGIN command tells PostgreSQL you're starting a transaction. After you run it, all the changes you make are "in limbo" until you finish the transaction with COMMIT or cancel the changes with ROLLBACK.

Classic example: let's say you want to transfer 100 units of currency from one account to another. You start the transaction with BEGIN:

BEGIN;
-- Started the transaction
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Decreased balance on account 1
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Increased balance on account 2

The COMMIT Command

The COMMIT command finishes the transaction and saves all the changes you made in it to the database. After you run this command, the changes are visible to everyone.

Example of finishing a transaction:

COMMIT;
-- All changes in the transaction are saved

Now the balance changes we made in the example above are "permanent." The money has been transferred successfully.

The ROLLBACK Command

If you notice a mistake during the transaction or decide to cancel the changes, you can use the ROLLBACK command. It cancels all operations done after the BEGIN command.

Let's say, during the money transfer, you find out there's not enough funds in the account. Then the transaction gets rolled back:

BEGIN;
-- Started the transaction
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Oops, error: not enough funds.
ROLLBACK;
-- All changes are canceled, database is back to its original state

After the ROLLBACK command, no changes are saved in the database. This is super handy for preventing mistakes.

Full Example: Transferring Money Between Accounts

Let's put it all together. Here's a full transaction example — with balance check, money transfer, and the ability to roll back if something goes wrong:

-- Wrap it in an anonymous PL/pgSQL DO block:
DO $$
DECLARE
    current_balance NUMERIC;
BEGIN
    SELECT balance INTO current_balance FROM accounts WHERE account_id = 1;

    IF current_balance >= 100 THEN
        UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
        UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
    ELSE
        RAISE NOTICE 'Insufficient funds';
    END IF;
END $$;

This example shows how transaction commands work together to prevent data inconsistency.

Autocommit Setting Quirk

By default, PostgreSQL has autocommit mode turned on, so if you run commands outside an explicit transaction, they're saved to the database right away. For example:

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Changes are saved right away, even without COMMIT

If you want to manage transactions manually, it's a good idea to always use BEGIN to explicitly start a transaction.

Autocommit is a client-side mode, not a server setting. PostgreSQL itself has no such setting. How you control autocommit depends on the client:

-- In psql:
\set AUTOCOMMIT off

-- In DBeaver / DataGrip: a UI toggle
-- In JDBC: connection.setAutoCommit(false)

Once autocommit is off, every statement implicitly opens a transaction that you must end explicitly with COMMIT or ROLLBACK.

Common Mistakes When Working with Transactions

Missed COMMIT. If you forget to run COMMIT, your changes will stay unfinished and get lost when the session ends.

Locks. While a transaction is open, the resources it uses can stay locked. This can cause problems with parallel access to data.

Unnecessary ROLLBACK. Sometimes devs get too cautious and roll back a transaction for no reason. This leads to repeated calculations and puts extra load on the database.

Stuck transactions. If you start a transaction but forget to finish it (neither COMMIT nor ROLLBACK), it can cause the session to hang and lock up database resources.

Practical Use

At interviews for a developer or DBA (database administrator) position, you might get asked about how transactions work in PostgreSQL. Knowing the BEGIN, COMMIT, and ROLLBACK commands shows you know how to work with data safely and efficiently.

In real life, transactions are especially useful for building reliable systems, like handling orders in online stores or calculating bonus points in a loyalty program.

Now that you get the basic transaction commands, we can move on and dive into cool stuff like using SAVEPOINT and working with isolation levels. PostgreSQL is a bottomless sea of possibilities, so hang tight.