Diving Deeper into Transactions

We already mentioned earlier in the course what a transaction is. Just to remind you, it's a sequence of operations that should be executed as a single unit. Like, say, you need to make a bank transfer. In that case, you take money from one account and add it to another. If one of these actions fails (like, the money gets taken out but doesn't get added to the other account), that's a huge mess. This is exactly where transactions save the day.

A transaction either runs completely or doesn't run at all. This is called the "all or nothing" principle.

BEGIN;
-- Decrease balance on one account
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Increase balance on another account
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- Apply changes

If something goes wrong, you can roll back the changes using ROLLBACK.

What Are the ACID Properties of Transactions

When we talk about transactions in PostgreSQL (and in relational databases in general), you’ll often hear the acronym ACID — and nope, it’s not about chemistry. ACID stands for Atomicity, Consistency, Isolation, Durability. These four properties make sure your data is handled safely, consistently, and without any nasty surprises.

Atomicity
A transaction is either done completely or not done at all. If something inside it goes wrong — everything gets rolled back. Imagine: you’re transferring money, and suddenly there’s an error — either everything is canceled, or the transfer goes through fully. No “half-done” operations.

Consistency
After a transaction finishes, the database stays in a correct, logical state. All rules, constraints, and relationships between tables must be followed. For example, if you’re not allowed to have a negative balance, a transaction that would break that just won’t be saved.

Isolation
While one transaction isn’t finished, another shouldn’t see its intermediate data. This protects you from weird effects where you see “undefined” data states. Imagine in an online store, the money’s already been taken, but the item hasn’t been added to the order yet — not cool, right?

Durability
If a transaction finishes successfully, all its changes are guaranteed to be saved. Even if the power goes out right after — the data stays in the database. It’s like hitting “Save” and knowing for sure everything’s locked in.

These four properties are the reason why transactions are considered a rock-solid mechanism for working with databases.

Examples of Transaction Scenarios

Theory is great, but the real power of transactions shows up in real-world tasks. In situations with money, related tables, or bulk changes — transactions are a must-have tool. They help you not just run operations, but do it confidently, without risking data loss or leaving your database in a “half-working” state.

Here are a few typical examples where transactions really save the day:

1. Handling Payments

When a client transfers money from one account to another, a transaction makes sure the money doesn’t just disappear into thin air:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

If there’s not enough money in the first account, you can roll back the changes:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- Oops, negative balance!
ROLLBACK;

2. Updating Related Tables

Imagine you’re updating a student’s status to "graduated" and at the same time adding a record to the "graduates" table:

BEGIN;
UPDATE students SET status = 'graduated' WHERE student_id = 42;
INSERT INTO graduates (student_id, graduation_date) VALUES (42, '2023-06-10');
COMMIT;

If one of the operations fails (like there’s an error in INSERT), the database will go back to its original state.

3. Bulk Data Updates

Transactions are super useful for running big updates, for example:

BEGIN;
UPDATE orders SET status = 'completed' WHERE delivery_date < CURRENT_DATE;
COMMIT;

If the server crashes or you realize the update went sideways, you can roll back the changes at any time!

Commands for Working with Transactions

PostgreSQL gives you a few key commands:

• BEGIN: starts a new transaction:

  BEGIN;
  

• COMMIT: saves all the changes made in the transaction:

  COMMIT;
  

ROLLBACK: undoes all the changes made in the current transaction:

ROLLBACK;

Example of a Full Cycle

BEGIN;
-- Some operations
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;

-- Decided to roll back the changes
ROLLBACK;

-- Start again
BEGIN;
-- Same operations, but a different transfer
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Finish the transaction
COMMIT;

Transactions in Real Life

Online stores. Lots of platforms use transactions to manage orders and payments. For example, an order only goes through if the payment is successful. If something goes wrong, the order is automatically canceled.

Banking systems. Transactions protect your money from stuff like unexpected power outages.

Transaction history. PostgreSQL keeps WAL (Write-Ahead Logging) journals to restore data if something crashes. That’s the magic that makes transactions reliable.

In the next lecture, we’ll dig deeper into the BEGIN, COMMIT, and ROLLBACK commands, and also check out examples of bulk operations and partial rollbacks with SAVEPOINT. See you then!