PG中的自动提交（AUTOCOMMIT）
Tags： AUTOCOMMITPG事务
Oracle中sqlplus里面执行DML语句，是需要提交commit；若错了，也可以回滚rollback； PostgreSQL psql里面默认是自动提交；执行完就马上提交，不能回滚，但是可以关闭自动提交。
AutoCommit基于客户端（psql、pgadmin等等）SESSION连接参数AUTOCOMMIT，数据库SERVER端不进行控制。
SET AUTOCOMMIT
SET AUTOCOMMIT — 设置当前会话的自动提交行为。
大纲
1 | SET AUTOCOMMIT { = | TO } { ON | OFF } |
描述
SET AUTOCOMMIT设置当前数据库会话的自动提交行为。默认情况下，嵌入式 SQL 程序不\在自动提交模式中，因此需要显式地发出COMMIT。这个命令可以把会话改成自动提交模式，这样每一个单独的语句都会被隐式提交。
兼容性
SET AUTOCOMMIT是 PostgreSQL ECPG 的扩展。
注意
这里的AUTOCOMMIT是大写的，不能使用小写。如果使用小写，虽不会报错，但会使该命令无效。
一些配置方式
1.检查当前AUTOCOMMIT参数值
1 2 | postgres=# \echo :AUTOCOMMIT on |
2.修改当前AUTOCOMMIT参数值为OFF
1 | postgres=# \set AUTOCOMMIT off |
3.检查修改的AUTOCOMMIT参数值是否为OFF
1 2 | postgres=# \echo :AUTOCOMMIT off |
4.仅限于psql工具
客户端所在系统用户执行以下语句，家目录下生成.psqlrc
1 | $ echo "\set AUTOCOMMIT off" >>$HOME/.psqlrc |
BEGIN
BEGIN — 开始一个事务块
大纲
1 2 3 4 5 6 7 | BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] 其中 transaction_mode 是以下之一： ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY [ NOT ] DEFERRABLE |
描述
BEGIN开始一个事务块，也就是说所有 BEGIN命令之后的所有语句将被在一个 事务中执行，直到给出一个显式的COMMIT或 者ROLLBACK。默认情况下（没有 BEGIN）， PostgreSQL在 “自动提交”模式中执行事务，也就是说每个语句都 在自己的事务中执行并且在语句结束时隐式地执行一次提交（如果执 行成功，否则会完成一次回滚）。
在一个事务块内的语句会执行得更快，因为事务的开始/提交也要求可观 的 CPU 和磁盘活动。在进行多个相关更改时，在一个事务内执行多个语 句也有助于保证一致性：在所有相关更新还没有完成之前，其他会话将不 能看到中间状态。
如果指定了隔离级别、读/写模式或者延迟模式，新事务也会有那些特性， 就像执行了SET TRANSACTION一样。
参数
WORKTRANSACTION可选的关键词。它们没有效果。
这个语句其他参数的含义请参考 SET TRANSACTION。
注解
START TRANSACTION具有和BEGIN 相同的功能。
在已经在一个事务块中时发出BEGIN将惹出一个警告 消息。事务状态不会被影响。要在一个事务块中嵌套事务，可以使用保 存点（见SAVEPOINT）。
由于向后兼容的原因，连续的 transaction_modes 之间的逗号可以被省略。
示例
开始一个事务块：
1 | BEGIN; |
兼容性
BEGIN是一种 PostgreSQL语言扩展。它等效于 SQL 标准的命令START TRANSACTION，它的参考页 包含额外的兼容性信息。
DEFERRABLE transaction_mode 是一种PostgreSQL语言扩展。
附带地，BEGIN关键词被用于嵌入式 SQL 中的一种 不同目的。在移植数据库应用时，我们建议小心对待事务语义。
另见
COMMIT, ROLLBACK, START TRANSACTION, SAVEPOINT
示例
默认情况下，postgresql是自动提交的，可以使用begin;或\set AUTOCOMMIT off避免自动提交。
示例一
默认开启AUTOCOMMIT：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | postgres=# create table test_auto_commit(id int); CREATE TABLE postgres=# \echo :AUTOCOMMIT on postgres=# insert into test_auto_commit values (1111); INSERT 0 1 postgres=# update test_auto_commit SET id = 1000 where id = 1111; UPDATE 1 postgres=# select * from test_auto_commit ; id ------ 1000 (1 row) postgres=# delete from test_auto_commit where id = 1000; DELETE 1 postgres=# select * from test_auto_commit ; id ---- (0 rows) |
结论：psql默认开启AUTOCOMMIT参数为on，INSERT、UPDATE、DELETE均无需显式输入commit命令，由客户端SESSION自动添加;
下边关闭AUTOCOMMIT做测试：
1 2 3 4 5 6 7 8 | postgres=# create table test_auto_commit(id int); CREATE TABLE postgres=# \set AUTOCOMMIT off postgres=# \echo :AUTOCOMMIT off |
INSERT验证
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | postgres=# select * from test_auto_commit ; id ---- (0 rows) postgres=*# insert into test_auto_commit values (1111); INSERT 0 1 postgres=*# select * from test_auto_commit ; id ------ 1111 (1 row) postgres=*# rollback ; ROLLBACK postgres=# select * from test_auto_commit ; id ---- (0 rows) |
UPDATE验证
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | postgres=# insert into test_auto_commit values (1111); INSERT 0 1 postgres=*# commit; COMMIT postgres=*# select * from test_auto_commit ; id ------ 1111 (1 row) postgres=# update test_auto_commit SET id = 1000 where id = 1111; UPDATE 1 postgres=*# select * from test_auto_commit ; id ------ 1000 (1 row) postgres=*# rollback; ROLLBACK postgres=# select * from test_auto_commit ; id ------ 1111 (1 row) |
DELETE示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | postgres=# select * from test_auto_commit ; id ------ 1111 (1 row) postgres=*# delete from test_auto_commit where id = 1111; DELETE 1 postgres=*# select * from test_auto_commit ; id ---- (0 rows) postgres=*# rollback; ROLLBACK postgres=# select * from test_auto_commit ; id ------ 1111 (1 row) postgres=*# delete from test_auto_commit where id = 1111; DELETE 1 postgres=*# commit; COMMIT postgres=# select * from test_auto_commit ; id ---- (0 rows) |
结论：修改psql默认开启AUTOCOMMIT参数为off，INSERT、UPDATE、DELETE均需显式输入commit命令，由客户端SESSION不会自动添加;
示例二
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | -- 1）使用begin;命令 postgres=# begin; BEGIN postgres=# insert into test values(2,2); INSERT 0 1 postgres=# select * from test; id | name ----+------ 1 | 1 | 2 2 | 2 (3 行记录) postgres=# rollback; ROLLBACK postgres=# select * from test; id | name ----+------ 1 | 1 | 2 (2 行记录) -- 2）直接关闭自动提交的功能 postgres=# \set AUTOCOMMIT off postgres=# postgres=# postgres=# insert into test values(2,2); INSERT 0 1 postgres=# select * from test; id | name ----+------ 1 | 1 | 2 2 | 2 (3 行记录) postgres=# rollback; ROLLBACK postgres=# select * from test; id | name ----+------ 1 | 1 | 2 (2 行记录) |
倒数第二个代码段中感觉不对啊。
已经关闭了自动提交，创建test_auto_commit、向test_auto_commit插入1111，rollback之后。
再查询test_auto_commit的时候，应该提示表不存在才对，因为整个事务块都回滚了。
不应该提示：
postgres=# rollback ;
ROLLBACK
postgres=# select * from test_auto_commit ;
id
(0 rows)
已修复，感谢提醒。