Working with Transactions to Keep Your Data Consistent

Let’s imagine you’re building an online store app, and when processing an order payment you need to:

1. Hold money from the customer’s card.
2. Decrease the product quantity in the warehouse.
3. Create a record about the successful transaction.

What if something goes wrong in the middle of all this? Like, the product runs out after the money is held, but before the order record is created? Everything goes sideways: the money is “stuck”, the order isn’t finished, and your server gets tons of angry emails (and maybe even lawsuits).

Transactions are here to save you from this mess. They let you group a bunch of operations into one “atomic” unit of work with the database. It’s like the “Undo” button in a text editor: if something goes wrong, just roll back to the start.

How Do Transactions Keep Data Consistent?

Transactions are based on the ACID concept:

• Atomicity — All operations inside a transaction are done completely or not at all. “All or nothing.”
• Consistency — Data stays consistent before and after the transaction.
• Isolation — One transaction doesn’t mess with others.
• Durability — Once a transaction is done, its result is saved even if the system crashes.

Why am I repeating this again? Because this is the ideal everyone’s aiming for. And… it’s rarely fully achievable. When we come back to transactions later in this course, you’ll see that we’ll have to sacrifice some ACID principles.

So enjoy this moment when transactions are simple and beautiful. Let’s get to some examples!

Example: Using Transactions

Let’s look at a scenario where we add a student and register them for a course.

Suppose we’re working with a university database. We’ve got some non-regular listeners for our courses. If there’s a spot in the course, we register such a listener as a student (temporarily) and add them to the course. Here’s how it goes down.

When adding a new student to the database and registering them for a course, we need to:

1. Add a record to the students table.
2. Create a record in the enrollments table linking the student to the course.

If something goes wrong (like the course is already full), we need to roll back the operation so the data doesn’t get out of sync between tables. Here’s how you do it:

-- Start the transaction
BEGIN;

-- Step 1: Add the student
INSERT INTO students (name, age, gender)
VALUES ('Otto Lin', 20, 'Male')
RETURNING id;

-- Let’s say we got id = 10

-- Step 2: Register them for the course
INSERT INTO enrollments (student_id, course_id)
VALUES (10, 5);

-- Everything went fine? Save the changes
COMMIT;

What Happens If There’s an Error?

Let’s say there’s an error when registering for the course: for example, the course doesn’t exist. If you forget about the transaction, the student record will stay in the students table, but not in enrollments. That breaks data consistency. To avoid this, we can use the ROLLBACK command.

-- Start the transaction
BEGIN;

-- Step 1: Add the student
INSERT INTO students (name, age, gender)
VALUES ('Otto Lin', 20, 'Male')
RETURNING id;

-- Step 2: Try to register them for the course
INSERT INTO enrollments (student_id, course_id)
VALUES (10, 999); -- Error: no course with id = 999!

-- Roll back all changes
ROLLBACK;

As a result, none of the operations will be done, and the database will stay just like it was before the transaction.

Using SAVEPOINT for Control

Now imagine a more complex scenario. You want to do several operations, but at some point you need to roll back only to a certain spot, not cancel the whole thing.

Let’s do a step-by-step student registration

-- Start the transaction
BEGIN;

-- Add the student
SAVEPOINT add_student; -- Create a savepoint
INSERT INTO students (name, age, gender)
VALUES ('Anna Song', 22, 'Female');

-- Register her for the first course
SAVEPOINT enroll_course_1; -- Another savepoint
INSERT INTO enrollments (student_id, course_id)
VALUES (11, 5);

-- Register her for the second course (error here)
INSERT INTO enrollments (student_id, course_id)
VALUES (11, 999); -- Error!

-- Roll back only to the last savepoint
ROLLBACK TO enroll_course_1;

-- Continue the process
INSERT INTO enrollments (student_id, course_id)
VALUES (11, 6);

-- Save the changes
COMMIT;

This way, errors in one part of the process don’t mess up data in other parts.

Checking for Changes

If an SQL query changes something, you can check if there were actually any changes or not.

There might be a situation where you run DELETE, but no rows match the WHERE. Or you run UPDATE, but the data was already changed so nothing really happened.

For this, there’s a special system variable FOUND. It tells you if any rows were affected by the last SQL query:

• FOUND = TRUE — the query updated/deleted something;
• FOUND = FALSE — nothing was deleted or changed.

It doesn’t work with a regular SELECT, only for tracking changes.

Practical Use: Payment Processing

Transactions are especially useful in financial apps. Let’s look again at a system that needs to transfer money from one account to another.

Important: the snippet below only works inside a PL/pgSQL block (DO $$ BEGIN ... END $$; or a function). In plain SQL there is no IF ... THEN ... END IF, no FOUND variable and no RAISE EXCEPTION — running it as is in psql will produce a syntax error on IF.

-- Start the transaction
BEGIN;

-- Step 1: Take money from the first account
UPDATE accounts
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;

-- Step 2: Check if the operation was successful (rows were changed)
IF NOT FOUND THEN
    ROLLBACK; -- Rollback if not enough funds
    RAISE EXCEPTION 'Not enough funds!'; -- Error! Throw an exception
END IF;

-- Step 3: Add money to the second account
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

-- Commit the transaction
COMMIT;

A correct PL/pgSQL wrapper for the same logic looks like this:

DO $$
BEGIN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Not enough funds!';
    END IF;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
END $$;

Transaction control (BEGIN/COMMIT/ROLLBACK) is done outside the DO block, because inside DO a transaction is already open.

Here, if the client tries to transfer more money than they have, the transaction will roll back and the database won’t end up in a “stuck” state.

Features and Common Mistakes

Forgot COMMIT: if you forget to run COMMIT at the end of a transaction, the database will be “waiting”, and your changes won’t be saved.

Forgot WHERE: updating or deleting data without a condition can lead to disaster. For example, DELETE FROM students without WHERE will delete all students.

Long transactions: if a transaction is open too long, it can block access to data, causing performance issues. Always finish transactions (COMMIT or ROLLBACK) as quickly as possible.

Transactions are your only real friend when it comes to keeping your data consistent. They help you avoid inconsistency, especially in tricky scenarios like user registration, payment processing, or updating related tables. Once you get the hang of BEGIN, COMMIT, ROLLBACK, and SAVEPOINT, you’ll be able to build more reliable and secure apps.