openGauss每日一练第16天|课后练习-事务控制

数据库连接及环境检查

切换用户至omm用户（gaussdb的所有者）

su - omm

gsql连接数据库,并检查当前会话连接信息:

gsql -r
\conninfo

查看当前函数

show transaction_isolation;

课后练习

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

show transaction_isolation;

START TRANSACTION; 
select * from products; 
END;

show transaction_isolation;

START TRANSACTION;
SET LOCAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
show transaction_isolation;
end;

show transaction_isolation;

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

START TRANSACTION READ WRITE;
show transaction_isolation;
show transaction_read_only;
create table products1
(product_id integer,
product_name char(30),
category char(20)
);
SET LOCAL TRANSACTION read only;
show transaction_isolation;
show transaction_read_only;
INSERT INTO products VALUES    
(1601,'lamaze','toys'); 
show transaction_isolation;
show transaction_read_only;
end;
show transaction_isolation;
show transaction_read_only;
--数据插入失败，read only模式

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

create table products1
(product_id integer,
product_name char(30),
category char(20)
);

START TRANSACTION;
INSERT INTO products1 VALUES (1601,'lamaze','toys'); 
show transaction_isolation;
select * from products1;
SAVEPOINT p1;
INSERT INTO products1 VALUES (1700,'wait interface','Books');
select * from products1;
ROLLBACK TO SAVEPOINT p1;
select * from products1;
RELEASE SAVEPOINT p1;
INSERT INTO products1 VALUES (1700,'wait interface','Books');
end;
select * from products1;

4.清理数据

drop table products;
drop table products1;
\d

附：事务控制相关官网文档

openGauss 事务控制及操作