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

学习openGauss的第十六天。
主要内容是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.


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

omm=# start transaction ;
START TRANSACTION
omm=# set local transaction isolation level read committed read only
SET
omm=# select * from pg_class limit 1;
relname | relnamespace | reltype | reloftype | relowner | rela
m | relfilenode | reltablespace | relpages | reltuples | relallvisib
le | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | r
elcudescrelid | relcudescidx | relhasindex | relisshared | relpersis
tence | relkind | relnatts | relchecks | relhasoids | relhaspkey | r
elhasrules | relhastriggers | relhassubclass | relcmprs | relhasclus
terkey | relrowmovement | parttype | relfrozenxid | relacl
| reloptions | relreplident | relfrozenxid64 | relbucket | relbuck
etkey
--------------+--------------+---------+-----------+----------+-----
--+-------------+---------------+----------+-----------+------------
---+---------------+---------------+---------------+-------------+--
-----------+----------------+----------------+----------+-----------
-------+----------------+----------+--------------+-----------------
--+------------+--------------+----------------+-----------+--------
------
--------------+--------------+-------------+-------------+----------
------+---------+----------+-----------+------------+------------+--
18 | 2840 | 0 | 0 | 0 |
0 | 0 | t | f | p
| r | 29 | 0 | f | f | f
pg_statistic | 11 | 11334 | 0 | 10 |
0 | 13689 | 0 | 18 | 476 |
| f | f | 0 | f
| f | n | 0 | {omm=arwdDxt/omm
} | | n | 7891 | |
(1 row)

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

omm=# end;
omm=# COMMIT

2.以读写方式启动事务2，创建新表，修改事务为只读事务，查看transaction_read_only，并向表中插入记录
omm=# start transaction isolation level repeatable read read write;
START TRANSACTION
omm=# create table t1 (a int, b int);
CREATE TABLE
y;m=# set local transaction isolation level repeatable read read only
omm=# SET

omm=# show transaction_read_only;

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

omm=# insert into t1 values(1, 2);
ERROR: cannot execute INSERT in a read-only transaction

omm=# end ;
ROLLBACK

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

omm=# create table tab2(id int,name char(20)) ;
CREATE TABLE
omm=# begin ;
BEGIN
omm=# insert into tab2 values(1,'a') ;
INSERT 0 1
omm=# select * from tab2 ;
id | name
----+----------------------
1 | a
(1 row)

omm=# savepoint point1 ;
SAVEPOINT
omm=# insert into tab2 values(1,'b') ;
INSERT 0 1
omm=# select * from tab2 ;
id | name
----+----------------------
1 | a
1 | b
(2 rows)

omm=# rollback to savepoint point1 ;
ROLLBACK
omm=# select * from tab2 ;
id | name
----+----------------------
1 | a
(1 row)

omm=# release savepoint point1 ;
RELEASE

omm=# commit ;
COMMIT

4.清理数据
omm=# drop table tab2 ;
DROP TABLE