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.


    Let's create 4 connections to the database and open in each of the connections a transaction with an isolation level of Read Committed

    Read committed


    Read committed


    Step 1. At the initial moment of time before any data changes, the initial version of the data (v1) is available to all transactions;

    Read committed


    Step 2. During the work of the First transaction, the data without any locks is successfully updated to the "second version" (v2);

    Read committed


    Step 3. Changes made in the First transaction will be visible only to herself (SELECT returns v2), and will not be available to other transactions (SELECT request in the Second and Fourth transactions returns v1);

    Read committed


    Step 4. Closing the First Transaction. All changes made during her work are successfully recorded;

    Read committed


    Step 5. After closing the First transaction (previous step), the changes made during its execution on the data (upgrade from v1 to v2) were distributed to the rest of the transactions, the SELECT query in the remaining 3 open transactions returns v2 (“Repeatable reading”, distinguish the level isolation of Read Committed from Serializable);

    Read committed


    Step 6. The request to update the data in the Second transaction to the "third version" is successfully executed, but the requests for updating the data block mutable rows for their further change until the Second transaction is completed;

    Read committed


    Step 7. Due to the lock imposed on the data in the previous step, the Third transaction goes into standby mode with a request to delete data. Waiting for the Third transaction will occur before the closing of the Second transaction;

    Read committed


    Step 8. Despite the fact that the Third transaction is waiting for the Second to close, both the Second and the Fourth transactions continue to work without any problems, returning data according to their versions. The second returns v3, the fourth returns v2;

    Read committed


    Step 9. Closing the Second Transaction unlocks the data for change. The Read Committed isolation level allows the Third Transaction to continue without causing an error. After gaining access to change the new version of the data (v3), the Third transaction SUCCESSFULLY "deletes" them immediately (the difference between Read Committed and Serializable);

    Read committed


    Step 10. Prior to closing the Third transaction, the data will be deleted only inside the Third transaction. The fourth transaction before closing the Third data is available (SELECT query in the Fourth transaction returns v3);

    Read committed


    Step 11. Closing the Third Transaction. All changes made during her work are successfully recorded;

    Read committed


    Step 12. The request for data in the Fourth transaction returns nothing ("Phantom read", SELECT request returns 0 records).

    Read committed


    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.


    Let's create 4 connections to the database and open transactions in each connection with isolation level Serializable

    Serializable


    Serializable


    Step 1. The initial version of the data (v1) is available to all transactions;

    Serializable


    Step 2. During the work of the First transaction, the data without any locks is successfully updated to the "second version" (v2);

    Serializable


    Step 3. Changes made in the First transaction will be visible only to herself (SELECT returns v2), and will not be available to other transactions (SELECT request in the Second and Fourth transactions returns v1);

    Serializable


    Step 4. A request to update data in the first transaction (step 2), blocks the updated rows, and puts the Second transaction with a request to delete data into standby mode. Transaction blocking on updated data will occur until the First Transaction is closed;

    Serializable


    Step 5. Despite the fact that the Second transaction is waiting for the First to close, both the Third and the Fourth transactions continue to work without any problems, returning data according to their versions;

    Serializable


    Step 6. Completion of the First transaction removes the lock from the updated data, but within the isolation level of Serializable, repeated updating of data in parallel transactions is prohibited, and therefore an error occurs during the execution of the Second transaction (difference between Serializable and Read Committed);

    Serializable


    Step 7. The SELECT query in the Second transaction becomes impossible, because the error that occurred in the previous step cancels ("blocks") the transaction. The SELECT query in Third and Fourth transactions returns the original version of the data (v1). Despite the fact that the First transaction was completed successfully, the changes did not become visible to other open transactions (the difference between Serializable and Read Committed). Opening of the Fifth transaction in the upper left window;

    Serializable


    Step 8. Closing the Second Transaction. All changes made by this transaction will be canceled due to an error during its operation;

    Serializable


    Step 9. The SELECT query in the Fifth transaction returns a new version of the data (v2). The SELECT query in the Third and Fourth transactions returns the original version of the data (v1);

    Serializable


    Step 10. The isolation level of Serializable still prevents updating data, the UPDATE query in the Third transaction does not complete successfully, with the ensuing consequences for the progress of the entire transaction (despite the fact that the First transaction has already completed successfully, and all changes made to it are saved in the database ) But the UPDATE query in the Fifth transaction completes successfully, since it is open after the completion of the First transaction, and works with the new version of the data;

    Serializable


    Step 11. Closing the Third Transaction. All changes made by this transaction will be canceled due to an error during its operation;

    Serializable


    Step 12. Transaction Four also shows that transactions with SELECT queries have no problems, and the Fifth transaction receives data already updated by itself (v5).

    Serializable


    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.

    Here is the result in PostgreSQL 9.6

    Serialization Anomaly, Lost Updates


    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.

    What is the isolation level in your current main project?


    Also popular now: