Question 1

What is the default transaction isolation level in PostgreSQL when a client connects without changing any settings?

Accepted Answer

Correct answer: READ COMMITTED. PostgreSQL's default isolation level is READ COMMITTED. Each statement in a transaction gets its own fresh snapshot as of that statement's start, unlike REPEATABLE READ or SERIALIZABLE, which snapshot once at transaction start.

Question 2

A single UPDATE statement is executed with no surrounding BEGIN/COMMIT. What happens in PostgreSQL?

Accepted Answer

Correct answer: It runs and commits as its own implicit transaction. Outside of an explicit BEGIN...COMMIT block, PostgreSQL wraps every individual statement in its own implicit transaction, so it commits immediately once it succeeds.

Question 3

Inside an explicit BEGIN block, several UPDATE statements are executed, and then ROLLBACK is issued (no SAVEPOINTs used). What is the result?

Accepted Answer

Correct answer: All changes made since BEGIN are discarded. Without SAVEPOINTs to control the granularity, ROLLBACK undoes every change made since BEGIN, restoring the database to the state it was in before the transaction started.

Question 4

In pg_stat_activity, a session shows state = 'idle in transaction'. What does this mean?

Accepted Answer

Correct answer: A transaction is open, but the client isn't sending a query. 'idle in transaction' means the client opened a transaction (e.g. via BEGIN) and is currently not sending any query — the transaction is still open, holding whatever snapshot and locks it acquired.

Question 5

In a fresh session, with no prior configuration changes, the following is run: What does it print?

Accepted Answer

Correct answer: read committed. Verified by running SHOW transaction_isolation; in a fresh session against PostgreSQL 18: it prints read committed, the server's default isolation level.

Question 6

A developer runs a single INSERT statement (no BEGIN) that violates a NOT NULL constraint and fails. They then run a SELECT to check whether any row was inserted. What do they find?

Accepted Answer

Correct answer: No row was inserted; the failed statement's transaction rolled back. Since the standalone statement runs in its own implicit transaction, a constraint violation aborts that transaction as a whole, so nothing from it is ever committed.

Database Pg Transactions Locking Interview Questions

Sample questions

By specialization

By seniority

Other fields