Oracle VS PostgreSQL – 事务控制语句

开源软件联盟PostgreSQL分会 2022-06-21

1724

在从Oracle 迁移到 PostgreSQL时，要考虑的重要事实是它们都是 2 个不同的数据库引擎。组织在迁移过程中犯的主要错误是假设 Oracle 和 PostgreSQL 的行为是相同的。Oracle 与 PostgreSQL 之间存在差异的领域之一是使用事务控制语句。在将 PL/SQL 过程和函数从 Oracle 迁移到 PostgreSQL 中的 PL/pgSQL 时，我们需要详细了解事务控制语句的工作原理。在本文结束时，您将清楚地了解 Oracle 与 PostgreSQL 的事务控制语句。

开始和结束

让我们从一个重要的事实开始，PL/SQL 和 PL/pgSQL 中的 BEGIN 和 END 只是句法元素，与事务无关。大多数时候，我们对用于事务控制的类似名称的 SQL 命令感到困惑。在 PL/SQL 和 PL/pgSQL 中启动块的 BEGIN 与启动事务的 SQL 语句 BEGIN 不同。此处的 BEGIN/END 仅用于分组的目的，但它们不会启动或结束事务。函数和触发器过程始终作为外部查询启动事务的一部分运行。他们无法启动或提交该事务，因为没有他们必须运行的上下文。

以 PostgreSQL 中的以下 PL/pgSQL 代码为例。

CREATE OR REPLACE PROCEDURE test_proc ()
    AS $$
DECLARE
BEGIN
    RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$
LANGUAGE PLPGSQL;

DO $$
BEGIN
   RAISE NOTICE 'current transaction id: %', txid_current();
   CALL test_proc ();
   RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$;

以下是上述匿名块的输出。

NOTICE: current transaction id: 17788
NOTICE: current transaction id: 17788
NOTICE: current transaction id: 17788

我们可以从上面的输出中得出结论，BEGIN 和 END 不会开始或结束事务。Oracle 中的 PL/SQL 也是如此。

现在，让我们看看在 PostgreSQL 中使用 COMMIT 时会发生什么

在 PostgreSQL 的 PL/pgSQL 中，每当一个过程中发生 COMMIT 时，当前事务就会结束，并自动启动一个新事务。

考虑以下带有 COMMIT 的 PL/pgSQL 代码。

CREATE OR REPLACE PROCEDURE test_proc ()
    AS $$
DECLARE
BEGIN
    RAISE NOTICE 'current transaction id: %', txid_current();
    COMMIT;
    RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$
LANGUAGE PLPGSQL;

现在，让我们调用上面创建的过程并查看事务 ID。

CALL test_proc();

上述命令的输出如下所示。

NOTICE: current transaction id: 1183970
NOTICE: current transaction id: 1183971

上面的输出表明，当发出 COMMIT 时，当前事务结束并创建新事务。

让我们看看在 Oracle 中使用 COMMIT 时会发生什么

在这种情况下，Oracle 中的 PL/SQL 类似于 PostgreSQL 中的 PL/pgSQL，但是，在创建新事务的方式上仍然存在明显的差异。执行 COMMIT 时，只要遇到第一个 SQL 语句（特别是 DML 命令，如 INSERT 、 UPDATE 、 DELETE 、 SELECT ），就会结束当前事务并开始新事务。

以下以Oracle中的 PL/SQL 代码为例。

CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
    insert into test_ts values(1);
    DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
    COMMIT;

    -- No DML performed after the above commit.

    DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
    insert into test_ts values(1);

    DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;

现在，让我们调用上面创建的过程并查看事务 ID。

CALL test_proc();

上述命令的输出如下所示。

transaction id: 10.4.6777
transaction id:
transaction id: 10.19.6776

我们可以从上面的输出中观察到两件事：第一个是 COMMIT 结束了事务。第二个是新事务仅在遇到第一个 SQL 语句时才开始，而不是立即开始。

Oracle 与 PostgreSQL 中的异常块

现在让我们考虑Oracle与PostgreSQL中的 EXCEPTION 块之间的行为差异。

PostgreSQL 中的异常块

在 PL/pgSQL 中，块中的 EXCEPTION 子句有效地创建了一个子事务，该子事务可以回滚而不影响外部事务。

考虑 PostgreSQL 中的以下 PL/pgSQL 代码。

-- I have created the following procedure
CREATE OR REPLACE PROCEDURE test_proc()
    AS $$
DECLARE
BEGIN
    insert into test_ts values(1);
    RAISE NOTICE 'current transaction id: %', txid_current();
    PERFORM 1/0;
        EXCEPTION
             WHEN OTHERS THEN
             RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$ LANGUAGE PLPGSQL;

上述程序将进入异常块，因为 PERFORM 1/0 将抛出异常。一旦代码进入异常块，它将回滚进入 BEGIN 后执行的所有操作。

现在使用以下匿名块在PostgreSQL中调用此过程。

DO
$$
BEGIN
    insert into test_ts values(1);
    RAISE NOTICE 'current transaction id: %', txid_current();
    CALL test_proc();
    insert into test_ts values(1);
    RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$;

