【openGauss 5.0.0】事务管理与锁示例

一、环境说明

二、事务管理与锁概念

三、事务管理与锁示例

以下将以事务块的方式进行演示，增删改查所对应的锁分别是什么，通过以下示例加以说明：

1. 示例1：DDL操作

   1. 创建表
   2. 新增列
   3. 重命名表
   4. 删除列

   窗口1：执行DDL操作

   openGauss=# begin ;
   BEGIN
   openGauss=# create table orders(name varchar(40),amount int);
   CREATE TABLE
   openGauss=# end;
   
   

   窗口2：执行查看锁类型

   # 未执行create语句之前
   openGauss=# select database,relation,locktype,transactionid,pid,mode from pg_locks ;
    database | relation |  locktype  | transactionid |       pid       |      mode
   ----------+----------+------------+---------------+-----------------+-----------------
       15730 |    12136 | relation   |               | 139863564482304 | AccessShareLock
             |          | virtualxid |               | 139863564482304 | ExclusiveLock
   
   
   # 执行create语句之后
   openGauss=# select database,relation,locktype,transactionid,pid,mode from pg_locks ;
    database | relation |   locktype    | transactionid |       pid       |        mode
   ----------+----------+---------------+---------------+-----------------+---------------------
             |          | virtualxid    |               | 139863871444736 | ExclusiveLock
       15730 |    12136 | relation      |               | 139863564482304 | AccessShareLock
             |          | virtualxid    |               | 139863564482304 | ExclusiveLock
       15730 |    24579 | relation      |               | 139863871444736 | AccessExclusiveLock
             |          | transactionid |         26082 | 139863871444736 | ExclusiveLock
       15730 |          | object        |               | 139863871444736 | AccessShareLock
   (6 rows)
   
   # 提交事务之后
   openGauss=# select database,relation,locktype,transactionid,pid,mode from pg_locks ;
    database | relation |  locktype  | transactionid |       pid       |      mode
   ----------+----------+------------+---------------+-----------------+-----------------
       15730 |    12136 | relation   |               | 139863564482304 | AccessShareLock
             |          | virtualxid |               | 139863564482304 | ExclusiveLock
   (2 rows)
   

2. 示例2：DML操作
   窗口1：执行显示事务

   openGauss=# begin ;
   BEGIN
   openGauss=# delete from tb1;
   DELETE 1
   openGauss=# insert into tb1 values(2,2);
   INSERT 0 1
   openGauss=# select * from tb1;
    arr1 | att2
   ------+------
       2 |    2
   (1 row)
   

   窗口2：查询pg_class和pg_locks视图，查看变化

   openGauss=# select relname,oid from pg_class where relname='tb1';
    relname |  oid
   ---------+-------
    tb1     | 24576
    
    # 当执行delete和insert语句时，会看到ExclusiveLock和RowExclusiveLock排他锁
   openGauss=# select database,relation,locktype,transactionid,pid,mode from pg_locks ;
    database | relation |   locktype    | transactionid |       pid       |       mode
   ----------+----------+---------------+---------------+-----------------+------------------
       15730 |    12136 | relation      |               | 139863648499456 | AccessShareLock
             |          | virtualxid    |               | 139863648499456 | ExclusiveLock
       15730 |    24576 | relation      |               | 139864098199296 | RowExclusiveLock
             |          | virtualxid    |               | 139864098199296 | ExclusiveLock
             |          | transactionid |         26080 | 139864098199296 | ExclusiveLock
             
   # 当执行select语句时，会看到AccessShareLock 访问共享锁
   openGauss=# select database,relation,locktype,transactionid,pid,mode from pg_locks ;
    database | relation |   locktype    | transactionid |       pid       |       mode
   ----------+----------+---------------+---------------+-----------------+------------------
       15730 |    12136 | relation      |               | 139863648499456 | AccessShareLock
             |          | virtualxid    |               | 139863648499456 | ExclusiveLock
       15730 |    24576 | relation      |               | 139864098199296 | AccessShareLock
       15730 |    24576 | relation      |               | 139864098199296 | RowExclusiveLock
             |          | virtualxid    |               | 139864098199296 | ExclusiveLock
             |          | transactionid |         26080 | 139864098199296 | ExclusiveLock
   

