Postgre Transaction Isolation

Https://github.com/angelfan/DayDayUp/blob/5aed5c7c43132a5bf2f2f1766dab045544c8f870/note/transaction_ Isolation.md#%e4%ba%8b%e5%8a%a1%e9%9a%94%e7%a6%bb

Transaction isolation

There are two ways to implement MVCC:

1. When writing new data, move the old data to a separate place, such as the rollback segment, when others read the data, and read the old data from the rollback segment;

2. When writing data, the old data is not deleted, but the new data is inserted.

The PostgreSQL database uses the second method, while the Oracle database and the InnoDB engine in MySQL use the first method.

Compared to the Racle database and the InnoDB engine in MySQL, the advantages and disadvantages of PostgreSQL's MVCC implementation are as follows.

Advantages:

1. Transaction rollback can be completed immediately, regardless of the number of operations performed by the transaction;

2. The data can be updated many times without the need to ensure that the rollback segments are not exhausted, as the oracle and MySQL InnoDB engines often do, and that the "ORA-1555" errors are not often encountered as Oracle databases;

Disadvantages:

1. Old version data needs to be cleaned up. PostgreSQL cleans up the old version of the command to become vacuum;

2. Older versions of data can cause queries to be slower because older versions of the data exist in the data file, and more chunks of data are scanned when queried.

(This paragraph is transferred from "the way of the practice of PostgreSQL")

Dirty Read (dirty reads)

One transaction reads the data that is written by another uncommitted parallel transaction.

Non-repeatable read (non-repeatable reads)

A transaction re-reads the previously read data and discovers that the data has been modified by another committed transaction.

Phantom Read (Phantom Read)

A transaction re-executes a query that returns a set of rows that match the query criteria and finds that the rows have changed because of other recently committed transactions.

BEGIN;     1;  --' old ' gap1--'??? '   

' New1

Read non-submitted READ uncommitted

In another transaction, as soon as the record is updated, the current transaction is read to the updated data (dirty Read)

Read submitted by the Read Committed

Another transaction must be committed and the current transaction will not read the latest data (not dirty read but repeatable read)

REPEATABLE READ REPEATABLE READ

The current transaction is not read to the new data (cannot be read repeatedly), even if another transaction is inserted, the current transaction is able to make a number change (select COUNT (*) from table) (can be read in phantom).

Serializable Serializable

Another transaction does an insert operation and the current transaction does not make a change in quantity (select COUNT (*) from table) (not phantom Read)

Isolation Level	Dirty Read	non-repeatable read	Phantom Reading
Read not submitted	possible	possible	possible
Read Committed	No way	possible	possible
REPEATABLE READ	No way	No way	possible
Serializable	No way	No way	No way

Read Committed isolation levels (BEGIN TRANSACTION isolation level read COMMITTED;)

When a transaction runs at this isolation level: a select query (without a for update/share clause) can see only data that has been committed before the query starts, cannot see uncommitted data, or data that other transactions have committed during query execution (read-only database snapshots at that time). However, select can see that the execution results are updated in the same transaction as before, even if they have not yet been committed. (Note: Two adjacent select commands in the same transaction may see different snapshots because other transactions are committed during the first select execution.) ）

Analysis

What happens if you have two transactions that modify the same row of data? This is the time for the transaction isolation level (transaction isolation levels) to debut. Postgres supports two basic models to give you control over what you should do with such a situation. Read COMMITTED is used by default, waiting for the initial transaction to complete before reading the row record and then executing the statement. If the record has been modified during the wait, it will start over again. For example, when you execute an update with a WHERE clause, the WHERE clause returns a hit record result after the initial transaction is committed, and if the condition of the WHERE clause is still satisfied, then the update will be executed. In the following example, two transactions modify the same row of records at the same time, and the initial UPDATE statement causes the where of the second transaction to not return any records, so the second transaction is not modified to any records at all

Order.transactionDoOrder.where (Ship_code:' 123'). Update_all (Ship_code:' 123456') sleep20End# executes before the first transaction finishes executing Update_allOrder.transactionDoOrder.where (Ship_code:' 123'). Update_all (Ship_code:' 1234567‘)End# Ship_code = 123456# Order.where (ship_code: ' 123 '). Update_all (ship_code: ' 1234567 ') was unable to find where (Ship_code: ' 123 ')Order.transactionDoOrder.where (Ship_code:' 123'). First.update (Ship_code:' 1234567‘)End# Ship_code = 1234567# This situation modifies the record because it executes the order.where (ship_code: ' 123 ') first.# because the default isolation level is read Committed, so this time# Order.where (ship_code: ' 123 ') you can get the data.# executed SQL statement is ## first get Order#id## UPDATE "Orders" SET "Ship_code" = 1234567 WHERE "Orders". " id "= Order#id# repeatableorder.transaction do old_ship_ Code = order.where (id: 2). Pluck ( Span class= "PL-C1" >:ship_code) sleep 10 new_ship_code =  Order.where (id: 2). Pluck (:ship_code) p [old_ship_code , New_ship_code] # data is not the same endorder.where ( id: 2). Update_all (ship_code:  123456123)                

REPEATABLE READ Isolation LEVEL

ActiveRecord::base.isolation_level (do   order.where (2). Pluck (order.where (2). Pluck ( EndEnd       

Serializable isolation Level

The serializable level provides the most stringent transaction isolation. This level is executed for all committed transactions that impersonate a serial transaction, as if the transaction were to be executed serially (rather than in parallel) one after the other. However, just as you can read the isolation level repeatedly, applications that use this level must be prepared to restart the transaction when serialization fails. In fact, the isolation level is exactly the same as repeatable read hope, and it simply monitors these conditions to perform the behavior of parallel serializable transactions in a way that is inconsistent with the possible sequence of all transactions (one at a time). This monitoring does not introduce any behavior that prevents repeatable read occurrences, but there are some overhead monitoring, detection conditions which may cause serialization exceptions to trigger serialization failures.

Analysis: If you need a better "two transactions simultaneously modify the same row of data" control this behavior, you can set the transaction isolation level to Serializable (SERIALIZABLE). Under this strategy, the above scenario will fail directly because it follows the rule: "If the row I am modifying is modified by another transaction, I will not try again" and Postgres will return the error message: Serial access is not possible because of concurrent modifications. Capturing this error and then retrying is what your app needs to do, or not just give it a try, if that's reasonable.

ActiveRecord::base.isolation_level (do   order.where (2). Update_all (endEnd  Order.where (2). Update_all (123)# Pg::trserializationfailure:error:could not serialize access due to Concurrent Update# activerecord::transactionisolationconflict       

Postgre Transaction Isolation