![02fed8676cf2d224472dfb0d0096a317.png](https://i-blog.csdnimg.cn/blog_migrate/fc7ed71a819cee4ba79ac5299471199c.jpeg)
事务是数据库的最基本概念,在PostgreSQL中使用begin;end;命令可以开启和提交事务,当然这是最常见的PostgreSQL事务,除此之外PostgreSQL中还有子事务、多事务、2PC事务的概念。这篇博客,我将会演示这些PostgreSQL事务的出现场景和内核实现方式。
普通事务
使用PostgreSQL客户端连接至PostgreSQL服务器,是默认开启事务自动提交的,也就是说每执行一条DML会自动完成提交过程。用户可以使用set AUTOCOMMIT off命令关闭自动提交,也可以使用begin命令开启一个事务块。
echo :AUTOCOMMIT
create table t1(i int);
insert into t1 values(1);
rollback;
select * from t1;
set AUTOCOMMIT off
echo :AUTOCOMMIT
insert into t1 values(2);
rollback;
select * from t1;
insert into t1 values(22);
commit;
select * from t1;
commit;
set AUTOCOMMIT on
echo :AUTOCOMMIT
begin;
insert into t1 values(3);
rollback;
select * from t1;
begin;
insert into t1 values(33);
commit;
select * from t1;
postgres=# echo :AUTOCOMMIT
on
postgres=# create table t1(i int);
CREATE TABLE
postgres=# insert into t1 values(1);
INSERT 0 1
postgres=# rollback;
2020-06-08 15:15:50.261 CST [29689] WARNING: there is no transaction in progress
WARNING: there is no transaction in progress
ROLLBACK
postgres=# select * from t1;
i
---
1
(1 row)
postgres=#
--自动提交开启的情况下,我们执行的DML语句已成功提交,rollback没有对我们造成影响
postgres=# set AUTOCOMMIT off
postgres=# echo :AUTOCOMMIT
off
postgres=# insert into t1 values(2);
INSERT 0 1
postgres=# rollback;
ROLLBACK
postgres=# select * from t1;
i
---
1
(1 row)
postgres=# insert into t1 values(22);
INSERT 0 1
postgres=# commit;
COMMIT
postgres=# select * from t1;
i
----
1
22
(2 rows)
postgres=#
--自动提交关闭的情况下,我们执行的DML语句都被rollback回滚,也可被commit提交
postgres=# commit;
COMMIT
postgres=#
postgres=# set AUTOCOMMIT on
postgres=# echo :AUTOCOMMIT
on
postgres=# begin;
BEGIN
postgres=# insert into t1 values(3);
INSERT 0 1
postgres=# rollback;
ROLLBACK
postgres=# select * from t1;
i
----
1
22
(2 rows)
postgres=# begin;
BEGIN
postgres=# insert into t1 values(33);
INSERT 0 1
postgres=# commit;
COMMIT
postgres=# select * from t1;
i
----
1
22
33
(3 rows)
postgres=#
----在begin事务块,我们执行的DML语句可以被rollback回滚,也可被commit提交
现在我们借助pageinspa