学习心得

事务是被原子化的一系列操作. 事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位.
START TRANSACTION
SET LOCAL TRANSACTION ISOLATION LEVEL
END

0.进入系统

su - omm
gsql -r

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

先查看默认值

START TRANSACTION;
SHOW TRANSACTION_ISOLATION;
SHOW

• 回显

transaction_isolation
-----------------------
 read committed
(1 row)

隔离级别修改为REPEATABLE READ

SET LOCAL TRANSACTION ISOLATION LEVEL REPEATABLE READ
SHOW TRANSACTION_ISOLATION;
END;

• 回显

 transaction_isolation
-----------------------
 repeatable read
(1 row)
COMMIT

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

建立表格后设为READ_ONLY

START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE;
CREATE TABLE t1(id int,name char(20));
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
show transaction_read_only;

• 回显

START TRANSACTION
CREATE TABLE
SET
 transaction_read_only
-----------------------
 on
(1 row)

意料中的插入失败

insert into t1 values(1,'XXXX');
END;

• 回显

ERROR:  cannot execute INSERT in a read-only transaction
ROLLBACK

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

TRANSACTION

create table t2(id int,name char(20));
insert into t2 values(1,'XXXX');
insert into t2 values(2,'XXYY');
SAVEPOINT temp_savepoint;
select * from t2;

• 回显

 id |         name
----+----------------------
  1 | XXXX
  2 | XXYY
(2 rows)

CHANGE

delete t2 where id=2;
select * from t2;

• 回显

 id |         name
----+----------------------
  1 | XXXX
(1 row)

ROLLBACK

ROLLBACK TO SAVEPOINT temp_savepoint;
select * from t2;

• 回显

ROLLBACK
 id |         name
----+----------------------
  1 | XXXX
  2 | XXYY
(2 rows)

RELEASE & CHANGE

RELEASE SAVEPOINT temp_savepoint;
INSERT INTO t2 values(3,'ZZZZ');
COMMIT;
SELECT * FROM t2;

• 回显

 id |         name
----+----------------------
  1 | XXXX
  2 | XXYY
  3 | ZZZZ
(3 rows)

4.清理数据

DROP t1;
DROP t2;

• 回显

DROP TABLE
DROP TABLE