PostgreSQL Isolation Levels Quiz

Read Committed isolation level is the default in PostgreSQL.
SELECT queries see only data that has been committed before the query started; it never sees changes or uncommitted data.
Two SELECT commands executed within a single transaction can see different data, even though they are within the same transaction, if other transactions have committed changes after the first SELECT started and before the second SELECT started.
The search condition of a SELECT command is re-evaluated to see if the updated version of the row still matches the search condition.
In Read Committed mode, each row proposed for insertion will either insert or update.
If a conflict originates in another transaction whose effects are not yet visible to the INSERT, the UPDATE clause will affect that row, even though possibly no version of that row is conventionally visible to the command.
In Merge mode, the user can specify various combinations of INSERT, UPDATE and DELETE subcommands.
If MERGE attempts an UPDATE or DELETE and the row is concurrently updated but the join condition still passes for the current target and the current source tuple, then MERGE will behave the same as the UPDATE or DELETE commands and perform its action on the updated version of the row.
However, because MERGE can specify several actions and they can be conditional, the conditions for each action are re-evaluated on the updated version of the row, starting from the first action, even if the action that had originally matched appears later in the list of actions.
On the other hand, if the row is concurrently updated or deleted so that the join condition fails, then MERGE will evaluate the condition's NOT MATCHED actions next, and execute the first one that succeeds.
Because of the above rules, it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it does not see effects of those commands on other rows in the database.
The Repeatable Read isolation level is more stringent than Read Committed and provides a more rigorous guarantee that each transaction sees a completely stable view of the database.
This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction.
Thus, successive SELECT commands within a single transaction see the same data, i.e., they do not see changes made by other transactions that committed after their own transaction started.
Applications using this level must be prepared to retry transactions due to serialization failures.
UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time.
But such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress).
If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message ERROR: could not serialize access due to concurrent update
When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning.
Serializable transactions emulate serial transaction execution, which means that all committed transactions are executed one after the other, serially.
This level has the same behavior as Repeatable Read, except that it monitors for conditions which could make concurrent execution of serializable transactions behave inconsistently with all possible serial executions of those transactions.
If a serializable transaction is running at the Repeatable Read level and tries to commit, it will be rolled back with this message: "could not serialize access due to read/write dependencies among transactions".
Applications that rely on Serializable transactions must not depend on data being valid until the transaction which read it has successfully committed.

PostgreSQL Isolation Levels Quiz

5 Questions

What is the default isolation level in PostgreSQL?

What does the MERGE command do?

What happens when a conflict originates in another transaction whose effects are not yet visible to the INSERT?

When a serializable transaction is running at the Repeatable Read level and tries to commit, what will happen?

What must applications that rely on Serializable transactions do?

Study Notes

Make Your Own Quizzes and Flashcards

More Quizzes Like This

[04/Vienne/07]

[04/Sura/07]

PostgreSQL SELECT Statement Syntax

PostgreSQL Fundamentals

Upgrade to continue