AUTOCOMMIT – Oracle vs PostgreSQL
目录结构
注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
1、参考书籍：《PostgreSQL数据库内核分析》
2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》
3、PostgreSQL数据库仓库链接，点击前往
4、日本著名PostgreSQL数据库专家 铃木启修 网站主页，点击前往
5、参考书籍：《PostgreSQL中文手册》
6、参考书籍：《PostgreSQL指南：内幕探索》，点击前往
7、参考书籍：《事务处理 概念与技术》
8、AUTOCOMMIT – Oracle vs PostgreSQL，点击前往
1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享 抛砖引玉 一起学习
3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）
5、本文内容基于PostgreSQL master源码开发而成
@
文章快速说明索引
学习目标：
做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。
学习内容：（详见目录）
1、深入理解PostgreSQL数据库之AUTOCOMMIT – Oracle vs PostgreSQL
学习时间：
2024-03-05 12:01:39 星期二
学习产出：
1、PostgreSQL数据库基础知识回顾 1个
2、CSDN 技术博客 1篇
3、PostgreSQL数据库内核深入学习
注：下面我们所有的学习环境是Centos8+PostgreSQL master+Oracle19C+MySQL8.0
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)
postgres=#
#-----------------------------------------------------------------------------#
SQL> select * from v$version;
BANNER Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
BANNER_FULL Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
CON_ID 0
#-----------------------------------------------------------------------------#
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.06 sec)
mysql>
功能使用背景说明
Oracle 和 PostgreSQL 在事务模型上存在显着差异，AUTOCOMMIT 就是这些差异之一。我们看到许多客户成功迁移了代码，但仍然遇到行为变化，甚至与事务控制相关的运行时错误。由于 Oracle 和 PostgreSQL 之间的 AUTOCOMMIT 设置不同，因此会发生许多问题。为了确保成功迁移，绝对有必要了解 Oracle 和 PostgreSQL 之间 AUTOCOMMIT 的区别。
Oracle 中使用 SQL*Plus 的简单演示，如下：
SQL*Plus 是一种交互式工具，可作为即时客户端使用，默认情况下也可与每个 Oracle 数据库安装一起使用。这里使用 2 个 SQL*Plus 会话来进行演示。
SQL*Plus session 1:
采用了一个空表 AUTOC_TEST。在第一个会话中，在表中插入一行，如下：
SQL> INSERT INTO AUTOC_TEST VALUES(10);
1 row created.
SQL*Plus session 2:
在不关闭第一个会话的情况下，将检查第二个会话中表的行数。我们看到它在第二个会话中显示“0 行”，即使我们在第一个会话中插入了一行。
SQL> SELECT COUNT(*) FROM AUTOC_TEST;
COUNT(*)
----------
0
使用 psql 在 PostgreSQL 中进行相同的演示，如下：
现在让我们使用 2 个 psql 会话在 PostgreSQL 中执行相同的测试。psql 是 PostgreSQL 客户端应用程序和类似于 Oracle 的 SQL*Plus 的交互式工具。
PSQL session 1:
# 空表 autoc_test 并在第一个会话中插入了一行。
postgres=# INSERT INTO autoc_test VALUES(10);
INSERT 0 1
PSQL session 2:
postgres=# SELECT COUNT(*) FROM autoc_test;
count
-------
1
(1 row)
但在 PostgreSQL 中，我们发现与 Oracle 相比，它表现出不同的行为。我们在第一个会话中插入一行，然后检查第二个会话中表的行数，而不关闭第一个会话。与 Oracle 不同的是，我们能够在第二个会话中看到插入的行。
要理解这种差异，我们需要了解数据库中的 AUTOCOMMIT。
数据库中的 AUTOCOMMIT 是什么？
数据库中的自动提交是指每条SQL语句在执行时自动提交，使单个语句成为独立的事务。在自动提交模式下，每个用户语句都被视为一个单独的事务，如果执行成功，则在语句末尾自动执行提交。如果出现错误，语句执行期间所做的任何更改都会回滚。
一、Oracle 中有 AUTOCOMMIT 吗？
Oracle 没有自动提交模式。在 Oracle 中，AUTOCOMMIT 设置不是您直接在数据库服务器本身内设置的功能。相反，自动提交行为通常在客户端或驱动程序级别进行控制。例如，许多与 Oracle 数据库交互的 SQL 客户端、开发工具和编程语言驱动程序可以配置为在执行每个 SQL 语句后自动提交事务。
为了管理连接到 Oracle 的应用程序中的自动提交行为，我们通常会查看正在使用的特定客户端或驱动程序的文档，以了解如何启用或禁用自动提交。例如，在Java的JDBC（Java数据库连接）中，我们可以通过在Connection对象上调用setAutoCommit（true）或setAutoCommit（false）来控制这种行为。
默认情况下，Oracle 在事务模式下运行，其中事务中 SQL 语句（例如 INSERT、UPDATE、DELETE）所做的更改不会在数据库中永久保存，直到发出显式 COMMIT 语句。
这就是为什么在上面的 Oracle 示例中，我们无法看到第一个会话插入的行对第二个会话可见。这是因为，Oracle 完全控制用户/客户端何时提交，并且 Oracle 服务器中没有 AUTOCOMMIT 概念。为了能够看到第二个会话中的更改，我们需要在第一个会话中提交更改或在客户端级别打开自动提交。
DDL 在 Oracle 中完成之前和之后隐式 COMMIT
Oracle 中的 DDL 语句在开始之前自动提交会话中任何未完成的事务，并在完成后再次提交。这意味着，如果在执行 DDL 语句之前有任何未提交的 DML 操作（如 INSERT、UPDATE、DELETE），Oracle 将自动提交这些更改。同样，一旦 DDL 操作完成，它就会再次提交，使 DDL 更改永久化。
作为示例，让我们再次使用 2 个 SQL*Plus 会话。在执行此测试之前，我们已截断了 AUTOC_TEST 表。
Session 1:
SQL> INSERT INTO AUTOC_TEST VALUES(10);
1 row created.
Session 2:
SQL> SELECT COUNT(*) FROM AUTOC_TEST;
COUNT(*)
----------
0
我们看到我们从会话 1 中插入了一行，但在会话 2 中看不到该变化。现在让我们在会话 1 中运行 DDL 语句并观察上面讨论的内容。
Session 1:
SQL> CREATE TABLE NEW_TABLE(I INT);
Table created.
Session 2:
SQL> SELECT COUNT(*) FROM AUTOC_TEST;
COUNT(*)
----------
1
我们看到，当我们从会话 1 运行 DDL 语句时，Oracle 会自动提交，并且我们能够看到会话 2 中的更改。
二、PostgreSQL 中的自动提交怎么样？
PostgreSQL 在自动提交模式下运行，并且没有选项可以在服务器级别更改此行为。这通常称为以非链接模式执行的事务。 但是，PostgreSQL 允许客户端配置自动提交设置。这些数据库客户端通常允许我们通过在事务结束后的第一个语句之前自动发出 BEGIN 语句来关闭客户端的自动提交。
这就是为什么在上面的例子中，我们能够在第二个会话中获取第一个会话的更改。因为 PostgreSQL 服务器以自动提交模式运行。PSQL 客户端默认为 AUTOCOMMIT ON。
始终强烈建议避免禁用自动提交。大多数数据库客户端和 API（包括 psql、JDBC、psycopg2、pgAdmin 和 DBeaver）都提供禁用自动提交的选项。值得注意的是，在提到的列表中，除 psycopg2 之外的所有客户端都以自动提交模式作为默认设置运行。因此，当使用 psycopg2 时，我们需要确保使用以下语句打开 AUTOCOMMIT 模式，其中 conn 是连接变量。
conn.autocommit = True
PostgreSQL 中“自动提交关闭”的问题
在 PostgreSQL 中关闭自动提交时可能会出现几个问题。如果禁用自动提交而忘记执行提交或回滚，您的事务将长期处于空闲状态。还有几个问题：
我们不能在过程中使用 COMMIT/ROLLBACK
如果我们禁用自动提交，我们将无法在过程中使用 COMMIT 或 ROLLBACK 等事务控制语句。如果我们使用它们，我们将收到运行时错误。请参阅下面的示例 –
CREATE OR REPLACE PROCEDURE add_numbers(
in1 INTEGER,
in2 INTEGER
)
AS $$
BEGIN
INSERT INTO testidm VALUES(1);
COMMIT;
END;
$$ LANGUAGE plpgsql;
CALL add_numbers(1,2);
--Output
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function add_numbers(integer,integer) line 4 at COMMIT
文档中也提到了同样的事情：
If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction.
如果 CALL 在事务块中执行，则被调用过程不能执行事务控制语句。仅当 CALL 在其自己的事务中执行时才允许事务控制语句。
长时间运行的事务持有的锁
在 PostgreSQL 中禁用自动提交使用户可以完全控制何时提交或回滚。但是，这可能会导致事务长时间运行，并且事务内 SQL 语句获取的锁可能会保持较长时间，从而可能阻塞其他事务。
表膨胀
在 PostgreSQL 中，autovacuum 守护进程负责删除由删除和更新生成的死元组。如果会话有长时间运行的打开事务，autovacuum 可能无法删除这些死元组，从而导致表膨胀。
结论
总而言之，当从 Oracle 迁移到 PostgreSQL 时，我们处理 AUTOCOMMIT 的方式可能会导致问题。 Oracle 依赖于手动提交，而 PostgreSQL 默认情况下以自动提交模式运行。 了解并适应这些差异对于平稳过渡至关重要，避免交易管理方式出现意外问题。 始终建议不要关闭自动提交，因为这样做会导致意外行为和运行时错误。
sqlplus的事务提交
SQL> select * from v$version;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
--------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
Version 19.3.0.0.0
SQL> show autocommit
autocommit OFF
SQL>
事务提交有三种类型：显式提交、隐式提交及自动提交。接下来分别看一下：
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT;
(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER
AUDIT
COMMENT
CONNECT
CREATE
DISCONNECT
DROP
EXIT
GRANT
NOAUDIT
QUIT
REVOKE
RENAME
...
(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON;
# 需要注意的是，无论AUTOCOMMIT设置为何值，当退出SQL*Plus时，当前会话所有的DML操作所改变的数据都会被提交
SQL> set auto
SP2-0281: autocommit missing set option
Usage: SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] | n }
SQL>
# OFF为默认值，表示关闭自动提交
# ON和IMM都表示打开自动提交，二者没有区别
# n表示成功执行n条DML操作后再自动提交。n不能小于0，也不能大于20亿（2,000,000,000）。注意，这里不是一个DML语句所影响的行数，而是DML语句的个数
情景一：autocommit 开启/关闭 下的rollback，如下：
## autocommit OFF
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 13:37:06 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> create table t1(id int);
Table created.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> insert into t1 values(2);
1 row created.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
STATUS
--------
ACTIVE
SQL> commit;
Commit complete.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> insert into t1 values(3);
1 row created.
SQL> rollback;
Rollback complete.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> select * from t1;
ID
----------
2
SQL>
如上autocommit=off，第一条DML语句自动开启事务 于是上面的rollback就会把insert 3给回滚掉！
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 13:45:09 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set autocommit on;
SQL>
SQL> drop table t1;
Table dropped.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> create table t1(id int);
Table created.
SQL> insert into t1 values(1);
1 row created.
Commit complete.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> update t1 set id = 2 where id = 1;
1 row updated.
Commit complete.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> rollback;
Rollback complete.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> select * from t1;
ID
----------
2
SQL>
如上autocommit=on，于是每一条DML语句自动开启事务，然后execute 最后都提交了。那么上面的rollback就不会把insert 和 update给回滚掉！
情景二：autocommit 开启/关闭 遇到DDL，如下：
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 13:51:25 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show autocommit;
autocommit OFF
SQL>
SQL> create table t1(id int);
insert into t1 values(1);
Table created.
SQL> SQL>
1 row created.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
STATUS
--------
ACTIVE
SQL> create table t2 as select * from t1;
Table created.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> rollback;
Rollback complete.
SQL> select * from t1;
ID
----------
1
SQL>
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 13:54:26 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set autocommit on
SQL>
SQL> create table t1(id int);
Table created.
SQL> insert into t1 values(1);
1 row created.
Commit complete.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> create table t2 as select * from t1;
Table created.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL> rollback;
Rollback complete.
SQL> select t1.STATUS from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
no rows selected
SQL>
如上当autocommit=off，第一条DML语句自动开启事务 遇到DDL之后隐式提交事务，自然上面的rollback无法回滚了！
情景三：autocommit 开启/关闭 遇到 会话正常/异常退出，如下：
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 14:19:35 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set autocommit on
SQL>
SQL> create table t1(id int);
Table created.
SQL> insert into t1 values(1);
1 row created.
Commit complete.
SQL>
SQL> set autocommit off
SQL>
SQL> insert into t1 values(2);
1 row created.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@dbserver ~]$
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 6 14:20:59 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from t1;
ID
----------
1
2
SQL>
异常退出，如下：
如上当autocommit=off，第一条DML语句自动开启事务 会话正常退出则会提交事务，异常则回滚！
psql自动提交原理
psql的自动提交，默认如下：
[postgres@localhost:~/test/bin]$ ./psql
psql (16.0)
Type "help" for help.
postgres=# \echo :AUTOCOMMIT
on
postgres=#
## 关闭如下
postgres=# \set AUTOCOMMIT off
postgres=#
postgres=# \echo :AUTOCOMMIT
off
postgres=#
下面来看一下其实现的原理，如下：
此时函数堆栈，如下：
SendQuery(const char * query) (\home\postgres\postgres\src\bin\psql\common.c:1099)
MainLoop(FILE * source) (\home\postgres\postgres\src\bin\psql\mainloop.c:439)
main(int argc, char ** argv) (\home\postgres\postgres\src\bin\psql\startup.c:462)
如上，当自动提交关闭之后，客户端一侧使用如下libpq函数 进行事务的开启：
// src/bin/psql/common.c
...
if (transaction_status == PQTRANS_IDLE &&
!pset.autocommit &&
!command_no_begin(query))
{
PGresult *result;
result = PQexec(pset.db, "BEGIN");
if (PQresultStatus(result) != PGRES_COMMAND_OK)
{
pg_log_info("%s", PQerrorMessage(pset.db));
ClearOrSaveResult(result);
goto sendquery_cleanup;
}
ClearOrSaveResult(result);
transaction_status = PQtransactionStatus(pset.db);
}
...
// 接下来 transaction_status 状态 如下：
PQTRANS_INTRANS, /* idle, within transaction block */
然后执行，如下：
执行完成，如下：(注意 客户端状态)
浙公网安备 33010602011771号