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

👉openGauss SQL学习参考

学习目标

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

课程学习

连接openGauss

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

1.事务控制

--通过 START TRANSACTION 和 BEGIN 语法启动事务

--以默认方式启动事务
mydb=# 
mydb=# START TRANSACTION;
START TRANSACTION
mydb=# select relname, relnamespace from pg_class limit 1;
   relname    | relnamespace 
--------------+--------------
 pg_statistic |           11
(1 row)

mydb=# select txid_current();   --当前事务 ID
 txid_current 
--------------
        14540
(1 row)

mydb=# end;
COMMIT
mydb=# select txid_current();
 txid_current 
--------------
        14541
(1 row)

mydb=# 

--开启一个事务，设置事务的隔离级别为 READ COMMITTED，访问模式为 READ ONLY
mydb=# BEGIN;
BEGIN
mydb=# show transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 row)

mydb=# SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
SET
mydb=# show transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

mydb=# select relname, relnamespace from pg_class limit 1;
   relname    | relnamespace 
--------------+--------------
 pg_statistic |           11
(1 row)

mydb=# create schema tpcds10;
ERROR:  cannot execute CREATE SCHEMA in a read-only transaction
mydb=# commit;
ROLLBACK
mydb=# 


--以隔离级别为 repeatable read，读/写方式启动事务
mydb=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

mydb=# START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
START TRANSACTION
mydb=# show transaction_isolation;
 transaction_isolation 
-----------------------
 repeatable read
(1 row)

mydb=# show transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 row)

mydb=# select relname, relnamespace from pg_class limit 1;
   relname    | relnamespace 
--------------+--------------
 pg_statistic |           11
(1 row)

mydb=# create schema tpcds10;
CREATE SCHEMA
mydb=# rollback;
ROLLBACK
mydb=# 

--–事务回滚，schema 没有创建成功
mydb=# \dn+ tpcds10
                         List of schemas
 Name | Owner | Access privileges | Description | WithBlockChain 
------+-------+-------------------+-------------+----------------
(0 rows)

mydb=# 

2.savepoint

--保存点是事务中的一个特殊记号，它允许将那些在它建立后执行的命令全部回滚，把事务的状态恢复到保存点所在的时刻
mydb=# CREATE TABLE table1(a int);
CREATE TABLE
mydb=# START TRANSACTION;
START TRANSACTION
mydb=# INSERT INTO table1 VALUES (1);
INSERT 0 1
mydb=# 

–-建立保存点
mydb=# SAVEPOINT my_savepoint;
SAVEPOINT
mydb=# INSERT INTO table1 VALUES (2);
INSERT 0 1
mydb=# 

--回滚保存点
mydb=# ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK
mydb=# 
mydb=# SELECT * FROM table1;
 a 
---
 1
(1 row)

--–删除保存点
mydb=# RELEASE SAVEPOINT my_savepoint;
RELEASE
mydb=# INSERT INTO table1 VALUES (3);
INSERT 0 1
mydb=# COMMIT;
COMMIT
mydb=# 

--–查询表的内容，会同时看到1和3,不能看到2，因为2被回滚
mydb=# SELECT * FROM table1;
 a 
---
 1
 3
(2 rows)

mydb=# 

课后作业

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

mydb=# START TRANSACTION;
START TRANSACTION
mydb=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

mydb=# SET LOCAL TRANSACTION ISOLATION LEVEL repeatable read ;
SET
mydb=# show transaction_isolation;
 transaction_isolation 
-----------------------
 repeatable read
(1 row)

mydb=# end;
COMMIT
mydb=# 

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

mydb=# START TRANSACTION READ WRITE;
START TRANSACTION
mydb=# show transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 row)

mydb=# create table yhc1(id int);
CREATE TABLE
mydb=# SET LOCAL TRANSACTION READ ONLY;
SET
mydb=# show transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

mydb=# insert into yhc1 values(1);
ERROR:  cannot execute INSERT in a read-only transaction
mydb=# rollback;
ROLLBACK
mydb=# 

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

mydb=# CREATE TABLE yhc1(a int);
CREATE TABLE
mydb=# START TRANSACTION;
START TRANSACTION
mydb=# INSERT INTO yhc1 VALUES(1);
INSERT 0 1 
mydb=# SAVEPOINT yhc1_savepoint1;  --创建 savepoint
SAVEPOINT
mydb=# 
mydb=# update yhc1 set a = 11;
UPDATE 1
mydb=# SAVEPOINT yhc1_savepoint2;   --创建 savepoint
SAVEPOINT
mydb=# ROLLBACK TO SAVEPOINT yhc1_savepoint1;   --回滚 savepoint
ROLLBACK
mydb=# RELEASE SAVEPOINT yhc1_savepoint1;   --删除 savepoint
RELEASE
mydb=# select * from yhc1;
 a 
---
 1
(1 row)

mydb=# 

4.清理数据

mydb=# drop table yhc1;
DROP TABLE
mydb=#