学习目标
学习openGauss事务控制
事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位
课程学习实操
连接数据库
#第一次进入等待15秒 #数据库启动中... su - omm gsql -r复制
1.事务控制
–通过START TRANSACTION和BEGIN语法启动事务
-以默认方式启动事务
START TRANSACTION;select * from pg_class limit 1; END;omm=# START TRANSACTION; omm=# START TRANSACTION omm=# select * from pg_class limit 1; relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | r elallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | rel isshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassu bclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relrepliden t | 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=# END; COMMIT omm=#
–开启一个事务，设置事务的隔离级别为READ
BEGIN; SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY; show transaction_read_only; select * from pg_class limit 1; create schema tpcds10; commit;omm=# BEGIN; BEGIN omm=# SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY; SET omm=# show transaction_read_only; transaction_read_only ----------------------- on (1 row) omm=# select * from pg_class limit 1; relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | r elallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | rel isshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassu bclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relrepliden t | 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=# create schema tpcds10; ERROR: cannot execute CREATE SCHEMA in a read-only transaction omm=# commit; ROLLBACK omm=# omm=#复制
–以隔离级别为repeatable read，读/写方式启动事务
START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE; show transaction_isolation; show transaction_read_only; select * from pg_class limit 1; create schema tpcds10; rollback;omm=# START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE; START TRANSACTION omm=# show transaction_isolation; transaction_isolation ----------------------- repeatable read (1 row) omm=# show transaction_read_only; transaction_read_only ----------------------- off (1 row) omm=# select * from pg_class limit 1; relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | r elallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | rel isshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassu bclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relrepliden t | relfrozenxid64 | relbucket | relbucketkey --------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+-- -------------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+---- ---------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+--------- pg_statistic | 11 | 11334 | 0 | 10 | 0 | 13689 | 0 | 18 | 476 | 18 | 2840 | 0 | 0 | 0 | 0 | 0 | t | f -------+----------+------------------+----------------+----------+--------------+-------------------+------------+------------ --+----------------+-----------+-------------- | 0 | f | f | n | 0 | {omm=arwdDxt/omm} | | n | 7891 | | (1 row) | p | r | 29 | 0 | f | f | f | f | f omm=# create schema tpcds10; CREATE SCHEMA omm=# rollback; ROLLBACK omm=#
–事务回滚，schema没有创建成功
\dn+ tpcds10;omm=# \dn+ tpcds10; List of schemas Name | Owner | Access privileges | Description ------+-------+-------------------+------------- (0 rows) omm=#复制
2.savepoint
–保存点是事务中的一个特殊记号，它允许将那些在它建立后执行的命令全部回滚，把事务的状态恢复到保存点所在的时刻
START TRANSACTION; INSERT INTO table1 VALUES (1);omm=# start transaction; START TRANSACTION omm=# insert into table1 values(1); INSERT 0 1复制
–建立保存点
SAVEPOINT my_savepoint;INSERT INTO table1 VALUES (2);omm=# SAVEPOINT my_savepoint; SAVEPOINT omm=# insert into table1 values(2); INSERT 0 1复制
–回滚保存点
ROLLBACK TO SAVEPOINT my_savepoint;omm=# select * from table1; id ---- 1 2 (2 rows) omm=# rollback to savepoint my_savepoint; ROLLBACK omm=# select * from table1; id ---- 1 (1 row)复制
–删除保存点
RELEASE SAVEPOINT my_savepoint;INSERT INTO table1 VALUES (3); COMMIT;omm=# RELEASE SAVEPOINT my_savepoint; RELEASE omm=# insert into table1 values(3); INSERT 0 1 omm=# commit; COMMIT复制
–查询表的内容，会同时看到1和3,不能看到2，因为2被回滚
SELECT * FROM table1;omm=# select * from table1; id ---- 1 3 (2 rows)复制
课程作业
1.以默认方式启动事务1，修改事务隔离级别，查看transaction_isolation
omm=# start transaction;
START TRANSACTION
ommm=# select * from t1;
id | no
----+----
1 | 1
(1 row)
omm=# end;
COMMIT
omm=# show transaction_isolation;
transaction_isolation
-----------------------
read committed
(1 row)
omm=#
omm=# START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
START TRANSACTION
omm=# show transaction_isolation;
transaction_isolation
-----------------------
repeatable read
(1 row)
2.以读写方式启动事务2，创建新表，修改事务为只读事务，查看transaction_read_only，并向表中插入记录
omm=# start transaction read write;
START TRANSACTION
omm=# create table t2 (id int);
CREATE TABLE
omm=# SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
SET
omm=# show transaction_read_only;
transaction_read_only
-----------------------
on
(1 row)
omm=# insert into t2 values(1);
ERROR: cannot execute INSERT in a read-only transaction
omm=# commit;
ROLLBACK
omm=#
3.启动事务3，对表进行增删改查，并用到创建savepoint，回滚savepoint和删除savepoint
omm=# start transaction;
START TRANSACTION
omm=# create table t3(id int);
CREATE TABLE
omm=# insert into t3 values(1);
INSERT 0 1
omm=# savepoint my_savepoint;
SAVEPOINT
omm=# insert into t3 values(2);
INSERT 0 1
omm=# select * from t3;
id
----
1
2
(2 rows)
omm=# rollback to savepoint my_savepoint;
ROLLBACK
postgres=# select * from t3;
id
----
1
(1 row)
omm=# insert into t3 values(3);
INSERT 0 1
postgres=# select * from t3;
id
----
1
3
(2 rows)
omm=# release savepoint my_savepoint;
RELEASE
omm=# select * from t3;
id
----
1
3
(2 rows)
omm=# savepoint my_savepoint2;
SAVEPOINT
omm=# update t3 set id=4 where id=3;
UPDATE 1
omm=# select * from t3;
id
----
1
4
(2 rows)
omm=# rollback to savepoint my_savepoint2;
ROLLBACK
omm=# select * from t3;
id
----
1
3
(2 rows)
omm=# release savepoint my_savepoint2;
RELEASE
omm=# commit;
COMMIT
postgres=# select * from t3;
id
----
1
3
(2 rows)
postgres=#
4.清理数据
omm=# drop table t1,t2,t3;
DROP TABLE
学习总结
通过本节课的学习，我掌握了事务的基本操作和事务隔离级别的控制，savepoint的使用。
