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

学习目标

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

连接数据库

root@modb:~# su - omm
omm@modb:~$ gsql -r
复制

课程作业

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

-以默认方式启动事务

omm=# START TRANSACTION;
START TRANSACTION
omm=# select * from pg_class limit 1;
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltu
ples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | r
elhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | r
elhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid |      relacl 
      | reloptions | relreplident | relfrozenxid64 | relbucket | relbucketkey 
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+------
-----+---------------+---------------+---------------+---------------+-------------+----------------+--------------+--
-----------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+--
--------------+----------------+----------+------------------+----------------+----------+--------------+-------------
------+------------+--------------+----------------+-----------+--------------
 pg_statistic |           11 |   11334 |         0 |       10 |     0 |       13689 |             0 |       18 |      
 476 |            18 |          2840 |             0 |             0 |           0 |              0 |            0 | t
           | f           | p              | r       |       29 |         0 | f          | f          | f           | f
              | f              | 0        | f                | f              | n        | 0            | {omm=arwdDxt
/omm} |            | n            |           7891 |           | 
(1 row)

omm=# 
omm=# END;
omm=# COMMIT
复制

修改事务隔离级别，查看transaction_isolation

修改事务的隔离级别为READ COMMITTED

START TRANSACTION;
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
show transaction_read_only;
select * from pg_class limit 1;
create schema tpcds10;
END;
复制

omm=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)
复制

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

start transaction isolation level repeatable read read write;
create table t1 (a int, b int);
set local transaction isolation level repeatable read read only;
show transaction_read_only;
insert into t1 values(10, 20);
复制

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

omm=# set local transaction isolation level repeatable read read write;
SET
omm=# start transaction;
create table t2 (a int, b int);
insert into t2 values(10, 20);
savepoint my_savepoint;
update t2 set a = 100 where a = '10';
rollback to savepoint my_savepoint;
release savepoint my_savepoint;
insert into t2 values(30, 30);
commit;
select * from t2;START TRANSACTION
omm=# CREATE TABLE
omm=# INSERT 0 1
omm=# SAVEPOINT
omm=# UPDATE 1
omm=# ROLLBACK
omm=# RELEASE
omm=# INSERT 0 1
omm=# COMMIT
omm=# 
 a  | b  
----+----
 10 | 20
 30 | 30
(2 rows)
复制

4.清理数据

omm=# drop table t2;
DROP TABLE
复制