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

本次课学习的内容是openGauss事务控制。

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。

先进入实训环境，输入su - omm命令和密码连接openGauss。

root@modb:~# 
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 WRITE;
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 t1                                                
(t_id int);
CREATE TABLE
omm=# set local transaction isolation level repeatable read read only;
SET
 
omm=# show transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

omm=# insert into t1 values(54);
ERROR:  cannot execute INSERT in a read-only transaction
omm=# commit;
ROLLBACK
复制

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

omm=# START TRANSACTION;
START TRANSACTION
omm=# CREATE TABLE t2 (t_id int,t_name char(20));
CREATE TABLE
omm=# insert into t2 values(55,'test1');
INSERT 0 1
omm=# delete from t2 where t_name='test1';
DELETE 1
omm=# insert into t2 values(100,'test2');
INSERT 0 1
omm=# update t2 set  t_name='test20' where t_name='test2';
UPDATE 1
omm=# savepoint my_savepoint;
SAVEPOINT
omm=# insert into t2 values(1001,'test3');
INSERT 0 1
omm=# select * from t2;
 t_id |        t_name        
------+----------------------
  100 | test20              
 1001 | test3               
(2 rows)

omm=# rollback to savepoint my_savepoint;
ROLLBACK
omm=# select * from t2;
 t_id |        t_name        
------+----------------------
  100 | test20              
(1 row)

omm=# release savepoint my_savepoint;
RELEASE
omm=# insert into t2 values(200,'test4');
INSERT 0 1
omm=# select * from t2;
 t_id |        t_name        
------+----------------------
  100 | test20              
  200 | test4               
(2 rows)
复制

4、清理数据

omm=# delete from t2;
DELETE 2
omm=# commit;  
ROLLBACK
复制