1.以默认方式启动事务1，修改事务隔离级别，查看transaction_isolation
start transaction;
show transaction_isolation;
set local transaction isolation level repeatable read read write;
show transaction_isolation;
commit;
omm=# start transaction;
START TRANSACTION
omm=#
omm=# show transaction_isolation;
transaction_isolation
-----------------------
read committed
(1 row)
omm=#
omm=# set local transaction isolation level repeatable read read write;
SET
omm=#
omm=# show transaction_isolation;
transaction_isolation
-----------------------
repeatable read
(1 row)
omm=#
omm=# commit;
COMMIT
2.以读写方式启动事务2，创建新表，修改事务为只读事务，查看transaction_read_only，并向表中插入记录
start transaction isolation level read committed read write;
create table testdb (id int);
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
show transaction_read_only;
insert into testdb values (1);
select * from testdb;
commit;
omm=# start transaction isolation level read committed read write;
START TRANSACTION
omm=# create table testdb (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 testdb values (1);
ERROR: cannot execute INSERT in a read-only transaction
omm=# select * from testdb;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
omm=# commit;
ROLLBACK
omm=#
3.启动事务3，对表进行增删改查，并用到创建savepoint，回滚savepoint和删除savepoint
start transaction;
create table testdb (id int);
insert into testdb values (1),(2),(3);
savepoint my_savepint;
select * from testdb;
delete from testdb where id=2;
select * from testdb;
rollback to savepoint my_savepint;
select * from testdb;
release savepoint my_savepint;
delete from testdb where id=2;
commit;
omm=# start transaction;
START TRANSACTION
omm=# create table testdb (id int);
CREATE TABLE
omm=# insert into testdb values (1),(2),(3);
INSERT 0 3
omm=# savepoint my_savepint;
SAVEPOINT
omm=# select * from testdb;
id
----
1
2
3
(3 rows)
omm=# delete from testdb where id=2;
DELETE 1
omm=# select * from testdb;
id
----
1
3
(2 rows)
omm=# ROLLBACK
omm=# select * from testdb;
rollback to savepoint my_savepint;
id
----
1
2
3
(3 rows)
omm=# release savepoint my_savepint;
RELEASE
omm=# delete from testdb where id=2;
DELETE 1
omm=# commit;
COMMIT
omm=#
4.清理数据
drop table testdb;
