10.15. Transactions

Why Transactions Matter

In real applications you rarely run a single SQL statement. You often need a group of operations to succeed or fail together. For example, in a banking app:

Subtract $100 from Alice.
Add $100 to Bob.

If the first succeeds but the second fails, the data is wrong. Transactions solve this by grouping operations so they behave like a single unit.

A transaction is a sequence of one or more SQL statements that the database treats as a single logical operation.

Key idea: A transaction applies all its changes or none of them.

Typical use cases:

Money transfers
Creating an order with several order items
Updating multiple related tables during a single business operation

The ACID Properties of Transactions

You already have a full ACID chapter elsewhere, so here we focus on how ACID appears in SQL usage.

Transactions are defined by ACID properties:

Property	In practice, for you as an SQL user
Atomicity	Either all statements in the transaction are saved, or none are.
Consistency	The database moves from one valid state to another. Rules stay valid.
Isolation	Concurrent transactions do not corrupt each other's work.
Durability	Once committed, data is persisted even after failures.

You do not implement ACID manually. Your job is to use transactions correctly with BEGIN, COMMIT, and ROLLBACK so the database can guarantee these properties.

Basic Transaction Commands

Most SQL databases support these core commands:

Command	Meaning
`BEGIN`	Start a new transaction.
`COMMIT`	Save all changes made in the current transaction.
`ROLLBACK`	Undo all changes made in the current transaction.

Many databases also allow:

BEGIN TRANSACTION or START TRANSACTION
ROLLBACK TO SAVEPOINT name and SAVEPOINT name (more advanced, see later)

In many client libraries, you will not type these commands directly, but they exist under the hood. In SQL shells you often use them explicitly.

Autocommit vs Manual Transactions

Most SQL tools and drivers work in autocommit mode by default.

Autocommit ON
Every single statement is its own transaction.

INSERT is automatically committed if it succeeds.
If it fails, only that statement is rolled back.

Manual Transactions
You group multiple statements in one transaction using BEGIN and COMMIT.
Nothing is permanently saved until you COMMIT.

Mode	Example behavior
Autocommit	`UPDATE users SET name = 'Bob' WHERE id = 1;` is committed instantly.
Manual	`BEGIN;` then several SQL statements, then `COMMIT` or `ROLLBACK`.

Many programming languages have methods like:

connection.autocommit = False
connection.commit()
connection.rollback()

Starting and Ending a Transaction

A Simple Transaction Example

Imagine a bank_accounts table:

sql

CREATE TABLE bank_accounts (
    id          INT PRIMARY KEY,
    owner_name  VARCHAR(100),
    balance     NUMERIC(10, 2) NOT NULL
);

We want to transfer 100 from account 1 to account 2.

Incorrect way with autocommit

If every statement is its own transaction:

sql

UPDATE bank_accounts
SET balance = balance - 100
WHERE id = 1;  -- succeeds
UPDATE bank_accounts
SET balance = balance + 100
WHERE id = 2;  -- fails, maybe due to an error

Result: account 1 lost 100, account 2 did not gain 100. Inconsistent.

Correct way with a transaction

sql

BEGIN;
UPDATE bank_accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE bank_accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;

Now both updates are part of one transaction. Two cases:

Both updates succeed, COMMIT applies them permanently.
One update fails, the database automatically rolls back the transaction (or you can explicitly ROLLBACK).

Explicit Rollback Example

Suppose we detect a problem in our application logic and want to cancel the transaction:

sql

BEGIN;
UPDATE bank_accounts
SET balance = balance - 100
WHERE id = 1;
-- We realize something is wrong:
ROLLBACK;  -- Undo the update

After ROLLBACK the database is in the same state as before BEGIN.

Example: Multi‑Step Operation as a Transaction

Consider an online store with:

sql

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status      VARCHAR(20) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
    id          SERIAL PRIMARY KEY,
    order_id    INT NOT NULL,
    product_id  INT NOT NULL,
    quantity    INT NOT NULL,
    price       NUMERIC(10, 2) NOT NULL
);

Creating an order might require:

Insert a row into orders.
Insert several rows into order_items.
Decrease stock for each product in an inventory table.

We must guarantee that either all three steps happen or none.

sql

BEGIN;
INSERT INTO orders (customer_id, status)
VALUES (42, 'PENDING')
RETURNING id;
-- Suppose the returned id is 100
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
    (100, 5, 2, 9.99),
    (100, 8, 1, 19.99);
