KAHIBARO
Discord Login Register ☰
☰

9.13. Transactions

Table of Contents

Why Transactions Matter

When your backend talks to a database, many operations must be treated as a single unit of work. Either everything succeeds together, or nothing changes at all.

Examples:

This “all or nothing” behavior is exactly what database transactions provide.

Definition:
A transaction is a sequence of database operations that is treated as a single logical unit of work. The database guarantees that either all operations inside the transaction take effect, or none of them do.

Backend developers use transactions to keep data correct, even when there are errors, crashes, or concurrent users.

Basic Transaction Concepts

Most relational databases support the same basic transaction commands:

CommandMeaning
BEGIN / START TRANSACTIONStart a new transaction
COMMITSave all changes made in the transaction permanently
ROLLBACKUndo all changes made in the transaction since it began

Conceptually, a transaction works like this:

  1. Start a transaction.
  2. Execute one or more SQL statements.
  3. If everything is OK, commit.
  4. If something goes wrong, rollback.

In pseudocode:

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

If any UPDATE fails, you can ROLLBACK:

sql 
BEGIN;
UPDATE accounts SET balance = balance - 100
WHERE id = 1;
-- Something goes wrong here...
ROLLBACK;

After ROLLBACK, it is as if nothing inside the transaction happened.

Autocommit vs Manual Transactions

Many database clients and drivers use autocommit by default.

In a backend application, you usually:

Example with pseudocode in Python style:

python 
conn = get_db_connection()
try:
    conn.begin()  # START TRANSACTION
    withdraw(conn, from_account_id=1, amount=100)
    deposit(conn, to_account_id=2, amount=100)
    conn.commit()
except Exception:
    conn.rollback()
    raise

Here, both withdraw and deposit operations belong to a single transaction.

Rule:
Use a single transaction for operations that must not be partially applied. If any part fails, roll back the whole unit of work.

Examples of Transactions in Practice

Example 1: Bank Transfer

Without a transaction:

sql 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- server crash here!
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

If the server crashes between these two statements, Alice loses money but Bob does not gain it.

With a transaction:

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

If the server crashes:

So you never end up with only half the transfer applied.

Example 2: Creating an Order

Steps:

  1. Insert an order.
  2. Insert order items.
  3. Update stock.
  4. Record payment.

All must succeed together.

sql 
BEGIN;
INSERT INTO orders (user_id, total_price)
VALUES (42, 199.99)
RETURNING id;
-- Suppose the returned id is 100
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
  (100, 10, 1, 99.99),
  (100, 11, 1, 100.00);
UPDATE products
SET stock = stock - 1
WHERE id IN (10, 11);
-- If everything is ok
COMMIT;

If one of the inserts fails, or stock becomes negative, you can ROLLBACK and the database will undo all changes.

Transactions and Consistency

The main goal of a transaction is consistency of your data.

Consistency means your data respects all constraints and business rules.

Examples of inconsistencies that transactions help avoid:

Common patterns:

sql 
  BEGIN;
  SELECT balance FROM accounts
  WHERE id = 1
  FOR UPDATE;
  -- Application checks that balance >= 100
  UPDATE accounts
  SET balance = balance - 100
  WHERE id = 1;
  COMMIT;

FOR UPDATE locks the row so another transaction cannot change it between your SELECT and UPDATE. (Specific locking details are covered more deeply in database concurrency topics, but you should know that transactions also protect you from some race conditions.)

sql 
  BEGIN;
  INSERT INTO reservations (user_id, seat_id)
  VALUES (1, 50);
  UPDATE seats
  SET reserved = TRUE
  WHERE id = 50;
  COMMIT;

If any step would violate a constraint, the database will reject the statement. You can catch the error and roll back the entire transaction.

Rule:
Use transactions to keep your data consistent across multiple related changes. Do not rely on your application alone to fix partial failures.

Nested Transactions and Savepoints

Sometimes, inside a large transaction, you want to try a risky operation, and if that part fails, you want to undo only that part, not the whole transaction.

Databases provide savepoints for this.

Basic pattern:

sql 
BEGIN;
-- Safe work here
SAVEPOINT sp1;
-- Risky part
INSERT INTO risky_table (...) VALUES (...);
-- If the risky part fails, rollback to the savepoint only
ROLLBACK TO SAVEPOINT sp1;
-- Continue with other work
COMMIT;

You can think of a savepoint as a checkpoint inside a transaction.

Some ORMs expose this as nested transactions, but technically they are savepoints inside one real transaction.

Usage examples:

Common Mistakes with Transactions

Understanding what to avoid is very important in backend development.

1. Forgetting to Commit or Rollback

If you start a transaction and never commit or rollback, the connection can remain busy, holding locks and blocking other users.

Examples:

Best practice is to use constructs that guarantee cleanup, such as try/finally in code, or context managers in Python:

python 
with db.transaction() as tx:
    do_something(tx)
    do_something_else(tx)
# Automatically commits or rolls back

2. Long-Running Transactions

If you keep a transaction open for a long time, you:

Example of a bad pattern:

python 
# Bad: transaction covers slow operations
tx = db.begin()
rows = db.query("SELECT * FROM big_table")  # Large data
for row in rows:
    # Some slow processing, maybe external API calls
    call_external_service(row)
db.execute("UPDATE report SET finished = TRUE")
tx.commit()

It is better to:

3. Mixing Application Logic and Transactions Poorly

Putting too much application logic inside a transaction, especially logic that waits on external systems, is risky.

For example:

Instead:

Summary

Transactions are a core tool for backend developers working with databases.

You will use transactions constantly in real backend applications, especially when you start building more complex workflows and integrating with ORMs in later chapters.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3039 / 24h
Impressum / Imprint
Datenschutz / Data Policy