What are transactions and isolation levels in PostgreSQL?

A transaction in PostgreSQL is a sequence of operations on a database that is executed as a single unit. Either all operations within the transaction are successfully applied (commit), or in case of an error, they are rolled back (rollback), ensuring data integrity.

Transaction isolation levels determine how much a transaction can see the changes made by other concurrent transactions. PostgreSQL supports the following isolation levels:

• Read Uncommitted (effectively works as Read Committed) — a transaction can only see committed changes.
• Read Committed (default level) — each statement within a transaction sees data committed at the start of that statement.
• Repeatable Read — a transaction sees data as it was at the start of the transaction, preventing non-repeatable reads.
• Serializable — the strictest level, providing full isolation as if transactions were executed sequentially.

Example of using a transaction in Python with psycopg2:

import psycopg2

conn = psycopg2.connect(...)
try:
    with conn:
        with conn.cursor() as cur:
            cur.execute("INSERT INTO users(name) VALUES(%s)", ('Alice',))
            cur.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = %s", (user_id,))
except Exception as e:
    print(f"Transaction error: {e}")