openGauss每日一练第16天｜学习openGauss事务控制的操作

坚持学习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.
omm=#
复制

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

omm=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)
omm=# start transaction;
START TRANSACTION
omm=# start transaction isolation level repeatable read read write;
START TRANSACTION
WARNING:  there is already a transaction in progress
omm=# show transaction_isolation;
 transaction_isolation 
-----------------------
 repeatable read
(1 row)
omm=# end;
COMMIT
复制

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

omm=# start transaction isolation level read committed read write;
START TRANSACTION
omm=# create table tab_test(id int,name varchar(10));
CREATE TABLE
omm=# set local transaction isolation level read committed read only;
SET
omm=# show transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)
omm=# insert into tab_test(id,name) values(1,'zhangsan');
ERROR:  cannot execute INSERT in a read-only transaction
复制

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

omm=# start transaction;
START TRANSACTION
omm=# create table tab_test(id int,name varchar(10));
CREATE TABLE
omm=# insert into tab_test(id,name) values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
INSERT 0 3
omm=# select * from tab_test;
  1 | zhangsan
  2 | lisi
  3 | wangwu
(3 rows)
omm=# savepoint my_savepoint;
SAVEPOINT
omm=# delete from tab_test where id=2;
DELETE 1
omm=# update tab_test set name='lisi' where id=3;
UPDATE 1
omm=# select * from tab_test;
 id |   name   
----+----------
  1 | zhangsan
  3 | lisi
(2 rows)
omm=# rollback to savepoint my_savepoint;
ROLLBACK
omm=# select * from tab_test;
 id |   name   
----+----------
  1 | zhangsan
  2 | lisi
  3 | wangwu
(3 rows)
omm=# release savepoint my_savepoint;
RELEASE
omm=# commit;
COMMIT
复制

4.清理数据

omm=# drop table tab_test;
DROP TABLE
复制

通过学习openGauss事务控制，在实际的练习中更深刻的了解事务概念。事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。保存点是事务中的一个特殊记号，它允许将那些在它建立后执行的命令全部回滚，把事务的状态恢复到保存点所在的时刻。