(openGauss每日一练第 16天)：openGauss事务控制、检查点的创建和回滚

1.事务控制

(1)、使用gsql登录openGauss
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=#

(2)、通过START TRANSACTION和BEGIN语法启动事务
--以默认方式启动事务
omm=# START TRANSACTION;
START TRANSACTION

omm=# select * from pg_class limit 1;
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | rel
toastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind 
| relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement 
| parttype | relfrozenxid |      relacl       | reloptions | relreplident | relfrozenxid64 | relbucket | relbucketkey 
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+----
-----------+---------------+---------------+-------------+----------------+--------------+-------------+-------------+----------------+---------
+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+----------------
+----------+--------------+-------------------+------------+--------------+----------------+-----------+--------------
      2840 |             0 |             0 |           0 |              0 |            0 | t           | f           | p              | r       
|       29 |         0 | f          | f          | f           | f              | f              | 0        | f                | f              
| n        | 0            | {omm=arwdDxt/omm} |            | n            |           7891 |           | 
(1 row)

 pg_statistic |           11 |   11334 |         0 |       10 |     0 |       13689 |             0 |       18 |       476 |            18 |    
omm=# END;
COMMIT

(3)、开启一个事务，设置事务的隔离级别为READ COMMITTED，访问模式为READ ONLY
omm=# BEGIN;
BEGIN

omm=# SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
SET

omm=# show transaction_read_only;
omm=#  transaction_read_only 
-----------------------
 on
(1 row)

omm=# select * from pg_class limit 1;
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | rel
toastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind 
| relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement 
| parttype | relfrozenxid |      relacl       | reloptions | relreplident | relfrozenxid64 | relbucket | relbucketkey 
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+----
-----------+---------------+---------------+-------------+----------------+--------------+-------------+-------------+----------------+---------
+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+----------------
+----------+--------------+-------------------+------------+--------------+----------------+-----------+-------------- pg_statistic |           11 |   11334 |         0 |       10 |     0 |       13689 |             0 |       18 |       476 |            18 |    
      2840 |             0 |             0 |           0 |              0 |            0 | t           | f           | p              | r       
|       29 |         0 | f          | f          | f           | f              | f              | 0        | f                | f              
| n        | 0            | {omm=arwdDxt/omm} |            | n            |           7891 |           | 
(1 row)

omm=# create schema tpcds10;
ERROR:  cannot execute CREATE SCHEMA in a read-only transaction
--尝试创建用户失败，因为访问模式为READ ONLY
omm=# commit;
ROLLBACK

(3)、以隔离级别为repeatable read，读/写方式启动事务
omm=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

omm=# START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
START TRANSACTION

omm=# show transaction_isolation;
 transaction_isolation 
-----------------------
 repeatable read
(1 row)

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

omm=# select * from pg_class limit 1;
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | rel
toastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind 
| relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement 
| parttype | relfrozenxid |      relacl       | reloptions | relreplident | relfrozenxid64 | relbucket | relbucketkey 
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+----
-----------+---------------+---------------+-------------+----------------+--------------+-------------+-------------+----------------+---------
+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+----------------
+----------+--------------+-------------------+------------+--------------+----------------+-----------+--------------
 pg_statistic |           11 |   11334 |         0 |       10 |     0 |       13689 |             0 |       18 |       476 |            18 |    
| n        | 0            | {omm=arwdDxt/omm} |            | n            |           7891 |           | 
(1 row)

      2840 |             0 |             0 |           0 |              0 |            0 | t           | f           | p              | r       
|       29 |         0 | f          | f          | f           | f              | f              | 0        | f                | f              


--再次创建用户tpcds10
omm=# create schema tpcds10;
CREATE SCHEMA

--回滚
omm=# rollback;
ROLLBACK

(8)、查看schema tpcds10
omm=# \dn+ tpcds10;
                List of schemas
 Name | Owner | Access privileges | Description 
------+-------+-------------------+-------------
(0 rows)
--因为事务回滚，schema没有创建成功

2.savepoint

保存点是事务中的一个特殊记号，它允许将那些在它建立后执行的命令全部回滚，把事务的状态恢复到保存点所在的时刻

(1)、创建表，启动事务并插入数据
omm=# CREATE TABLE table1(a int);
CREATE TABLE

omm=# START TRANSACTION;
START TRANSACTION

omm=# INSERT INTO table1 VALUES (1);
INSERT 0 1

(2)、建立保存点，并插入数据
omm=# SAVEPOINT my_savepoint;
SAVEPOINT

omm=# INSERT INTO table1 VALUES (2);
INSERT 0 1

(3)、回滚保存点
omm=# ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK

omm=# select * from table1;
 a 
---
 1
(1 row)

(4)、删除保存点

omm=# RELEASE SAVEPOINT my_savepoint;
RELEASE

omm=# INSERT INTO table1 VALUES (3);
INSERT 0 1

omm=# COMMIT;
COMMIT

(5)、查询表的内容，会同时看到1和3,不能看到2，因为2被回滚
omm=# SELECT * FROM table1;
 a 
---
 1
 3
(2 rows)

练习：

1.以默认方式启动事务1，修改事务隔离级别，查看transaction_isolation
omm=# START TRANSACTION;
START TRANSACTION

omm=# SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
SET

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

omm=# END;
COMMIT
2.以读写方式启动事务2，创建新表，修改事务为只读事务，查看transaction_read_only，并向表中插入记录
omm=# START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
START TRANSACTION

omm=# CREATE TABLE table1(id int);
CREATE TABLE

omm=# SET TRANSACTION  READ ONLY;
SET
omm=# show transaction_isolation;

omm=#  transaction_isolation 
-----------------------
 repeatable read
(1 row)

INSERT INTO table1 VALUES (1);
ERROR:  cannot execute INSERT in a read-only transaction
omm=# END;
ROLLBACK

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

omm=# CREATE TABLE table2(id int);
CREATE TABLE

omm=# INSERT INTO table2 VALUES (1);
INSERT 0 1

omm=# INSERT INTO table2 VALUES (2);
INSERT 0 1

omm=# SAVEPOINT my_savepoint;
SAVEPOINT

omm=# delete from table2 where id =1;
DELETE 1

omm=# ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK

omm=# select * from table2;
(2 rows)

omm=#  id 
----
  1
  2
(2 row)

omm=# RELEASE SAVEPOINT my_savepoint;
RELEASE

4.清理数据
omm=# drop table table1;
ERROR:  table "table1" does not exist

omm=# drop table table2;
ERROR:  table "table2" does not exist
--因为以上两表的事务都已经被回滚所以表不存在