Transactions are one of those things every developer uses, but very few can clearly explain. Here’s the easiest way to understand it: a transaction is a group of SQL statements that succeed or fail together.

Think of it like writing an email. You don’t send each sentence individually — you draft everything, review it, and only then hit Send. Until you hit send, nothing becomes “official.”

The 4 Rules Every Transaction Must Follow (ACID)

Every database transaction follows the famous ACID properties:

• Atomicity — all or nothing; no half-failed changes
• Consistency — the database moves from one valid state to another
• Isolation — your changes don’t interfere with others mid-transaction
• Durability — once committed, it’s safely stored

If you only remember one thing, remember Atomicity: everything commits, or nothing does!

Starting, Committing, and Rolling Back

Begin a transaction:

BEGIN;
    

Do your work:

UPDATE account SET balance = balance - 50 WHERE account_id = 1;
UPDATE account SET balance = balance + 50 WHERE account_id = 2;
    

Make it official:

COMMIT;
    

Or undo everything:

ROLLBACK;
    

Commit = “Looks good, save it.” Rollback = “Pretend this never happened.”

Real-World Example from the Pagila Database

Here’s a simple example using the Pagila sample database. Let’s say we want to update an actor’s first and last name together (or not at all).

BEGIN;

UPDATE actor
SET first_name = 'JOHNNY'
WHERE actor_id = 12;

UPDATE actor
SET last_name = 'BANANAS'
WHERE actor_id = 12;

COMMIT;
    

If the second update fails for any reason, you just issue ROLLBACK and both changes vanish. No partially-updated actors named JOHNNY-something stuck in the database.

Autocommit: The Thing Most Developers Forget

Most SQL clients (psql, DBeaver, PgAdmin) default to autocommit mode. That means every single statement automatically commits unless you explicitly start a transaction.

So if you want the safety of a transaction, you must run BEGIN yourself.

When Should You Use Transactions?

Always use a transaction when multiple statements must succeed together:

• Transferring money between accounts
• Inserting into multiple related tables
• Updating several rows that must stay in sync
• Performing a batch update you might want to undo

If you’ve ever done a large UPDATE and prayed nothing goes wrong — yes, that should have been inside a transaction.

Conclusion

Transactions are simple once you get the idea: they let you treat multiple SQL statements as one logical unit. Start with BEGIN, make your changes, then choose COMMIT or ROLLBACK.

The more you work with them, the more you’ll wonder how anyone builds reliable systems without them. And if you want to go deeper — isolation levels, locks, MVCC — I’ve got more articles coming.