PostgreSQL Tutorial (ix): Introduction to things isolation _postgresql

The level of separation in the SQL standard is divided into the following four categories:
1. Read not submitted (READ UNCOMMITTED)
2. Read submitted (committed)
3. Repeatable READ (REPEATABLE Read)
4. Serializable (Serializable)
However, PostgreSQL was only able to implement two of the previous versions of 9.1, read-committed and serializable, and if the other two were selected in the actual application, PostgreSQL would automatically adjust to a stricter isolation level. There are three implementations available in the PostgreSQL v9.1 version, which adds repeatable reads on the original basis. In this blog we will only note and compare for 2 and 4, because the difference between 9.1, 3, and 4 is also very small.

	Read submitted	Serializable
PostgreSQL Default Isolation Level	Is	Whether
Other things are not submitted data visible	Not visible	Not visible
Execution efficiency	High	Low
Applicable scenarios	Simple SQL logic, if a SQL statement contains nested queries, it is highly likely that different versions of the data will be available in multiple SQL queries.	Complex SQL logic, especially with nested queries, is more appropriate.
Select query consistency point in time	When the select query begins execution, during the execution of this query, any other concurrency data operation for the query result set will not be read by this query, that is, the version of the data that this query obtains is consistent with the data version at the start of the query.	From the beginning of the object at which the select query is executed, any other concurrent object's data operations against the query result set will not be read by this query, that is, the data version obtained by this query is consistent with the data version at the start of the query.
Whether the data operation within the same thing is visible	For example, there is an update and select operation within the same thing, even if the current thing has not yet been submitted, update changes are still visible in the select behind the current thing.	And read has been submitted the same.
The same data that is seen in the same select multiple times in the same thing	Different, because the consistency point of the level select is when the query starts executing, and the time points for multiple queries are definitely different, if the other concurrent things are modified and submitted or the current thing modifies only the data that the query will get when the first query starts at the beginning of the second query, The results of these data operations will be reflected in the second query.	In two steps, the second query will see the results of the changes in the same thing if it occurs between the two query statements. However, changes to other concurrent things will not have any effect, that is, the results of the two select are the same. For obvious reasons, the Select consistency point of the isolation level is the same as when things started.
Changes in peer data	If at this point two concurrent things are modifying the same row of data, the first thing to modify will give the row the row-level lock, and another thing will go into the waiting state until the first thing to operate the line ends. So if the first modification to the row is eventually rolled back, the second modification will modify the data directly after the wait is completed. However, if the first operation is submitted normally, then the type of the operation needs to be further judged, and if the row is deleted (delete), the second modification will be ignored directly. If you update the record for that row, the second modification requires a reassessment of whether the row still conforms to the previously defined modification criteria.	The mechanism for reading committed isolation levels is essentially the same, except that after the first modification is submitted, the second action will no longer distinguish between delete or update, but directly and return the following information: Error:can ' t serialize access due to Concurrent update. This is because a serializable transaction cannot change or lock rows that have been changed by other transactions after the start of a serializable transaction. Therefore, when an application receives such an error message, it should exit the current transaction and then restart the entire transaction from the beginning. In your application, you should also have the necessary code to deal specifically with this type of error.

Finally, it should be explained that, in most cases, the read-committed level is applicable, and that the level of concurrency is more efficient. It is only in a special case that the current thing isolation level is manually adjusted to serializable or repeatable.