PostgreSQL tutorial of ix: introduction to the isolation of things

2020-05-06 11:54:02
OfStack

In the standard of SQL, there are four kinds of isolation levels: 

1. Read uncommitted (Read uncommitted)

      2. Read submitted (Read committed)

3. Repeatable reading (Repeatable read)

4. Serializable (Serializable)

Whereas       only implemented two of them, read-committed and serializable, in previous versions of 9.1, PostgreSQL will automatically adjust to the stricter isolation level if the other two are selected in practice. Three implementations are provided in the PostgreSQL v9.1 release, which adds repeatable reads to the original. In this blog post, we will only explain and compare 2) and 4), because in 9.1, the difference between 3) and 4) is also very small. 

      读已提交
      可串行化
    
      PostgreSQL缺省隔离级别
      是
      否
    
      其它事物未提交数据是否可见
      不可见
      不可见
    
      执行效率
      高
      低
    
      适用场景
      简单SQL逻辑，如果SQL语句中含有嵌套查询，那么在多次SQL查询中将极有可能获得不同版本的数据。
      复杂SQL逻辑，特别是带有嵌套的查询比较适用。
    
      SELECT查询一致性时间点
      从该SELECT查询开始执行时，在此查询执行期间，任何其它并发事物针对该查询结果集的数据操作都将不会被本次查询读到，即本次查询获取的数据版本是与查询开始执行时的数据版本相一致。
      从该SELECT查询所在事物开始时，在此查询执行期间，任何其它并发事物针对该查询结果集的数据操作都将不会被本次查询读到，即本次查询获取的数据版本是与查询所在事物开始时的数据版本相一致。
    
      同事物内的数据操作是否可见
      比如在同一个事物内存在update和select操作，即使当前事物尚未提交，update所作的修改，在当前事物后面的select中依然可见。
      和读已提交相同。
    
      同事物内多次相同的select所见的数据是否相同
      不同，由于该级别select的一致性时间点是该查询开始执行时，而多次查询的时间点将肯定不相同，如果在第一次查询开始到第二次查询开始之间，其它的并发事物修改并提交或当前事物仅修改了查询将要获取的数据，那么这些数据操作的结果将会在第二个查询中有所体现。
      需要分两步来说，对于同一事物内的修改如果发生在两次查询语句之间，那么第二个查询将会看到这些修改的结果。然而对于其它并发事物的修改，将不会造成任何影响，即两次select的结果是相同的。原因显而易见，该隔离级别的select一致性时间点是与事物开始时相一致的。
    
      相同行数据的修改
      如果此时两个并发事物在修改同一行数据，先修改的事物将会给该行加行级锁，另外一个事物将进入等待状态，直到第一个事物操作该行结束。那么倘若第一个针对该行的修改操作最终被其事物回滚，第二个修改操作在结束等待后，将直接修改该数据。然而如果第一个操作是被正常提交的话，那么就需要进一步判断该操作的类型，如果是删除(delete)该行，第二个修改操作将直接被忽略。如果是update该行的记录，第二个修改操作则需要重新评估该行是否依然符合之前定义的修改条件。
      和读已提交隔离级别的机制基本相同，只是在第一个修改操作提交后，第二个操作将不再区分之前的修改是delete还是update，而是直接并返回下面信息：Error: Can't serialize access due to concurrent update. 这是因为一个可串行化的事务在可串行化事务开始之后不能更改或者锁住被其他事务更改过的行。因此，当应用收到这样的错误信息时，它应该退出当前的事务然后从头开始重新进行整个事务。在应用程序中，也应该有必要的代码来专门处理该类错误。
    
Finally,       notes that read commit levels are applicable in most cases and are more efficient in concurrency. Only in more exceptional cases do you manually adjust the current level of things isolation to serializable or repeatable.

	读已提交	可串行化
PostgreSQL缺省隔离级别	是	否
其它事物未提交数据是否可见	不可见	不可见
执行效率	高	低
适用场景	简单SQL逻辑，如果SQL语句中含有嵌套查询，那么在多次SQL查询中将极有可能获得不同版本的数据。	复杂SQL逻辑，特别是带有嵌套的查询比较适用。
SELECT查询一致性时间点	从该SELECT查询开始执行时，在此查询执行期间，任何其它并发事物针对该查询结果集的数据操作都将不会被本次查询读到，即本次查询获取的数据版本是与查询开始执行时的数据版本相一致。	从该SELECT查询所在事物开始时，在此查询执行期间，任何其它并发事物针对该查询结果集的数据操作都将不会被本次查询读到，即本次查询获取的数据版本是与查询所在事物开始时的数据版本相一致。
同事物内的数据操作是否可见	比如在同一个事物内存在update和select操作，即使当前事物尚未提交，update所作的修改，在当前事物后面的select中依然可见。	和读已提交相同。
同事物内多次相同的select所见的数据是否相同	不同，由于该级别select的一致性时间点是该查询开始执行时，而多次查询的时间点将肯定不相同，如果在第一次查询开始到第二次查询开始之间，其它的并发事物修改并提交或当前事物仅修改了查询将要获取的数据，那么这些数据操作的结果将会在第二个查询中有所体现。	需要分两步来说，对于同一事物内的修改如果发生在两次查询语句之间，那么第二个查询将会看到这些修改的结果。然而对于其它并发事物的修改，将不会造成任何影响，即两次select的结果是相同的。原因显而易见，该隔离级别的select一致性时间点是与事物开始时相一致的。
相同行数据的修改	如果此时两个并发事物在修改同一行数据，先修改的事物将会给该行加行级锁，另外一个事物将进入等待状态，直到第一个事物操作该行结束。那么倘若第一个针对该行的修改操作最终被其事物回滚，第二个修改操作在结束等待后，将直接修改该数据。然而如果第一个操作是被正常提交的话，那么就需要进一步判断该操作的类型，如果是删除(delete)该行，第二个修改操作将直接被忽略。如果是update该行的记录，第二个修改操作则需要重新评估该行是否依然符合之前定义的修改条件。	和读已提交隔离级别的机制基本相同，只是在第一个修改操作提交后，第二个操作将不再区分之前的修改是delete还是update，而是直接并返回下面信息：Error: Can't serialize access due to concurrent update. 这是因为一个可串行化的事务在可串行化事务开始之后不能更改或者锁住被其他事务更改过的行。因此，当应用收到这样的错误信息时，它应该退出当前的事务然后从头开始重新进行整个事务。在应用程序中，也应该有必要的代码来专门处理该类错误。