PostgreSQL中事务的隔离级别

迈不过友情╰ 2023-02-18 14:08 36阅读 0赞

参考文章：[PostgreSQL中事务的隔离级别][PostgreSQL]

# 数据库事务的隔离级别有以下四种： #

> （1）读未提交（READ UNCOMMITTED）
> 
> （2）读已提交（READ COMMITTED）
> 
> （3）重复读（REPEATABLE READ）
> 
> （4）串行化（SERIALIZABLE）

# 对于并发事务，我们不希望发生的行为如下： #

> （1）脏读：一个事务读取了另一个未提交的事务写入的数据。
> 
> （2）不可重复读：一个事务重新读取前面读取过的数据时，发现该数据已改变。
> 
> （3）幻读：一个事务开始后，需要根据数据库中现有的数据做一些更新，于是重新执行一个查询，返回符合查询条件的行，这时发现这些行因为其它最近提交的事务而发生了改变，导致现有事务如果再进行下去可能发发生逻辑上的错误。

不同事务隔离级别的行为见表：

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>读未提交</td> 
   <td>可能</td> 
   <td>可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>读已提交</td> 
   <td>不可能</td> 
   <td>可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>重复读</td> 
   <td>不可能</td> 
   <td>不可能</td> 
   <td>可能</td> 
  </tr> 
  <tr> 
   <td>可串行化</td> 
   <td>不可能</td> 
   <td>不可能</td> 
   <td>不可能</td> 
  </tr> 
 </tbody> 
</table>

![20190311164109266.png][]

PostgreSQL里默认的隔离级别是"读已提交"。

# PostgreSQL事务说明： #

> PostgreSQL事务的隔离级别目前有4种，分别是：读未提交，读已提交，可重复读，串行化。  
>     在PostgreSQL里，可以请求四种可能的事务隔离级别中的任意一种。  
>     但在内部，实际上只有三种独立的隔离级别，分别对应读已提交，可重复读和可串行化。  
>     如果选择了读未提交的级别， 实际上用的是读已提交，  
>     在重复读的PostgreSQL执行时，幻读是不可能的，  
>     所以实际的隔离级别可能比选择的更严格。  
>     这是因为把标准隔离级别映射到 PostgreSQL 的多版本并发控制架构的唯一合理的方法。  
>     PostgreSQL里默认的隔离级别是"读已提交"。

**要设置一个事务的事务隔离级别，使用SET TRANSACTION命令：**

SET TRANSACTION transaction_mode [, ...]
    SET TRANSACTION SNAPSHOT snapshot_id
    SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]
    
    where transaction_mode is one of:
    
        ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
        READ WRITE | READ ONLY
        [ NOT ] DEFERRABLE

**例如：**

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT pg_export_snapshot();
     pg_export_snapshot
    ---------------------
     00000003-0000001B-1
    (1 row)

**或者：**

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SET TRANSACTION SNAPSHOT '00000003-0000001B-1';

# 实验 #

duyeweb=# \h begin
    Command:     BEGIN
    Description: start a transaction block
    Syntax:
    BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
    
    where transaction_mode is one of:
    
        ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
        READ WRITE | READ ONLY
        [ NOT ] DEFERRABLE

## 读未提交 ##

表示可以读到其他会话未提交的数据（postgresql不支持）。

读已提交

表示可以读到其他会话已提交的数据。

1. 创建一张表为test，插入一条记录

duyeweb=# create table test(a int, b varchar(128));
    CREATE TABLE
    duyeweb=# insert into test values(1, 'hello');
    INSERT 0 1

2. 在会话1中打开事务进行查询

duyeweb=# begin;
    BEGIN
    duyeweb=# select * from test;
     a |   b   
    ---+-------
     1 | hello
    (1 row)

3. 在会话2中打开事务进行更新

duyeweb=# begin;
    BEGIN
    duyeweb=# update test set b='xxxx';
    UPDATE 1

4. 此时在会话2中还没有关闭事务，在会话1中进行查询

duyeweb=# select * from test;
     a |   b   
    ---+-------
     1 | hello
    (1 row)

5. 发现会话1中的记录并没有进行改变。当提交会话2中的事务，在会话1中进行查询值已经改变

duyeweb=# begin;
    BEGIN
    duyeweb=# update test set b='xxxx';
    UPDATE 1
    duyeweb=# commit;
    COMMIT

再次查询：

duyeweb=# select * from test;
     a |  b   
    ---+------
     1 | xxxx
    (1 row)

## 可重复读 ##

表示在一个事务中，执行同一条SQL，读到的是同样的数据（即使被读的数据可能已经被其他会话修改并提交）。

1 在会话1中打开可重复读事务，进行查询

duyeweb=# begin transaction isolation level repeatable read;
    BEGIN
    duyeweb=# select * from test;
     a |  b   
    ---+------
     1 | xxxx
    (1 row)

2. 在会话2中进行更新操作

duyeweb=# update test set b='yyyyy';
    UPDATE 1

3. 在会话1中进行，发现会话1中的记录没有因为会话2的提交而变化

duyeweb=# select * from test;
     a |   b   
    ---+-------
     1 | xxxx
    (1 row)

4. 在会话1中进行提交，再查询，发现会话1中的记录变化了

duyeweb=# commit;
    duyeweb=# select * from test;
     a |   b   
    ---+-------
     1 | yyyyy
    (1 row)

## 串行化 ##

表示并行事务模拟串行执行，违反串行执行规则的事务，将回滚。

1. 在会话 1中打开事务串行

duyeweb=# begin transaction isolation level serializable;

2. 在会话2中打开事务串行

duyeweb=# begin transaction isolation level serializable;

3、在会话1中进行插入操作

duyeweb=# insert into test select * from test; 
    INSERT 0 1
    duyeweb=# select * from test;                 
     a |   b   
    ---+-------
     1 | yyyyy
     1 | yyyyy
    (2 rows)

4、在会话2中进行插入操作

duyeweb=# insert into test select * from test; 
    INSERT 0 1
    duyeweb=# select * from test; 
     a |   b   
    ---+-------
     1 | yyyyy
     1 | yyyyy
    (2 rows)

5、提交会话1中的事务，能够正常提交，进行查询能够查到插入的记录

duyeweb=# end;
    COMMIT
    duyeweb=# select * from test;
     a |   b   
    ---+-------
     1 | yyyyy
     1 | yyyyy
    (2 rows)

6、当提交会话2的事务时会报错

duyeweb=# end;
    ERROR:  could not serialize access due to read/write dependencies among transactions
    DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
    HINT:  The transaction might succeed if retried.

[椟夜的PostgreSQL源码分析文章汇总][PostgreSQL 1]

[PostgreSQL]: https://blog.csdn.net/liyazhen2011/article/details/83109076?utm_medium=distribute.pc_relevant.none-task-blog-baidujs-1
[20190311164109266.png]: https://img-blog.csdnimg.cn/20190311164109266.png
[PostgreSQL 1]: https://blog.csdn.net/tencupofkaiwater/article/details/86649897