在上面的块中，我正在执行插入并调用上述过程并再次执行另一个插入。这意味着，除了过程（test_proc()）执行之外的一切都会成功。因此，我们只会看到插入到 test_ts 表中的 2 条记录。

上述命令的输出和插入的行数如下所示。

-- Output
NOTICE: current transaction id: 17971
NOTICE: current transaction id: 17971
NOTICE: current transaction id: 17971
NOTICE: current transaction id: 17971

-- Rows Inserted
postgres=# SELECT * from test_ts;
 id
----
  1
  1
(2 rows)

在这种情况下，PL/pgSQL 块作为原子子事务执行。当一个异常被捕获时，整个块在异常块被执行之前被回滚。实际上，异常处理程序块是在子事务中运行的，这只不过是在 BEGIN 处创建一个保存点。

当抛出异常时，它会回滚到保存点。由于这个原因，在 PostgreSQL 的过程 test_proc 中进入异常块之前执行的插入被回滚。所以我们只看到在上面的输出中插入了 2 行。

当一个 EXCEPTION 子句捕捉到错误时，PL/pgSQL 函数的局部变量保持与错误之前相同，但对块内持久数据库状态的所有更改都将回滚。如果我们在 EXCEPTION 块中显式指定 ROLLBACK，则整个事务都会回滚。

Oracle中的异常块

让我们看一下Oracle PL/SQL 中异常的行为。
考虑以下类似于上述 PL/pgSQL 代码的 PL/SQL 代码。

CREATE OR REPLACE PROCEDURE test_proc IS
dummy number;
BEGIN
    insert into test_ts values(1);
    DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
    dummy := 1/0;
         EXCEPTION
              WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;

类似于我们上面的 PostgreSQL 示例，Oracle 的上述过程将进入异常块，因为 1/0 将引发异常。一旦代码进入异常块，它将回滚进入 BEGIN 后执行的所有操作。

我现在已经执行了以下操作。

BEGIN
   insert into test_ts values(1);
   DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
   test_proc();
   insert into test_ts values(1);
   DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;

上述命令的输出和插入的行数如下所示。

-- Output
transaction id: 8.24.3784
transaction id: 8.24.3784
transaction id: 8.24.3784
transaction id: 8.24.3784

-- Rows Inserted
SQL> select * from test_ts;
    ID
----------
    1
    1
    1

当您看到上述结果时，您应该看到结果为 3 条记录。所以在Oracle中通过procedure : test_proc 执行的插入，在进入异常块之前不会回滚。因此，行的输出是 3，而 PostgreSQL 中类似过程的输出是 2。

在 PL/SQL 中，异常块与事务控制无关，该块在现有事务中运行。当发生异常时，这只是关于分支到另一个代码路径。

与 PL/pgSQL 不同，当 EXCEPTION 子句捕获错误时，块不会回滚。由于这个原因，在异常之前执行的插入在事务中仍然可见，并且可以提交或回滚。

函数中的提交和回滚

对于 Oracle 中的 PL/SQL 中的函数，COMMIT 和 ROLLBACK 的行为方式与过程相同。但是对于PostgreSQL 中的PL/pgSQL 函数，您不能使用 SAVEPOINT、COMMIT 或 ROLLBACK。但是，我们仍然可以使用 PostgreSQL 函数中的 EXCEPTION 块来处理异常。PL/SQL 和 PL/pgSQL 函数中的 EXCEPTION 块的行为类似于过程。在 PL/pgSQL 函数中，当 EXCEPTION 子句捕获错误时，该块被回滚，而 PL/SQL 函数 EXCEPTION 块仅用于代码分支并且该块不回滚。

在 PL/SQL 和 PL/pgSQL 中，如果引发错误并且未在其中处理，则调用过程或函数的事务将中止。中止的事务无法提交，如果它们尝试提交，则COMMIT被视为 ROLLBACK。

结论

我们在本文中讨论了以下几个要点：
1、BEGIN 和 END 只是 Oracle 的 PL/SQL 和 PostgreSQL 的 PL/pgSQL 中的句法元素，与交易无关。
2、在PL/pgSQL 的 COMMIT 之后新事务自动启动，而在 PL/SQL 中，新事务仅在遇到第一个 SQL 语句时才开始。
3、PL/pgSQL 中的 EXCEPTION 块在捕获异常时回滚该块直到最后一个保存点，这与 PL/SQL 中的 EXCEPTION 块仅用于代码分支不同。
4、最后，我们还了解到 PL/pgSQL 函数不能有 SAVEPOINT、COMMIT 或 ROLLBACK。

在开始迁移数据库之前，了解 Oracle 和 PostgreSQL 之间的区别很重要。我们之前的文章中已经发布了一些此类差异：处理尾随零、存储过程中的 OUT 参数、移植数组长度等。有关 Oracle 和 PostgreSQL 之间的更多此类差异，请订阅我们的新闻通讯。

文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle VS PostgreSQL – 事务控制语句

评论