The transaction isolation levels with examples for PostgreSQL

Introduction

In the SQL standard describes four transaction isolation level is uncommited Read (Read uncommitted), Read committed (Read committed), Repeatable read (Repeatable read) Serializable, and (Serializability). In this article we will consider the life cycle of four concurrently running transactions isolation levels Read committed and Serializable.

For Read committed isolation level permissible the following special conditions data read:

non-repeatable read a transaction re-reads the same data as before, and detects that they were modified by another transaction (which ended after the first reading).

Phantom read a transaction re-executes a query returning a set of rows for some condition, and finds that the set of rows satisfying the condition has changed due to transactions that resulted during this time.

as for Serializable, the isolation level is the most strict, and has no phenomena of reading data.

ACID or 4 transaction properties

Before we proceed to the consideration of the transaction isolation levels in a couple of words remember about the basic requirements for a transactional system.

Atomicity (atomicity) is reflected in the fact that the transaction needs to be performed in whole or not executed at all.

Consistency (consistency) ensures that, upon completion of the transaction, data is transferred from one consistent state to another, i.e. transaction cannot destroy the mutual consistency of the data.

Isolation (isolation) — localization of the user processes means that competing for access to the database transaction physically processed sequentially, in isolation from each other, but for users it looks like they run in parallel.

Durability (durability) — error tolerance — if a transaction completes successfully, the changes in the data that were produced, can not be lost under any circumstances.

Read Committed isolation Level

By default, PostgreSQL's Read Committed isolation level. This isolation level always allows you to see the changes of successfully completed transactions in parallel in the remaining open transactions. In the transaction operating at this level, a SELECT query (without a FOR UPDATE/SHARE) sees only data that was recorded prior to the request; he will never see uncommitted data or changes made in the process of query execution by concurrent transactions. In fact, a SELECT query sees a snapshot of the database at the beginning of the query. However SELECT can see results of changes made earlier in the same transaction, even if they are not fixed. Also note that two successive SELECT can see different data, even within a single transaction if some other transaction has committed the changes after performing the first SELECT statement.

the Essence of the Read Committed isolation level are shown in figure 1.

note: the table is already a record with the first data version (v1). Please take SELECT v1; — as a team-valued data v1, and UPDATE v1 to v2; — as a team data update from the first version to the second.

Create database-data 4 connect to and open each of the connections of the transaction with Read Committed isolation level

Step 1. In the initial time before any changes to the data all transactions available in the original version of the data (v1);

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

Step 4. The closing of the First transaction. All changes made in the course of its work successfully recorded;

Step 5. After the closing of the First transaction (the previous step), changes made in the course of its execution on the data (update from v1 to v2) was distributed to the rest of the transaction, the SELECT query in the remaining 3 open transaction returns v2 ("non-repeatable read", the difference between the Read Committed isolation level from Serializable);

Step 6. Request to update the data in the Second transaction to "third version" succeeds, but requests to update the data block affected rows on a potential change to the completion of the Second transaction;

Step 7. Due to the lock imposed on the data in the previous step, the Third transaction enters an idle mode request to delete data. Waiting for the Third transaction will occur before the closing of the Second transaction;

Step 8. Despite the fact that the Third transaction is pending the Second closing, as the Second and Fourth transaction without any problem continue its work, restoring the data according to their versions. Returns the second v3, the Fourth returns v2;

Step 9. Closing of the Second transaction leads to the unlocking of data for changes. The Read Committed isolation level allows you to continue the work of the Third transaction without throwing an error. By accessing the edit a new version (v3) of the Third transaction is SUCCESSFUL immediately "removes" (different from Read Committed to Serializable);

Step 10. Before the closing of the Third transaction, the data will be removed only within the Third transaction. The fourth transaction before the close of the Third data is available (SELECT the request in the Fourth transaction returns v3);

Step 11. The closing of the Third transaction. All changes made in the course of its work successfully recorded;

Step 12. A request to receive data in a Fourth transaction returns nothing ("Phantom read", SELECT query returns 0 records).

note. From the diagram the effect of an INSERT query. Under this isolation level, rows are added, for example in step 3, the First transaction would be VISIBLE to other transactions after the First transaction completes.

the Partial transaction isolation provided by Read Committed, 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 modifications may require more rigorously consistent view of data, such as Serializable.

Serializable isolation Level

Isolation level Serializable provides unobstructed access to the database transactional with SELECT queries. But for transactional queries UPDATE and DELETE, the Serializable isolation level does not allow modification of the same row in different transactions. In isolation this level all transactions are processed as if they were all started sequentially (one after another). If two concurrent transactions attempt to update the same row, it will not be possible. In this case, PostgreSQL will force the transaction, the second, and all subsequent ones, tried to change the line to cancel (rollback — ROLLBACK).

