PostgreSQL example transaction isolation levels
Introduction
The SQL standard describes four levels of transaction isolation - Read uncommited, Read committed, Repeatable read, and Serializable. This article will look at the life cycle of four concurrent transactions with isolation levels of Read committed and Serializable .
The following special conditions for reading data are acceptable for the Read committed isolation level:
Non-repeatable reading - the transaction re-reads the same data as before, and discovers that it was modified by another transaction (which ended after the first read).
Phantom read - a transaction repeatedly executes a query that returns a set of rows for a certain condition, and detects that the set of rows satisfying the condition has changed due to a transaction that has completed during this time.
As for Serializable, this isolation level is the most stringent, and does not have data reading phenomena.
ACID or 4 transaction properties
Before we begin to consider the isolation levels of a transaction in a couple of words, let us recall the basic requirements for a transaction system.
Atomicity (atomicity) - is expressed in the fact that the transaction must be completed as a whole or not completed at all.
Consistency - ensures that as transactions are completed, data goes from one consistent state to another, that is, a transaction cannot destroy the mutual consistency of data.
Isolation - localization of user processes means that transactions competing for access to the database are physically processed sequentially, in isolation from each other, but for users it looks like they are being executed in parallel.
Durability - error tolerance - if the transaction is completed successfully, then those changes in the data that it made cannot be lost under any circumstances.
Read Committed isolation level
По умолчанию в PostgreSQL уровень изоляции Read Committed. Такой уровень изоляции всегда позволяет видеть изменения внесённые успешно завершёнными транзакциями в оставшихся параллельно открытых транзакциях. В транзакции, работающей на этом уровне, запрос SELECT (без предложения FOR UPDATE/SHARE) видит только те данные, которые были зафиксированы до начала запроса; он никогда не увидит незафиксированных данных или изменений, внесённых в процессе выполнения запроса параллельными транзакциями. По сути запрос SELECT видит снимок базы данных в момент начала выполнения запроса. Однако SELECT видит результаты изменений, внесённых ранее в этой же транзакции, даже если они ещё не зафиксированы. Также заметьте, что два последовательных оператора SELECT могут видеть разные данные даже в рамках одной транзакции, если какие-то другие транзакции зафиксируют изменения после выполнения первого SELECT.
The essence of the Read Committed isolation level is shown in Figure 1.
Note: The table already contains a record with the first version of the data (v1). Please accept the SELECT v1 commands; - as a command that returns data of version 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 effect of an INSERT query. Within this isolation level, the rows added, for example in step 3, in the First transaction, would be visible to other transactions after the completion of the First transaction.
The 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 cases. Applications that perform complex queries and changes may require a more consistent presentation of data, such as Serializable.
Serializable Isolation Level
Serializable isolation provides unhindered transaction database access with SELECT queries. But for transactions with UPDATE and DELETE queries, the isolation level of Serializable does not allow modification of the same row within different transactions. With isolation at this level, all transactions are processed as if they were all started sequentially (one after another). If two simultaneous transactions try to update the same row, then this 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 Figure 2.
Note. The diagram does not show the effect of an INSERT query. Within this isolation level, the rows added, for example in step 3, in the First transaction would NOT be AVAILABLE to the Second, Third and Fourth transactions after the completion of the First transaction. Also, the result of ROLLBACK is not shown in the diagram (Steps 8 and 11). If the Second and Third transactions made any changes over the non-locked data, then all these changes would not be committed, as the transactions fail (the essence of the property is Atomicity).
The isolation level of Serializable ensures that all data affected by a transaction is not modified by other transactions. At this level, the appearance of “phantoms” is excluded, so 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 isolation level of Serializable justifies itself when you do not want to see the changes made by parallel completed transactions during the operation of the current transaction.
Serialization Anomaly (Lost Update)
Another phenomenon of reading data is described by the fact that the result of successful fixing of a group of transactions is inconsistent with all kinds of options for executing these transactions in turn.
Please orient me in the comments if I am mistaken about the fact that the serialization anomaly and the lost update are related phenomena.
The documentation on the PostgreSQL PRO website says that Read Committed allows for "Serialization Anomaly . " Domestic Wikipedia, not insisting that the table refers specifically to PostgreSQL, writes that Read Commited prevents the serialization anomaly . The English Wikipedia is silent about this phenomenon of reading data . But German Wikipedia cites the “Lost Updates” phenomenon in its version of the table , indicating that Read Committed may not be subject to loss of updates with additional protection through the cursor (Cursor Stability). Ukrainian Wikipedia supports the Russian version of the article, Spanish Wikipedia supports the English version of the article. PostgreSQL English documentation is no different from the documentation from the PostgreSQL PRO website.
Cursor Stability extends the locking behavior of the READ COMMITED level for SQL cursors by adding a new read operation (Fetch) to the rc cursor (meaning read cursor, i.e. reading from the cursor) and requiring that the lock be set on the current cursor element. The lock is held until the cursor is moved (until its current element has changed) or closed, possibly by a commit operation. Naturally, a transaction reading by the cursor can change the current line (wc - write by the cursor), in which case the write lock of this line will remain until the transaction is committed, even after moving the cursor and then fetching the next line.
Total: 149.
Conclusion
Understanding transaction isolation levels is an important aspect when processing data in any multi-user DBMS. Isolation levels have well-defined characteristics and behavior. Higher isolation levels reduce parallel processing capabilities and increase the risk of process deadlocks. 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 logical data integrity, speed and the possibility of parallel multi-user processing.
Literature
" MVCC Unmasked
" 13.2. Transaction Isolation
» Critique of Isolation Levels
» Transaction isolation levels in SQL. Cheat sheet
» Transactional isolation level
» ... the lost update phenomena
Only registered users can participate in the survey. Please come in.