Transactions and isolation levels

Explain the isolation levels, and what each one lets through.

Read uncommitted allows dirty reads — you can see another transaction's uncommitted work. Read committed stops that, but the same query twice can return different rows. Repeatable read pins the rows you have read, and depending on the engine still allows phantoms: new rows matching your predicate. Serializable behaves as if transactions ran one after another. Every level up costs concurrency, in locks or in aborted transactions.

Know your default. Postgres is read committed, and its repeatable read is snapshot isolation, which does prevent phantoms — this is where interviewers separate reading from experience.

What are the ACID properties, in your own words?

Atomicity — all of the transaction happens or none of it does. Consistency — it moves the database from one state that satisfies your constraints to another. Isolation — concurrent transactions do not see each other's half-finished work. Durability — once it is committed it survives the machine losing power. Atomicity and durability both come from the write-ahead log; isolation comes from locks or from multi-version snapshots.

Two transactions deadlock. What happened, and what do you do?

Each holds a lock the other needs, so neither can proceed. The database detects the cycle and kills one, returning an error to that client. The fix is rarely at the database: acquire locks in a consistent order everywhere, keep transactions short so the window is small, and make the caller retry — a deadlock is a transient failure and retrying almost always succeeds.