openGauss每日一练第16天 课程笔记和作业

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

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

学习笔记

• 通过START TRANSACTION和BEGIN语法启动事务
  • 隔离级别为READ COMMITTED，访问模式为READ ONLY
  • 隔离级别为repeatable read，读/写方式

START TRANSACTION;
END;

BEGIN;
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
commit;

START TRANSACTION ISOLATION LEVEL repeatable read READ WRITE;
rollback;

• 保存点的建立、回滚与删除

START TRANSACTION;
SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint;
RELEASE SAVEPOINT my_savepoint;
COMMIT;

课后作业

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

omm=# start transaction;
START TRANSACTION
omm=# set local transaction isolation level repeatable read read write;
SET
omm=# show transaction_isolation;
(1 row)

omm=#  transaction_isolation 
-----------------------
 repeatable read

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

omm=# end;
COMMIT

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

omm=# start transaction isolation level read committed read write;
omm=# START TRANSACTION

omm=# create table product(product_id int,product_name char(30));
CREATE TABLE
omm=# 
omm=# set local transaction isolation level read committed read only;
SET
omm=# insert into product values(1,'phone');
ERROR:  cannot execute INSERT in a read-only transaction
omm=# end;
ROLLBACK

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

omm=# create table product(product_id int,product_name char(30));
omm=# CREATE TABLE

omm=# start transaction;
START TRANSACTION
omm=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

omm=# show transaction_read_only;
(1 row)

omm=#  transaction_read_only 
-----------------------
 off

omm=# insert into product values(1,'computer');
INSERT 0 1
omm=# select * from product;
 product_id |          product_name          
------------+--------------------------------
          1 | computer                      
(1 row)

omm=# update product set product_name='phone' where product_id = 1;
UPDATE 1
omm=# select * from product;
 product_id |          product_name          
------------+--------------------------------
          1 | phone                         
(1 row)

omm=# savepoint my_sp1;
omm=# SAVEPOINT

omm=# insert into product values(2,'light');
INSERT 0 1
omm=# select * from product;
 product_id |          product_name          
------------+--------------------------------
          1 | phone                         
          2 | light                         
(2 rows)

omm=# rollback to savepoint my_sp1;
ROLLBACK
omm=# select * from product;
(1 row)

omm=#  product_id |          product_name          
------------+--------------------------------
          1 | phone                         

omm=# release savepoint my_sp1;
omm=# RELEASE

omm=# insert into product values(3,'mouse');
INSERT 0 1
omm=# commit;
COMMIT
omm=# select * from product;
 product_id |          product_name          
------------+--------------------------------
          1 | phone                         
          3 | mouse                         
(2 rows)

4.清理数据

omm=# drop table product;
DROP TABLE

学习体会

• transaction_isolation
  • serializable
  • read committed（默认值）
  • repeatable read
  • read uncommitted
  • default
• transaction_read_only
  • on
  • off
• ACID
  • atomicity
  • consistency
  • isolation
  • durability

学习资源

• 华为openGauss openGauss事务
• openGauss SQL学习参考资料
• 每日一练：openGauss数据库在线实训课程
• openGauss每日一练 | 21期养成好习惯，提升技术能力！
• 墨天轮Markdown编辑器使用介绍
• 墨天轮数据库在线实训平台V1.0操作手册
• 墨天轮数据社区

欢迎各位同学一起来交流学习心得！