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

openGauss每日一练第16天课后作业

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

start transaction;

select * from pg_indexes limit 1;

show transaction_isolation;

--默认值是read committed

commit;

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

start transaction isolation level read committed read write;

create table test_t1(a int);

SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;

show transaction_isolation;

insert into test_t1 values(1);

--报错，在只读事务中无法向表中插入数据

select * from test_t1;

--报错，事务已经中止了

--ERROR:  current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]

commit;

--实际执行的是rollback，表也没有创建出来

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

begin transaction;

create table test_t1(a int);

insert into test_t1 values (1),(2),(3);

savepoint test_t1_savepoint1;

delete from test_t1 where a=1;

savepoint test_t1_savepoint2;

update test_t1 set a=20 where a=2;

rollback to savepoint test_t1_savepoint2;

--回滚到保存点2

select * from test_t1;

rollback to savepoint test_t1_savepoint1;

--回滚到保存点1

select * from test_t1;

release savepoint test_t1_savepoint1;

insert into test_t1 values (4);

commit;

select * from test_t1;

4.清理数据

drop table test_t1;