3. 示例3：DQL操作

   1. 查询数据
      窗口1：执行显示事务

   窗口2：查询pg_locks视图，查看锁的变化

4. 示例4：DCL操作

   1. 授权
   2. 撤销

5. 事务隔离级别

   在openGauss事务默认级别【读已提交】下，复现可能出现的【不可能重复读】的问题：
   窗口1事务：执行insert插入

   openGauss=#  begin ;
   BEGIN
   openGauss=# insert into orders values('user2',100);
   INSERT 0 1
   # 此时事务未提交，切换到窗口2中进行操作
   # 当窗口2执行了查询操作后，再来执行提交操作
   openGauss=# end;
   COMMIT
   openGauss=#
   

   窗口2事务：执行select操作

   openGauss=# begin ;
   BEGIN
   openGauss=# select * from orders;
      name   | amount
   ----------+--------
    customer |      0
    shop     |    520
   (2 rows)
   # 此时查看到的是事务开始之前已提交的数据，切换到窗口1执行事务提交操作
   # 然后再执行一次查询
   openGauss=# select * from orders;
      name   | amount
   ----------+--------
    customer |      0
    shop     |    520
    user2    |    100   # 这一行就是体现了【不可重复读】的问题
   (3 rows)
   

   由此证明了，openGauss在事务隔离级别为【读已提交】下，一个查询语句只能读取到另外一个已提交的事务的数据。另外，针对窗口2而言，两次查询的结果是不一样的，也就证明了在窗口2当前事务中读取到了不一样的数据，也就是在窗口2事务中出现了【不可重复读】的现象。

   如何解决在一个事务中出现了不可重复读的问题呢？
   【答案】将openGauss默认的隔离级别设置为【repeatable read】可重复读。
   【演示】同样开启两个会话窗口1和窗口2，具体执行过程如下：
   先创建一张新表：

   openGauss=# create table tb4(a int,b int);
   

   在两个窗口中设置隔离级别：

   openGauss=# set default_transaction_isolation='repeatable read';
   

   窗口1中执行查询操作：

   openGauss=# begin ;
   BEGIN
   openGauss=# select * from tb4 ;  # 第一次查询，此时窗口2还没有插入数据
    a | b
   ---+---
   (0 rows)
   
   openGauss=# select * from tb4 ;  # 第二次查询，此时窗口2已执行insert操作，但事务为提交
    a | b
   ---+---
   (0 rows)
   
   openGauss=# select * from tb4 ; # 第三次查询，此时窗口2事务已提交，但是仍然看不到已提交的数据
    a | b
   ---+---
   (0 rows)
   

   窗口2中执行插入操作：

   openGauss=# begin ;
   BEGIN
   openGauss=# insert into tb4 values(1,1);  # 插入数据，未提交
   INSERT 0 1
   openGauss=# commit;  # 事务已被提交
   COMMIT
   

   由此可以看到，针对窗口1中的事务，无论窗口2的事务提交与否，窗口1事务中读取到的仍然是事务开启前的状态。由此，在可重复读的隔离级别下，保证了窗口1事务不受到其他事务提交的影响。
   此时，假设我们在窗口1中也执行插入数据的操作，会产生什么效果呢？此时，因我们所创建的表tb4并未设置任何约束，既没有主键约束，也没有其他的唯一性约束，在窗口1中执行的insert操作，依然会把与窗口2中insert相同的数据插入到表中：

   openGauss=# insert into tb4 values(1,1);  # 窗口1中插入数据，未提交
   INSERT 0 1
   openGauss=# commit;  # 窗口1中事务已被提交
   # 再次查询，结果如下：
   openGauss=# select * from tb4;
    a | b
   ---+---
    1 | 1
    1 | 1
   (2 row)
   

   假如，我们把上面的tb4删除后，重新建一张有主键约束的表tb4，然后同样执行上述操作，当窗口2提交事务后，再在窗口1中执行和窗口2相同的insert语句，会发现无法插入，因有主键约束了，无法执行相同记录的insert，具体报错信息如下：

   openGauss=# insert into tb4 values(1,1);
   ERROR:  duplicate key value violates unique constraint "tb4_pkey"
   DETAIL:  Key (a)=(1) already exists.
   

   由此，可知，在事务隔离级别为【可重复读】下，openGauss数据库可通过设置表的约束，依然可以保证数据的一致性。