openGauss每日一练第16天 | 学习openGauss事务控制

事务是关系型数据库须要支持的功能，事务的4个特性ACID：原子性、一致性、隔离性、持久性。

这其中隔离又分为四种级别：openGauss默认的事务隔离级别是READ_COMMITED

• 读未提交（Read Uncommitted）：可以读取未提交的记录。
• 读已提交（Read Committed）：事务中只能看到已提交的修改，解决脏读。
• 可重复读（Repeatable Read）：解决了不可重复读问题
  
• 序列化（Serializable）：最高隔离级别。(openGauss目前功能上不支持此隔离级别，等价于REPEATABLE READ)

https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/START-TRANSACTION.html

START TRANSACTION:启动事务

语法：START TRANSACTION格式

START TRANSACTION
  [ 
    { 
       ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE | REPEATABLE READ }
       | { READ WRITE | READ ONLY }
     } [, ...] 
  ];
复制

或BEGIN格式

BEGIN [ WORK | TRANSACTION ]
  [ 
    { 
       ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE | REPEATABLE READ }
       | { READ WRITE | READ ONLY }
      } [, ...] 
  ];
复制

学习笔记：

1.     查看隔离级别：show transaction_isolation;
2.     保存点：SAVEPOINT XXX
3.     回滚到保存点：ROLLBACK TO SAVEPOINT XXX
4.     删除保存点：RELEASE SAVEPOINT xxx;

openGauss没事启用事务时，发生DML操作默认是自动提交，启用事务后，需显示commit;

两种方式修改隔离级别：

•     第一种：(待验证)修改配置文件postgresql.conf，设置默认的隔离级别，如下：

        vim postgresql.conf
        default_transaction_isolation ='read committed'

        重新加载配置：
            gs_ctl -D /data/pg reload

•     第二种：动态修改PostgreSQL隔离级别

        修改隔离级别必须在事务中执行，可以修改默认的隔离级别和当前会话的隔离级别，语法如下：

        set default_transaction_isolation='repeatable read';
        set transaction isolation level serializable;

课程练习

1.以默认方式启动事务1，修改事务隔离级别，查看transaction_isolation

openGauss=# start transaction;
START TRANSACTION
openGauss=# select * from t limit 2;
 id |              name              
----+--------------------------------
  1 | a                             
  1 | XXX                           
(2 rows)

openGauss=# commit;
COMMIT
openGauss=# show default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 read committed
(1 row)

openGauss=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

openGauss=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

openGauss=# begin;
BEGIN
openGauss=# set transaction isolation level serializable;
SET
openGauss=# show transaction_isolation;
 transaction_isolation 
-----------------------
 repeatable read
(1 row)

openGauss=# 
复制

2.以读写方式启动事务2，创建新表，修改事务为只读事务，查看transaction_read_only，并向表中插入记录

openGauss=# begin;
BEGIN
openGauss=# set local transcation

openGauss=# set local transaction read write;
SET
openGauss=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

openGauss=# show transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 row)

openGauss=# create table tt (id int,name char(30));
CREATE TABLE

openGauss=# set local transaction read only;
SET
openGauss=# show transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

openGauss=# insert into tt values (1,'a');
ERROR:  cannot execute INSERT in a read-only transaction
openGauss=# 
复制

3.启动事务3，对表进行增删改查，并用到创建savepoint，回滚savepoint和删除savepoint

openGauss=# begin;
BEGIN
openGauss=# insert into t values (1,'a'),(2,'b');
INSERT 0 2
openGauss=# select * from t;
 id |              name              
----+--------------------------------
  1 | a                             
  1 | XXX                           
    | XXX                           
  3 | ppp                           
  9 | kkk                           
  1 | a                             
  2 | b                             
(7 rows)

openGauss=# savepoint mypoint;
SAVEPOINT
openGauss=# delete from t;
DELETE 7
openGauss=# select * from t;
 id | name 
----+------
(0 rows)

openGauss=# rollback to savepoint mypoint;
ROLLBACK
openGauss=# select * from t;
 id |              name              
----+--------------------------------
  1 | a                             
  1 | XXX                           
    | XXX                           
  3 | ppp                           
  9 | kkk                           
  1 | a                             
  2 | b                             
(7 rows)

openGauss=# release savepoint mypoint;
RELEASE
openGauss=# commit;
COMMIT
openGauss=# 
复制

4.清理数据

openGauss=# delete from t;
DELETE 7
复制