KAHIBARO
Discord Login Register ☰
☰

11.9. Transactions

Table of Contents

Why Transactions Matter in PostgreSQL

A transaction is a group of one or more SQL statements that are treated as a single unit of work. Either all of them succeed, or none of them have any effect.

In PostgreSQL, transactions protect your data from partial updates, race conditions, and many kinds of errors. They are also the foundation for the ACID properties that you will see in the ACID chapter.

Imagine you are transferring money between two accounts:

sql 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- debit
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- credit

If the first statement succeeds but the second fails, your database would be in an inconsistent state. A transaction ensures that either both updates are applied or both are rolled back.

Important:
A transaction groups multiple SQL statements and guarantees that they are applied all or nothing.

In PostgreSQL, every SQL statement runs inside a transaction. If you do not explicitly start one, PostgreSQL uses an implicit transaction for each statement.

Basic Transaction Control: BEGIN, COMMIT, ROLLBACK

PostgreSQL uses three main commands to control transactions:

CommandPurpose
BEGINStart a new explicit transaction
COMMITSave all changes in the transaction
ROLLBACKCancel the transaction, undo changes

Explicit Transaction Example

Here is how you wrap multiple statements in a single transaction:

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

If something goes wrong before COMMIT, you can undo everything:

sql 
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
/* Suppose you discover a mistake or get an error */
ROLLBACK;  -- balance change for id = 1 is undone

Rule:
Use COMMIT to make all changes permanent, use ROLLBACK to undo all changes made since the last BEGIN.

Auto-commit vs Manual Transactions

Interactive tools like psql often run in auto-commit mode:

Example in psql:

sql 
-- Auto-commit mode
UPDATE products SET stock = stock - 1 WHERE id = 10;  -- committed immediately
-- Explicit transaction
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'paid' WHERE id = 123;
COMMIT;  -- both updates are committed together

Transaction States

During its life, a transaction goes through several states:

StateDescription
idleNo transaction is active
activeA transaction is open and running statements
in transactionWaiting for more statements
failed (aborted)An error happened, must be rolled back

PostgreSQL tracks the state of a transaction on each database connection.

From Idle to Committed

Typical flow:

  1. Idle
    No transaction is active.
  2. BEGIN
    Transaction starts, state becomes active.
  3. Run statements
    Inserts, updates, deletes, selects.
  4. COMMIT
    Changes are written permanently, state returns to idle.

Example:

sql 
-- idle
BEGIN;  -- now active
INSERT INTO logs (message) VALUES ('Started task');
UPDATE tasks SET status = 'running' WHERE id = 5;
COMMIT;  -- back to idle

Error Inside a Transaction

If an error occurs inside a transaction, PostgreSQL puts that transaction into a failed state. You cannot run more statements in that transaction. You must roll back.

Example:

sql 
BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 100);
-- This fails if an account with id 1 already exists
INSERT INTO accounts (id, balance) VALUES (1, 200);
-- At this point the transaction is in a failed state
-- Any further statement will fail until you ROLLBACK
ROLLBACK;

In psql, you will see:

text 
ERROR:  duplicate key value violates unique constraint "accounts_pkey"
STATEMENT:  INSERT INTO accounts (id, balance) VALUES (1, 200);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Rule:
After an error inside an explicit transaction, you must run ROLLBACK to clear the failed state.

Transaction Isolation Levels in PostgreSQL

Transactions run at different isolation levels, which control how much a transaction can see changes made by other concurrent transactions.

PostgreSQL supports these standard isolation levels:

Isolation LevelCommon Problems Prevented
READ COMMITTEDPrevents dirty reads
REPEATABLE READPrevents dirty reads and non-repeatable reads
SERIALIZABLEPrevents all standard anomalies, strongest level

PostgreSQL does not implement READ UNCOMMITTED differently from READ COMMITTED. They behave the same.

Rule:
If you do not change it, PostgreSQL uses READ COMMITTED as the default isolation level.

READ COMMITTED

This is the default. Each statement in a transaction sees a snapshot of the database at the start of that statement, not at the start of the transaction.

Example of behavior:

Transaction A:

sql 
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- returns 100
-- waits...
SELECT balance FROM accounts WHERE id = 1;  -- may see a different value
COMMIT;

Transaction B, running in parallel:

sql 
BEGIN;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

Timeline:

  1. A runs the first SELECT, sees 100.
  2. B updates balance to 150 and commits.
  3. A runs the second SELECT, now sees 150.

So, within the same transaction A can see changes committed by B between statements.

