Intro to Transactions: BEGIN, COMMIT, ROLLBACK

In this course, we’ll talk about transactions three times. And every time, we’ll discover something new. Right now, let’s cover the basics of transactions. Later in the course, we’ll dig into transaction isolation levels, and at the very end — the quirks of nested transactions.

What’s a Transaction?

Let’s start simple — with a definition. A transaction is a group of actions that only happen together. That’s it.

Here’s the key takeaway: if something (anything!) in the group fails, you need to roll back the results of all the other actions and put the system back to its original state.

From SQL’s point of view, a transaction is a set of actions (like inserting, updating, or deleting data) that guarantees either all the operations inside the transaction succeed, or none of them happen at all. This makes working with databases reliable and consistent, especially in critical scenarios like handling payments or updating related data.

Imagine you’re transferring money between two bank accounts. To keep it simple, there are two steps:

1. Money gets taken from one account.
2. The same amount gets added to another account.

If something crashes at the very end (like the server goes down), it’s super important that nobody loses out. In other words, either both steps succeed (the transaction is committed), or both steps are rolled back (the transaction is canceled).

Transactions and the ACID Principles

Transactions are built on the ACID principles, which remind us of four key features:

• Atomicity: all or nothing. Either all the operations inside the transaction happen, or they all get rolled back.
• Consistency: data stays in a valid state before and after the transaction.
• Isolation: every transaction works as if it’s the only one in the system.
• Durability: once you commit, the data sticks around, even if the server crashes.

Core Commands for Managing Transactions

Get ready for some hands-on stuff! Here are the three main commands for working with transactions:

1. BEGIN
   Starts a new transaction. All the following operations will run inside it.

2. COMMIT
   Saves the changes. After this command, everything you did becomes permanent.

3. ROLLBACK
   Cancels the changes. If something goes wrong, you can roll back the transaction and your data will stay as it was.

Basic Transaction Syntax

Here’s a simple transaction structure:

BEGIN; 
-- your SQL operations go here
COMMIT;

Example using ROLLBACK:

BEGIN;
-- changing the students table
UPDATE students
SET grade = grade + 10
WHERE id = 1;

-- Oh! We realized that was a mistake.
ROLLBACK;

Real-World Example of Using a Transaction

Let’s say we have these tables:

students:

courses:

Suppose we want to enroll a student in a course and update their average grade at the same time:

BEGIN;

-- Step 1: Add a record to the "course enrollments" table
INSERT INTO course_enrollments (student_id, course_id)
VALUES (1, 2);

-- Step 2: Update the student's average grade
UPDATE students
SET grade = grade + 5
WHERE id = 1;

COMMIT;

What happens if the server crashes between the first and second step? If we didn’t use a transaction, the data would be inconsistent: the course enrollment would be added, but the grade wouldn’t be updated. But with a transaction, both operations either go through or both get canceled.

Handling Errors Inside Transactions

Sometimes things go sideways, and you need to handle errors the right way. In PostgreSQL, the transaction will automatically roll back if there’s an error.

Let’s make a mistake on purpose and see what happens. Imagine there’s a unique constraint on the student_id column in the course_enrollments table. Let’s try to add a duplicate row:

BEGIN;

INSERT INTO course_enrollments (student_id, course_id)
VALUES (1, 2);

-- END OF TRANSACTION (not done yet)
COMMIT;

If you try to insert a student who’s already enrolled in the course, you’ll get an error, and PostgreSQL will automatically end the transaction with a rollback.

Using ROLLBACK to Manually Undo Changes

Most of the time, you can’t predict errors, and you’ll want to roll back the transaction if something goes wrong:

BEGIN;

-- Add a new student
INSERT INTO students (name, grade)
VALUES ('Omori Sanny', 75);

-- Oops! We realized we added the student by accident.
ROLLBACK;

After the ROLLBACK command, the table stays the same — Omori Sanny never showed up in students.

Handy Tips and Common Mistakes

Working with transactions is way easier if you keep a few important rules in mind:

• Always use transactions if your operation has more than one step, especially when changing data in multiple tables.
• Never forget to commit your changes (COMMIT). Otherwise, the transaction stays open and your data won’t change.
• Wrap complex operations in transactions to keep your data consistent.
• If you see an error at any step, don’t be afraid to use ROLLBACK.

Now that you know how to control your operations with transactions, it’s time to put this knowledge to work and move on to learning how transactions help keep your data solid!