openGauss每日一练第十六天

openGauss每日一练第十六天

学习地址

https://www.modb.pro/course/133

学习目标

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

课后作业

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

--SQL文本：
start transaction;
set local transaction isolation level repeatable read;
show transaction_isolation;
commit;

omm=# start transaction;
START TRANSACTION
omm=# set local transaction isolation level repeatable read;
SET
omm=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)
omm=# commit;
COMMIT
omm=

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

--SQL文本：
begin;
set transaction read write;
create table emp (
id integer,
name char(20)
);
set transaction read only;
show transaction_read_only;
insert into emp values (1,'zhang');

omm=# begin;
BEGIN
omm=# set transaction read write;
SET
omm=# create table emp (
omm(# id integer,
omm(# name char(20)
omm(# );
CREATE TABLE
omm=# set transaction read only;
SET
omm=# show transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

omm=# insert into emp values (1,'zhang');
ERROR:  cannot execute INSERT in a read-only transaction
omm=# commit;
ROLLBACK
omm=# 
    

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

--SQL文本：
begin;
create table emp (
id integer,
name char(20)
);
insert into emp values (1,'zhang');
insert into emp values (2,'wang');
savepoint point1;
delete emp where id=1;
rollback to savepoint point1;
update emp set name='zhao' where id=1;
release savepoint point1;
select * from emp;
commit;

omm=# begin;
BEGIN
omm=# create table emp (
omm(# id integer,
omm(# name char(20)
omm(# );
CREATE TABLE
omm=# insert into emp values (1,'zhang');
INSERT 0 1
omm=# insert into emp values (2,'wang');
INSERT 0 1
omm=# savepoint point1;
SAVEPOINT
omm=# delete emp where id=1;
DELETE 1
omm=# rollback to savepoint point1;
ROLLBACK
omm=# update emp set name='zhao' where id=1;
UPDATE 1
omm=# release savepoint point1;
RELEASE
omm=# select * from emp;
 id |         name         
----+----------------------
  2 | wang                
  1 | zhao                
(2 rows)
omm=# commit;
COMMIT
omm=# 

4.清理数据

--SQL文本：
begin;
drop table emp;
commit;

omm=# begin;
BEGIN
omm=# drop table emp;
DROP TABLE
omm=# commit;
COMMIT
omm=#