In relational databases, SQL standards delineate four major transaction isolation levels. Among these, the stricter one is Serializable, which ensures that any concurrent executions of a set of Serializable transactions guarantee to return the same output as of running them separately in the given order. Three other levels of transaction isolation are explained based on the phenomena resulting from interactions between concurrent transactions.
Such common resultant phenomena are:
- Dirty read
In this phenomenon, transactions read data written by another concurrent transaction, which is uncommitted.
- Nonrepeatable read
Here, the transaction tends to re-read data that had been previously read to find that it had been modified by a consecutive transaction that had committed since the initial read.
- Phantom read
A transaction executes a query that returns a set of rows for a search condition and then finds that it is changed due to another transaction committed after that.
- Serialization anomaly
It is the result of committing a transaction set successfully, but the output is inconsistent with the sequence of the same transactions in order one at a time.
PostgreSQL transaction isolation
In the PostgreSQL DB, you may request any of the four basic levels of transaction isolation, but only three distinct levels of isolation are implemented internally. The Read-Uncommitted mode of PostgreSQL also behaves the same as Read Committed. It is because this is the only sensible way to map the standard isolation levels in the concurrency control model.
It should also be known that the Repeatable Read PostgreSQL’s implementation may not allow any phantom reads. In the SQL standard, isolation levels may only define which phenomena should not happen instead of which happen. In order to set isolation levels of given transactions, the command is SET TRANSACTION.
Some of the data types in PostgreSQL also have some special rules related to transactional behaviors. In general, the changes made to various sequences become visible immediately to other transactions and will not roll back unless the transactions made the changes get aborted.
Read Committed isolation
As explained by RemoteDBA.com, in PostgreSQL, Read Committed can be considered as the default isolation level. When this isolation is used by a transaction, the SELECT query may only see the committed data before the query executes. It will not see any uncommitted data or changes during the execution of the query by other simultaneous transactions. So, as a result, the SELECT query may take a quick snapshot of the DB as the query starts to run. But SELECT can see the effects of any previous updates already executed within the transaction even if those are not committed yet. It is also a fact that two SELECT commands successively may see a different set of data even if those are within the same transaction.
Other PostgreSQL commands like DELETE, UPDATE, FOR UPDATE, and SELECT FOR SHARE also behave the same as SELECT, as we discussed above while searching for data rows. These will see only the target rows which were committed at the time when the command starts. In many cases, the target row may have been updated already by another transaction by the time the command finds it. In such cases, would-be updater may wait for the transaction to commit the update or rollback if it is still progressing.
Subsequently, if the first updater rolls back, its effects negate, and the second updater may proceed with updating the row found. On the other hand, if the first updater is committed, then the second updater may apply the operation to the updated row or ignore it if the target row got deleted with the first updater. The WHERE command may be re-evaluated to check whether the updated row may still match the given search condition. If it is so, then the second updater may proceed with its operation by using the updated version. In the case of SELECT FOR SHARE or SELECT FOR UPDATE, it means that the updated version of the row is locked and returned to the user.
The INSERT clause with ON CONFLICT DO UPDATE may also behave in a similar way. In Read-Committed mode, each proposed row for insertion can either update or insert data. Unless there are any unrelated errors, either one of the outcomes will occur. If there is a conflict in which is not visible to INSERT, then the UPDATE clause may affect the target row, even no version of that row will be visible to it. Another clause is INSERTING with ON CONFLICT DO NOTHING, which also acts in the same way, but never update the row.
Based on the above-mentioned rules, it is possible for the update command to see an inconsistent snapshot but can see the effects of the concurrent update commands, which the same rows are trying to update. This further makes the Read-Committed mode unsuitable for the commands which involve any complex conditions. However, usage with more complexity may sometimes produce some undesirable results too in the Read-committed mode. Say, for example, if the DELETE command operates on data that is added as well as removed from its restriction criteria.
As the Read-Committed mode may run each command based on its snapshot view of all the committed transactions up to that point, subsequent commands may see the effects of concurrent transactions. The key point to be noted in the above case is whether a single command may see a consistent database view.
In many cases, partial transaction isolation through the Read Committed mode may be enough, which is faster to use. But this may not be enough in all cases. Applications that have more complex queries may require a more consistent database view than simple Read-Committed mode transactions.
Similar to SELECT, DELETE, UPDATE, SELECT FOR SHARE, and SELECT FOR UPDATE commands also may behave the same way while searching for the target rows. However, there is a chance that such target rows may have already been updated or deleted by concurrent transactions. If that is the case, then repeatable read transactions may wait for the update to roll back or commit the transaction. But, if the updater commits, then subsequent repeatable read transaction may roll back with an error message.
Author bio
Walter Moore is a notable management consultant and digital marketing expert at blastup. He is an experienced digital marketer who has helped e-commerce businesses in all niches gain with his effective marketing strategies and guidance.