Create database-data 4 connect to and open each of the connection transaction isolation level Serializable

Step 1. All transactions available in the original version of the data (v1);

Step 2. In the course of the First transaction data without any locks successfully updated to "second version" (v2);

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

Step 4. Request to update the data in the first transaction (step 2), blocks of the row being updated, and takes in the standby mode, the Second transaction request to delete data. Lock transaction for updating the data will occur before the closing of the First transaction;

Step 5. Despite the fact that the Second transaction waits for the closure of the First, the Third and Fourth transactions without any problem continue its work, restoring the data according to their version;

Step 6. The completion of the First transaction releases the lock with the updated data, but under the Serializable isolation level refresh the data in concurrent transactions is prohibited, and therefore, during the execution of the Second transaction an error occurs (the difference from the Read Committed Serializable);

Step 7. The SELECT query in the Second transaction is not possible, since the error incurred in the previous step cancels ("blocks") transaction. The SELECT query in the Third and Fourth transactions return initial version of the data (v1). Despite the fact that the First transaction was completed successfully, the changes do not become visible to other open transactions (different from Serializable to Read Committed). The opening of the Fifth transaction in the upper left window;

Step 8. Closing of the Second transaction. All changes made by this transaction are cancelled due to an error in the course of its work;

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

Step 10. The Serializable isolation level, all does not update data, the UPDATE request in the Third transaction completed not successfully, with consequences for the course of the entire transaction (despite the fact that the First transaction has been successfully completed, and made all her changes saved in the database). But the UPDATE query in the Fifth transaction succeeds because it is opened after completing the First transaction, and works with the new version of the data;

Step 11. The closing of the Third transaction. All changes made by this transaction are cancelled due to an error in the course of its work;

note. From the diagram the effect of an INSERT query. Under this isolation level, rows are added, for example in step 3, in the First transaction, would NOT be AVAILABLE the Second, Third and Fourth transaction after the First transaction completes. Also the chart doesn't show the result of ROLLBACK (Steps 8 and 11). If the Second and Third transactions have made any changes over the data is not blocked, then all these changes would not be recorded as transaction fail (the essence of the properties — Atomicity).

the Serializable isolation Level ensures that all affected in the transaction data will not be changed by other transactions. At this level the appearance of "phantoms" is excluded, so more complex competitive operation. In practice, this level of isolation is required in the accounting systems.

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

serialization Anomaly (Lost update)

Another phenomenon of the read data, describes the outcome of a successful commit of groups of transactions is inconsistent with all possible variants of execution of these transactions queue.

Orient me in the comments if I'm wrong about the fact that the anomaly serialization and lost update related phenomena.

Documentation on the PostgreSQL website PRO says that Read Committed allows "Serialization Anomaly". Domestic Wikipedia, without insisting on the fact that the table refers specifically to PostgreSQL, and writes that Read Commited prevents serialization anomaly. The English Wikipedia about this phenomenon read the data umalyaet. But German Wikipedia gives in his version of the table phenomenon "Lost Updates", indicating that the Read Committed may not be susceptible to lost updates with additional protection through the cursor (Cursor Stability). Ukrainian Wikipedia supports the Russian version of the articles, the Spanish Wikipedia supports the English version of the article. English-language documentation for the PostgreSQL no different from the documentation website of PostgreSQL PRO.

extends the Cursor Stability blocking behavior of the READ COMMITED level for SQL cursors by adding a new read operation (Fetch) on the cursor, rc (meaning read cursor, i.e., the reading on the cursor), and requiring that a lock was installed on the current item of the cursor. The lock is held until the cursor is moved (not yet changed its current element) or closed, possibly by a commit operation. Of course, a transaction that reads the cursor can change the current line (wc – record at cursor), and in this case, the lock on the record on this line will be maintained as long as the transaction is not fixed, even after the movement of the cursor and then fetch the next row.

Here's the result in PostgreSQL 9.6

Result: 149.

Conclusion

Understanding transaction isolation levels is an important aspect in the processing of data in any multi-user DBMS. Isolation levels have clearly defined characteristics and behaviour. Higher isolation levels reduce the possibilities of parallel processing and increase the risk of deadlocks processes. Therefore, the correct use of levels, depending on the application tasks is always a choice of the developer depending on the requirements to ensure logical data integrity, speed and the possibility of parallel multiuser processing.

" MVCC Unmasked
"13.2. Isolation transactions
"Criticism of the isolation levels
"the transaction isolation Levels in SQL. Cheat sheet
"the transaction isolation Level
"... the lost update phenomena

Article based on information from habrahabr.ru