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

学习目标

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

课程学习

连接openGauss
#第一次进入等待15秒
#数据库启动中…

su - omm
gsql -r

课后作业

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

start transaction;
set local transaction isolation level read committed read only;
show transaction_isolation;

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

start transaction isolation level repeatable read read write;
create table table2(a int);

set local transaction isolation level repeatable read read only; 

show transaction_read_only;

insert into table2 values(1);

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

start transaction;


create table table3(a int);
insert into table3 values(1);
savepoint my_savepoint;
update table3 set a = 5;
rollback to savepoint my_savepoint;
release savepoint my_savepoint;
insert into table3 values(2);
commit;

select * from table3;

4.清理数据

DROP table table3;

作业执行结果

omm=# start transaction;

START TRANSACTION
omm=# set local transaction isolation level read committed read only;
SET
omm=# show transaction_isolation;
(1 row)

omm=# 
omm=# 
 transaction_isolation 
-----------------------
 read committed
omm=# 
omm=# 
omm=# start transaction isolation level repeatable read read write;
WARNING:  there is already a transaction in progress
START TRANSACTION
omm=# create table table2(a int);
CREATE TABLE
omm=# 
omm=# set local transaction isolation level repeatable read read only; 
SET
omm=# 
omm=# show transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

omm=# 
omm=# insert into table2 values(1);
ERROR:  cannot execute INSERT in a read-only transaction
omm=# 
omm=# 
omm=# rollback;
ROLLBACK
omm=# 
omm=# 
omm=# start transaction;
START TRANSACTION
omm=# 
omm=# 
omm=# create table table3(a int);
omm=# CREATE TABLE
insert into table3 values(1);
INSERT 0 1
omm=# savepoint my_savepoint;
SAVEPOINT
omm=# update table3 set a = 5;
UPDATE 1
omm=# rollback to savepoint my_savepoint;
ROLLBACK
omm=# release savepoint my_savepoint;
RELEASE
omm=# insert into table3 values(2);
INSERT 0 1
omm=# commit;
COMMIT
omm=# 
omm=# select * from table3;
 a 
---
 1

 2
(2 rows)
omm=# 
omm=# 
omm=# 
omm=# DROP table table3;
DROP TABLE