How do you work with transactions in PostgreSQL? What isolation levels do you use?

In PostgreSQL, transactions are used to ensure atomicity and consistency of database operations. In Python, working with PostgreSQL is usually done through libraries such as psycopg2 or asyncpg, where transactions can be explicitly controlled using methods like begin(), commit(), rollback(), or through context managers.

Regarding transaction isolation levels, PostgreSQL supports four SQL standards:

• Read Uncommitted (effectively works as Read Committed)
• Read Committed (default level)
• Repeatable Read
• Serializable

In most cases, the Read Committed level is used, which ensures that a transaction only sees committed data, preventing dirty reads. For tasks requiring stricter consistency and preventing phantom reads, Repeatable Read or Serializable are used.

Example of using a transaction in psycopg2:

import psycopg2

conn = psycopg2.connect(...)
try:
    with conn:
        with conn.cursor() as cur:
            cur.execute("INSERT INTO users(name) VALUES(%s)", ('Alice',))
            # other operations
except Exception as e:
    print(f"Transaction error: {e}")