PostgreSQL learning Manual (7) transaction isolation

In the standard of SQL, the transaction isolation level is divided into the following four types:
1. Read uncommitted)
2. Read committed)
3. Repeatable read)
4. serializable)
However, PostgreSQL versions earlier than 9.1 only implement two of them: Read committed and serializable. If you select the other two in actual application, then PostgreSQL will automatically adjust to a stricter isolation level. Three implementation methods are provided in PostgreSQL v9.1, that is, deduplication is added to the original version. In this blog, we will only explain and compare the differences between 2) and 4), because in 9.1, 3) and 4) are very small.

	Read committed	Serializable
PostgreSQL default isolation level	Yes	No
Whether the uncommitted data of other things is visible	Invisible	Invisible
Execution efficiency	High	Low
Applicable scenarios	Simple SQL logic. If an SQL statement contains nested queries, data of different versions may be obtained in multiple SQL queries.	Complex SQL logic, especially nested queries.
SELECT query consistency time point	When the SELECT query starts to run, any other concurrent operations on the query result set during the query will not be read by this query, that is, the data version obtained by this query is consistent with the data version when the query starts execution.	From the time the SELECT statement is queried, any data operations performed by other concurrent tasks on the query result set during the query execution will not be read by this query, that is, the data version obtained by this query is consistent with the data version at the beginning of the query.
Is data operations in a colleague's belongings visible?	For example, if the update and select operations exist in the same thing, even if the current thing has not been submitted, the changes made by update are still visible in the select operation after the current thing.	Same as read committed.
whether the data seen by the same SELECT statement is the same for multiple times in a colleague	different, because the consistency time of select statements at this level is when the query starts to be executed, the time points for multiple queries are certainly different, if other concurrent transactions are modified and submitted, or the current transaction only modifies the data to be retrieved, the results of these data operations will be reflected in the second query.	in two steps, if modifications to the same thing occur between two query statements, the results of these modifications are displayed in the second query. However, modifications to other concurrent things will not have any impact, that is, the results of the two select statements are the same. It is obvious that the select consistency time point at this isolation level is consistent with the start time of the transaction.
Modify data of the same row	If two concurrent transactions are modifying the same row of data at this time, the first modified transaction will apply a row-Level Lock to the row, and the other transaction will enter the waiting state until the first transaction operation ends. If the first modification operation for this row is eventually rolled back by its transaction, the second modification operation will directly modify the data after the wait. However, if the first operation is submitted normally, the type of the operation needs to be further determined. If the row is deleted, the second modification operation will be ignored. If the row is updated, the second modification operation needs to re-evaluate whether the row still meets the previously defined Modification Conditions.	The read-committed isolation level mechanism is basically the same, but after the first modification operation is submitted, the second operation will no longer distinguish whether the previous modification is delete or update, but directly return the following information: Error: Can't serialize access due to concurrent update. this is because a serializable transaction cannot be changed or locked after the serializable transaction starts. Therefore, when the application receives such an error message, it should exit the current transaction and re-execute the entire transaction from the beginning. In the ApplicationProgram, It should also be necessaryCodeTo handle such errors.

It should be noted that in most cases, the Read committed level can be applied, and the concurrency efficiency of this level is higher. Only in special cases can the isolation level of the current transaction be manually adjusted to serializable or repeatable.

In the standard of SQL, the transaction isolation level is divided into the following four types:
1. Read uncommitted)
2. Read committed)
3. Repeatable read)
4. serializable)
However, PostgreSQL versions earlier than 9.1 only implement two of them: Read committed and serializable. If you select the other two in actual application, then PostgreSQL will automatically adjust to a stricter isolation level. Three implementation methods are provided in PostgreSQL v9.1, that is, deduplication is added to the original version. In this blog, we will only explain and compare the differences between 2) and 4), because in 9.1, 3) and 4) are very small.

	Read committed	Serializable
PostgreSQL default isolation level	Yes	No
Whether the uncommitted data of other things is visible	Invisible	Invisible
Execution efficiency	High	Low
Applicable scenarios	Simple SQL logic. If an SQL statement contains nested queries, data of different versions may be obtained in multiple SQL queries.	Complex SQL logic, especially nested queries.
SELECT query consistency time point	When the SELECT query starts to run, any other concurrent operations on the query result set during the query will not be read by this query, that is, the data version obtained by this query is consistent with the data version when the query starts execution.	From the time the SELECT statement is queried, any data operations performed by other concurrent tasks on the query result set during the query execution will not be read by this query, that is, the data version obtained by this query is consistent with the data version at the beginning of the query.
Is data operations in a colleague's belongings visible?	For example, if the update and select operations exist in the same thing, even if the current thing has not been submitted, the changes made by update are still visible in the select operation after the current thing.	Same as read committed.
whether the data seen by the same SELECT statement is the same for multiple times in a colleague	different, because the consistency time of select statements at this level is when the query starts to be executed, the time points for multiple queries are certainly different, if other concurrent transactions are modified and submitted, or the current transaction only modifies the data to be retrieved, the results of these data operations will be reflected in the second query.	in two steps, if modifications to the same thing occur between two query statements, the results of these modifications are displayed in the second query. However, modifications to other concurrent things will not have any impact, that is, the results of the two select statements are the same. It is obvious that the select consistency time point at this isolation level is consistent with the start time of the transaction.
Modify data of the same row	If two concurrent transactions are modifying the same row of data at this time, the first modified transaction will apply a row-Level Lock to the row, and the other transaction will enter the waiting state until the first transaction operation ends. If the first modification operation for this row is eventually rolled back by its transaction, the second modification operation will directly modify the data after the wait. However, if the first operation is submitted normally, the type of the operation needs to be further determined. If the row is deleted, the second modification operation will be ignored. If the row is updated, the second modification operation needs to re-evaluate whether the row still meets the previously defined Modification Conditions.	The read-committed isolation level mechanism is basically the same, but after the first modification operation is submitted, the second operation will no longer distinguish whether the previous modification is delete or update, but directly return the following information: Error: Can't serialize access due to concurrent update. this is because a serializable transaction cannot be changed or locked after the serializable transaction starts. Therefore, when the application receives such an error message, it should exit the current transaction and re-execute the entire transaction from the beginning. In applications, the necessary code should also be used to handle such errors.

It should be noted that in most cases, the Read committed level can be applied, and the concurrency efficiency of this level is higher. Only in special cases can the isolation level of the current transaction be manually adjusted to serializable or repeatable.