What is a transaction, what levels of isolation do you know? What anomalies can occur? How does Serializable work in PostgreSQL?

A transaction is a sequence of operations with a database that is executed as a single unit: either all operations complete successfully, or none are applied (atomicity).

Transaction isolation levels (ANSI SQL):

• Read Uncommitted — reading even uncommitted changes (dirty read).
• Read Committed — reading only committed data.
• Repeatable Read — guarantees that re-reading the same data within a transaction yields the same result.
• Serializable — the strictest isolation, transactions are executed as if sequential.

Anomalies that can occur:

• Dirty Read: reading uncommitted data from another transaction.
• Non-Repeatable Read: data changes between two reads within the same transaction.
• Phantom Read: new rows appear that match the condition upon re-query.

Serializable in PostgreSQL: PostgreSQL implements the Serializable level using MVCC and conflict detection mechanisms. Transactions run in parallel, but upon conflict detection, one is rolled back with a serialization error. This ensures the effect of sequential transaction execution without locks, but requires handling transaction retry errors in the application.