#opengauss的第16天
1.以默认方式启动事务1，修改事务隔离级别，查看transaction_isolation
omm=# start transaction;
START TRANSACTION
omm=#
omm=# select * from pg_database ;
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlasts
ysoid | datfrozenxid | dattablespace | datcompatibility | datacl | datfrozenxid64
-----------±-------±---------±------------±------------±--------------±-------------±-------------±--------
------±-------------±--------------±-----------------±---------------------±---------------
template1 | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 |
14176 | 0 | 1663 | PG | {=c/omm,omm=CTc/omm} | 8176
omm=# end;
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_database limit 1;
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlasts
ysoid | datfrozenxid | dattablespace | datcompatibility | datacl | datfrozenxid64
-----------±-------±---------±------------±------------±--------------±-------------±-------------±--------
------±-------------±--------------±-----------------±---------------------±---------------
template1 | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 |
14176 | 0 | 1663 | PG | {=c/omm,omm=CTc/omm} | 8176
(1 row)
omm=# create schema sch1;
ERROR: cannot execute CREATE SCHEMA in a read-only transaction
2.以读写方式启动事务2，创建新表，修改事务为只读事务，查看transaction_read_only，并向表中插入记录
omm=# show transaction_isolation;
omm=# transaction_isolation
read committed
(1 row)
omm=# show transaction_read_only;
transaction_read_only
off
(1 row)
omm=# START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
omm=# START TRANSACTION
omm=# show transaction_isolation;
transaction_isolation
repeatable read
(1 row)
omm=# show transaction_read_only;
transaction_read_only
off
(1 row)
omm=# create table t1(id bigint,name char(40));
CREATE TABLE
omm=#
omm=#
omm=#
omm=# START TRANSACTION ISOLATION LEVEL repeatable read read only;
WARNING: there is already a transaction in progress
START TRANSACTION
omm=# show transaction_isolation;
omm=# transaction_isolation
repeatable read
(1 row)
omm=# show transaction_read_only;
transaction_read_only
on
(1 row)
omm=# insert into t1 values(1,‘opengauss’);
ERROR: cannot execute INSERT in a read-only transaction
omm=# rollback;
omm=# ROLLBACK
3.启动事务3，对表进行增删改查，并用到创建savepoint，回滚savepoint和删除savepoint
omm=# START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
START TRANSACTION
omm=# create table t1(id bigint,name char(40));
CREATE TABLE
omm=# insert into t1 values(1,‘opengauss’);
INSERT 0 1
omm=# insert into t1 values(2,‘postgresql’);
INSERT 0 1
omm=# update t1 set id=100 where id=1;
UPDATE 1
omm=# delete from t1 where id=2;
DELETE 1
omm=# select * from t1;
id | name
-----±-----------------------------------------
100 | opengauss
(1 row)
omm=# savepoint gauss_savepoint;
SAVEPOINT
omm=# insert into t1 values(10,‘mysql’);
INSERT 0 1
omm=#
omm=# select * from t1;
id | name
-----±-----------------------------------------
100 | opengauss
10 | mysql
(2 rows)
omm=# rollback to savepoint gauss_savepoint;
ROLLBACK
omm=# select * from t1;
id | name
-----±-----------------------------------------
100 | opengauss
(1 row)
omm=# release savepoint gauss_savepoint;
RELEASE
omm=# commit;
COMMIT
omm=# select * from t1;
id | name
-----±-----------------------------------------
100 | opengauss
4.清理数据
omm=# drop table t1;
DROP TABLE
omm=# \d
No relations found.
