Transaction Isolation Levels with PostgreSQL Examples

Introduction

The SQL standard describes four transaction isolation levels — Read uncommited, Read committed, Repeatable read, and Serializable. This article will look at the life cycle of four concurrently running transactions with Read committed and Serializable isolation levels.

For the Read committed isolation level, the following special data reading conditions are allowed:

Non-Repeatable Read — A transaction rereads the same data as before and finds that it was modified by another transaction (which ended after the first read).

Phantom read — A transaction re-executes a query that returns a rowset for a condition, and detects that the rowset that satisfies the condition has changed because of a transaction that has completed during this time.

As for Serializable, this isolation level is the strictest and has no data reading phenomena.

ACID or 4 transaction properties

Before we begin to consider the isolation levels of a transaction in a couple of words, we’ll recall the basic requirements for a transaction system.

Atomicity - is expressed in the fact that the transaction must be completed as a whole or not executed at all.

Consistency - ensures that as transactions are completed, data moves from one consistent state to another, that is, a transaction cannot destroy the mutual consistency of data.

Isolation - localization of user processes means that competing for access to the database transactions are physically processed sequentially, in isolation from each other, but for users it looks as if they are executed in parallel.

Durability - error tolerance - if a transaction is completed successfully, then the changes in the data that it has made cannot be lost under any circumstances.

Isolation level Read Committed

PostgreSQL's default isolation level is Read Committed. This isolation level always allows you to see the changes made by successfully completed transactions in the remaining open transactions in parallel. In a transaction running at this level, the SELECT query (without the FOR UPDATE / SHARE clause) sees only the data that was recorded before the query started; he will never see uncommitted data or changes made during the execution of a query by parallel transactions. Essentially, a SELECT query sees a snapshot of the database at the time the query starts. However, SELECT sees the results of changes made earlier in the same transaction, even if they are not yet fixed. Also note that two consecutive SELECT statements can see different data even within the same transaction if some other transactions commit changes after the first SELECT has been executed.

The essence of the Read Committed isolation level is shown in diagram 1.

Note: The table already contains an entry with the first version of the data (v1). Please take the command SELECT v1; - as a command that returns data versions v1, and UPDATE v1 to v2; - as a command to update data from the first version to the second.

Note. The diagram does not show the action of the INSERT query. Within this isolation level, the rows added, for example in step 3, in the First transaction, would be SEEED to the remaining transactions after the completion of the First transaction.

Partial transaction isolation, provided in Read Committed mode, is acceptable for many applications. This mode is quick and easy to use, but it is not suitable for all occasions. Applications that perform complex queries and changes may require a more strictly consistent presentation of data, such as Serializable.

Isolation level Serializable

The isolation level of Serializable provides unhindered access to the database of transactions with SELECT queries. But for transactions with UPDATE and DELETE requests, the isolation level Serializable does not allow modification of the same row within different transactions. When isolating such a level, all transactions are processed as if they are all running sequentially (one after another). If two simultaneous transactions try to update one and the same line, it will not be possible. In this case, PostgreSQL will force the transaction, the second, and all subsequent ones that tried to change the line to cancel (rollback - ROLLBACK).

The essence of the Serializable isolation level is shown in diagram 2.

Note. The diagram does not show the action of the INSERT query. Within this isolation level, the rows added, for example in step 3, in the First transaction, would be NOT AVAILABLE for the Second, Third and Fourth transactions after the completion of the First transaction. Also, the diagram does not show the result of ROLLBACK (Steps 8 and 11). If the Second and Third transactions made any changes to the unlocked data, then all these changes would not be recorded, since the transactions fail (the essence of the property is Atomicity).

The isolation level of Serializable ensures that all data affected by the transaction will not be altered by other transactions. At this level, the emergence of "phantoms" is excluded, therefore complex competitive operations become possible. In practice, this level of isolation is required in accounting systems.

For transactions containing only SELECT queries, the use of the Serializable isolation level is justified when you do not want to see the changes made in parallel with completed transactions during the course of the current transaction.

Serialization Anomaly (Lost Update)

Another phenomenon of reading data is described by the fact that the result of a successful fixation of a group of transactions is inconsistent with all sorts of options for executing these transactions in turn.

Orient me, please, in the comments, if I am mistaken about the fact that the anomaly of serialization and the lost update related phenomena.

The documentation on the PostgreSQL PRO website writes that Read Committed allows for “Serialization Anomaly” . Domestic Wikipedia, without insisting that the table refers specifically to PostgreSQL, writes that Read Commited prevents serialization anomaly . English Wikipedia about such a phenomenon of reading data is silent . But German Wikipedia cites the “Lost Updates” phenomenon in its version of the table, indicating that Read Committed may not be susceptible to the loss of updates with Cursor Stability. Ukrainian Wikipedia supports the Russian version of the article; Spanish Wikipedia supports the English version of the article. The English-language PostgreSQL documentation is no different from the documentation from the PostgreSQL PRO site.

Cursor Stability extends the blocking behavior of the READ COMMITED level for SQL cursors by adding a new read operation (Fetch) on the rc cursor (meaning read cursor, that is, reading on the cursor) and requiring the lock to be set on the current cursor element. The lock is held until the cursor is moved (until its current element is changed) or closed, possibly by a commit operation. Naturally, a transaction reading on the cursor can change the current line (wc is a record on the cursor), in which case the lock on the record of this line will be maintained until the transaction is committed, even after moving the cursor, followed by selecting the next line.

Conclusion

Understanding transaction isolation levels is an important aspect of data processing in any multi-user DBMS. Insulation levels have well-defined characteristics and behavior. Higher isolation levels reduce parallel processing and increase the risk of interlocking processes. Therefore, the correct use of levels depending on the tasks of the applications is always the choice of the developer depending on the requirements for ensuring the logical integrity of the data, speed and the possibility of parallel multi-user processing.

Literature

» MVCC Unmasked

" 13.2. Transaction isolation

» Criticism of isolation levels

» Transaction isolation levels in SQL. Crib

» Transaction Isolation Level

" ... the lost update phenomena