This allows non-repeatable reads (data changes between two reads in the same transaction).

REPEATABLE READ

At this level, each transaction sees a consistent snapshot of the database that does not change during that transaction.

Transactions in REPEATABLE READ:

Example:

Transaction A:

sql 
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- returns 100
-- waits...
SELECT balance FROM accounts WHERE id = 1;  -- still returns 100
COMMIT;

Transaction B, in parallel:

sql 
BEGIN;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

Even though B commits the change, A keeps seeing the old value 100 for the whole duration of the transaction.

SERIALIZABLE

This is the strictest level. PostgreSQL will ensure that the outcome is as if transactions had run one by one in some serial order, even though they actually run concurrently.

In SERIALIZABLE:

Example:

sql 
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM some_table WHERE ...;
-- do some calculations in your application
UPDATE some_table SET ... WHERE ...;
COMMIT;

If PostgreSQL detects that concurrent changes would violate serializable behavior, the COMMIT may fail with a serialization error. Your application should catch this and retry the transaction.

MVCC: How PostgreSQL Handles Concurrency

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to support many concurrent transactions without heavy locking.

High-level idea:

You do not need to manage MVCC directly when you write SQL, but it explains why:

Example of concurrent behavior:

Transaction A:

sql 
BEGIN;
SELECT stock FROM products WHERE id = 1;  -- sees 10
-- some time passes...
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

Transaction B:

sql 
BEGIN;
UPDATE products SET price = price * 0.9 WHERE id = 1;
COMMIT;

With MVCC, these two updates can usually run concurrently without blocking each other, because they are updating different columns. PostgreSQL merges the changes into a new row version.

Practical Transaction Patterns

Here are some common and useful patterns when working with transactions in PostgreSQL.

Pattern 1: Grouping Logical Work

Group related changes so they succeed or fail together:

sql 
BEGIN;
INSERT INTO orders (user_id, total_amount)
VALUES (42, 199.99)
RETURNING id;
-- suppose it returns id = 100
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES
  (100, 1, 1, 99.99),
  (100, 2, 2, 50.00);
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 1;
UPDATE inventory
SET stock = stock - 2
WHERE product_id = 2;
COMMIT;

If any insert or update fails, you call ROLLBACK and the database is as if nothing happened.

Pattern 2: Explicit Rollback on Validation Failure

You might detect a problem in your application logic and choose to roll back even if no SQL error occurred:

sql 
BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE id = 1
RETURNING balance;
-- Suppose application reads returned balance and sees it went below 0
ROLLBACK;  -- cancel the debit because business rule forbids negative balance

Pattern 3: Transaction with Isolation Level

You can set isolation level for a single transaction:

sql 
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM reports WHERE year = 2024;
/* some processing */
UPDATE reports SET total = 12345 WHERE year = 2024;
COMMIT;

Or change it for the current session:

sql 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- or:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Using Transactions from Application Code

Most backend applications do not send BEGIN and COMMIT manually as plain SQL. Instead, they use higher-level tools such as:

The idea is always the same:

  1. Obtain a database connection or session.
  2. Start a transaction.
  3. Run several SQL statements.
  4. Commit if all succeed, or roll back if something fails.

Example: Python with psycopg (conceptual)

python 
import psycopg
conn = psycopg.connect("dbname=mydb user=myuser password=secret")
try:
    with conn:
        with conn.cursor() as cur:
            cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))
            cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))
    # exiting the `with conn` block commits automatically if no error
except Exception:
    # if an error happens inside, psycopg rolls back automatically
    raise

In many libraries:

This pattern is common in ORMs too. You will see more details in the ORM and Database Integration chapters.

Common Transaction Pitfalls

Even with transactions, it is easy to make mistakes. Here are some that beginners often encounter.

Forgetting to Commit

If you start a transaction and forget to commit:

Example problem:

sql 
BEGIN;
UPDATE large_table SET status = 'processed' WHERE ...;
-- you leave the session open for a long time, forget to COMMIT

This can cause table bloat and block VACUUM.

Ignoring Serialization Failures

At SERIALIZABLE isolation level, you must be prepared to retry on specific errors. Otherwise, some operations will just fail unexpectedly under load.

Typical pattern in application code:

  1. Start a transaction.
  2. Try to complete work.
  3. If you get a serialization error, rollback and retry a few times.

Overusing High Isolation Levels

SERIALIZABLE gives very strong guarantees but can reduce concurrency and cause more aborts. For many web applications:

Rule:
Use the lowest isolation level that still gives correct business behavior, and handle transaction errors gracefully.

Summary

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

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