In SQL standards, the things isolation levels are divided into the following four types:
1. Read uncommitted
2. Read committed
3. Repeatable read
4. Serializable
However, PostgreSQL only implemented two of them in versions before 9.1, namely, read commit and serializable. If the other two are selected in actual applications, PostgreSQL will automatically adjust to a stricter isolation level. There are three implementation methods provided in the version of PostgreSQL v9.1, namely, the addition of repeatable readings on the original basis. In this blog we will just explain and compare 2) and 4) because in 9.1, the difference between 3) and 4) is also very small.
| Read Submitted | Serializable | |
| PostgreSQL default isolation level | yes | no |
| Is data not submitted by other transactions visible | Not visible | Not visible |
| Execution efficiency | high | Low |
| Applicable scenarios | Simple SQL logic, if the SQL statement contains nested queries, it is very likely to obtain different versions of data in multiple SQL queries. | Complex SQL logic, especially nested queries, are more suitable. |
| SELECT query consistency time point | When the SELECT query starts execution, during the query execution, any other concurrent thing's data operations on 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 when the query starts execution. | From the start of the thing where the SELECT query is located, during the execution of this query, any other concurrent thing's data operations on 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 when the query is started. |
| Is the data operations within colleagues visible? | For example, there are update and select operations in the same thing. Even if the current thing has not been submitted, the modifications made by the update are still visible in the select behind the current thing. | Same as read has been submitted. |
| Is the data seen by the same select multiple times in a colleague? | Different, since the consistency point in the select level is when the query starts execution, the time points of multiple queries will definitely be different. If between the first query and the second query start, other concurrent things modify and submit or the current thing only modify the data that the query will get, then the results of these data operations will be reflected in the second query. | It needs to be divided into two steps. If the modifications within the same thing occur between two query statements, the second query will see the results of these modifications. However, modifications to other concurrent things will not have any impact, that is, the result of the two selects is the same. The reason is obvious that the select consistency point in time for this isolation level is consistent with the beginning of the thing. |
| Modification of the same row of data | If two concurrent things are modifying the same row of data at this time, the first modified thing will give the row-level lock to the row, and the other thing will enter a waiting state until the first thing operation of the row ends. Then if the first modification operation for that row is finally rolled back by its thing, the second modification operation will directly modify the data after the wait is completed. However, if the first operation is submitted normally, then further judgment of the type of the operation is needed. If the line is deleted, the second modification operation will be directly ignored. If it is a record of updating the row, the second modification operation needs to reevaluate whether the row still meets the previously defined modification conditions. | The mechanism of reading the committed isolation level is basically the same, except that after the first modification operation is submitted, the second operation will no longer distinguish whether the previous modification is delete or update, but will 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 begins. So when the application receives such an error message, it should exit the current transaction and then restart the entire transaction from scratch. In the application, there should also be necessary code to specifically handle this type of error. |
Finally, it should be noted that in most cases, the read submitted level is applicable, and the concurrency efficiency of this level is higher. Only in more special cases can the current isolation level be manually adjusted to serializable or reproducible.