UPDATE inventory
SET stock = stock - 2
WHERE product_id = 5;
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 8;
COMMIT;

If any INSERT or UPDATE fails, you can:

sql

ROLLBACK;

and the database will forget all of these changes. The order is not partially created.

Savepoints for Partial Rollbacks

Sometimes you want to undo only part of a transaction, not the whole thing. This is where savepoints help.

A savepoint is a named point inside a transaction you can roll back to.

Commands:

SAVEPOINT name;
ROLLBACK TO SAVEPOINT name;
RELEASE SAVEPOINT name; (optional, to discard it)

Example with Savepoints

Consider a transaction that tries to insert 3 items. The second insert may fail but you want to still insert others:

sql

BEGIN;
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (200, 1, 1, 5.00);
SAVEPOINT after_first_item;
-- Second insert might violate a constraint
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (200, 9999, 1, 100.00);  -- Product 9999 does not exist
-- If the second insert fails:
ROLLBACK TO SAVEPOINT after_first_item;
-- Continue with other operations
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (200, 2, 3, 7.50);
COMMIT;

Here:

The first insert is kept.
The second failing insert is removed using ROLLBACK TO SAVEPOINT.
The third insert is added.
COMMIT permanently saves the first and third insert.

Transaction Isolation and Concurrency

When several users access the database at the same time, transactions may interfere with each other. The database uses locks and isolation levels to prevent data corruption.

You will learn isolation levels in more detail elsewhere. Here is the idea:

The database ensures that concurrent transactions behave as if they were executed one after another, depending on the chosen isolation level.
Some isolation levels allow more concurrency but can show slightly "stale" data.
Stricter isolation means fewer anomalies but sometimes more waiting and less throughput.

As a beginner backend developer you should know:

Long transactions can block other queries.
Keep transactions as short as possible.
Do not wait for user input inside a transaction in your application code.

Common Transaction Patterns in Backend Code

In real applications you rarely type BEGIN manually. The driver / ORM abstracts this. Here are common patterns you will see.

Pattern 1: Explicit transaction block

Pseudocode example:

python

conn.autocommit = False
try:
    cursor = conn.cursor()
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    conn.commit()  # both updates are saved together
except Exception:
    conn.rollback()  # undo all updates
    raise

Key ideas:

Turn off autocommit.
Wrap operations in try/except.
Call commit at the end if everything is fine.
Call rollback if any error happens.

Pattern 2: Context manager style (Python example)

Many libraries allow a with block that commits on success and rolls back on error:

python

with connection:
    with connection.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
# Leaving the 'with connection' block:
# - if no error, commit
# - if error, rollback

Typical Errors and Pitfalls

Forgetting to Commit

If autocommit is off and you never call COMMIT, your changes are not visible to other connections and will be lost when your session closes.

Example:

sql

BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Alice');
-- You forget to COMMIT and just exit the client
-- The insert is rolled back automatically

Symptom: "I inserted data but it disappeared."

Doing Too Much Work in One Transaction

If you keep a transaction open for a long time:

Other transactions might be blocked waiting for locks.
More chances of conflicts.
Higher risk of deadlocks.

Rule of thumb:

Keep transactions short and focused on a single business operation.

Mixing Unrelated Operations

Bad idea:

sql

BEGIN;
-- Transfer money between users
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Also update some unrelated reporting table
UPDATE daily_stats SET last_run = NOW();
COMMIT;

If the reporting update fails, the money transfer is also rolled back. Usually you want separate transactions for unrelated tasks.

When to Use a Transaction

You should explicitly use a transaction when:

You perform more than one related change that must be consistent.
You insert into multiple tables as part of one logical action.
You need to ensure data integrity in complex operations.

Typical examples in backend work:

Scenario	Use a transaction?
Simple single-row insert log entry	Usually no
Creating an order with items and payments	Yes
Updating user profile and sending email	DB update yes, email is external
Moving items between folders	Yes
Bulk import of data	Often yes, but may split into chunks

Summary

A transaction groups one or more SQL statements into a single unit that is either fully applied or not applied at all.
Use BEGIN, COMMIT, and ROLLBACK to control transactions when not in autocommit mode.
Savepoints let you roll back part of a transaction.
Transactions help maintain data integrity in multi-step operations like money transfers and order creation.
Keep transactions short, focused, and explicit in your backend code to avoid locking and performance issues.