管理事务 - Amazon Redshift
默认模式存储过程事务管理非原子模式存储过程事务管理
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

管理事务

您可以创建具有默认事务管理行为或非原子行为的存储过程。

默认模式存储过程事务管理

默认事务模式自动提交行为会导致分别提交每个独自运行的 SQL 命令。对存储过程的调用视为单个 SQL 命令。过程中的 SQL 语句的行为就像在事务块中一样，在调用开始时隐式开始，在调用完成时结束。对其他过程的嵌套调用的处理方式，与对待调用方在相同事务上下文中的任何其他 SQL 语句和操作一样。有关自动提交行为的更多信息，请参阅可序列化的隔离。

但是，假设您从用户指定的事务块（由 BEGIN...COMMIT 定义）中调用存储过程。在这种情况下，存储过程中的所有语句都在用户指定事务的上下文中运行。过程不会在退出时隐式提交。调用方控制过程提交或回退。

如果使用存储过程时遇到任何错误，则将回退在当前事务中进行的所有更改。

您可以在存储过程中使用以下事务控制语句：

  • COMMIT – 提交当前事务中完成的所有工作，并隐式开始新事务。有关更多信息，请参阅COMMIT。

  • ROLLBACK – 会回滚当前事务中完成的所有工作，并隐式开始新事务。有关更多信息，请参阅ROLLBACK。

TRUNCATE 是可以从存储过程中发出的另一个语句，会影响事务管理。在 Amazon Redshift 中，TRUNCATE 隐式发布提交。此行为在存储过程的上下文中保持相同。从存储过程中发出 TRUNCATE 语句时，它会提交当前事务并开始新事务。有关更多信息，请参阅TRUNCATE。

COMMIT、ROLLBACK 或 TRUNCATE 语句后面的所有语句都会在新事务的上下文中运行，直到遇到 COMMIT、ROLLBACK 或 TRUNCATE 语句或存储过程结束。

在存储过程中使用 COMMIT、ROLLBACK 或 TRUNCATE 语句时，适用以下限制：

  • 如果从事务块中调用存储过程，它就无法发出 COMMIT、ROLLBACK 或 TRUNCATE 语句。此限制适用于存储过程自身的主体和任何嵌套过程调用。

  • 如果存储过程用 SET config 选项创建，它就无法发出 COMMIT、ROLLBACK 或 TRUNCATE 语句。此限制适用于存储过程自身的主体和任何嵌套过程调用。

  • 任何打开的游标（显式或隐式）在处理 COMMIT、ROLLBACK 或 TRUNCATE 语句时会自动关闭。有关显式和隐式游标的约束，请参阅存储过程支持注意事项。

此外，您不能使用动态 SQL 运行 COMMIT 或 ROLLBACK。但是，您可以使用动态 SQL 运行 TRUNCATE。有关更多信息，请参阅动态 SQL。

处理存储过程时，请考虑将 PL/pgSQL 中的 BEGIN 和 END 语句仅用于分组。它们不启动或结束事务。有关更多信息，请参阅Block（阻止）。

以下示例演示在从明确的事务块中调用存储过程时的事务行为。从存储过程外部发布了两个插入语句，从内部发布一个语句，这都属于同一个事务 (3382)。用户明确发出提交时，提交事务。

CREATE OR REPLACE PROCEDURE sp_insert_table_a(a int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
END;
$$;

Begin;
  insert into test_table_a values (1);
  Call sp_insert_table_a(2);
  insert into test_table_a values (3);
Commit; 

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  | pid |  type   |               stmt_text
--------+------+-----+---------+----------------------------------------
    103 | 3382 | 599 | UTILITY | Begin;
    103 | 3382 | 599 | QUERY   | insert into test_table_a values (1);
    103 | 3382 | 599 | UTILITY | Call sp_insert_table_a(2);
    103 | 3382 | 599 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3382 | 599 | QUERY   | insert into test_table_a values (3);
    103 | 3382 | 599 | UTILITY | COMMIT

相反，例如从显式事务块外部发出相同的语句并且会话将自动提交设置为 ON 时。在这种情况下，每个语句都在自己的事务中运行。

insert into test_table_a values (1);
Call sp_insert_table_a(2);
insert into test_table_a values (3);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  | pid |  type   |                                                                    stmt_text
--------+------+-----+---------+-------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3388 | 599 | QUERY   | insert into test_table_a values (1);
    103 | 3388 | 599 | UTILITY | COMMIT
    103 | 3389 | 599 | UTILITY | Call sp_insert_table_a(2);
    103 | 3389 | 599 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3389 | 599 | UTILITY | COMMIT
    103 | 3390 | 599 | QUERY   | insert into test_table_a values (3);
    103 | 3390 | 599 | UTILITY | COMMIT

以下示例在插入到 test_table_a 中之后发布 TRUNCATE 语句。TRUNCATE 语句发出隐式提交，提交当前事务 (3335) 并启动新事务 (3336)。新事务在过程退出时提交。

CREATE OR REPLACE PROCEDURE sp_truncate_proc(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
  TRUNCATE test_table_b;
  INSERT INTO test_table_b values (b);
END;
$$;

Call sp_truncate_proc(1,2);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |  pid  |  type   |                                                                                             stmt_text
--------+------+-------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3335 | 23636 | UTILITY | Call sp_truncate_proc(1,2);
    103 | 3335 | 23636 | QUERY   | INSERT INTO test_table_a values ( $1 )
    103 | 3335 | 23636 | UTILITY | TRUNCATE test_table_b
    103 | 3335 | 23636 | UTILITY | COMMIT
    103 | 3336 | 23636 | QUERY   | INSERT INTO test_table_b values ( $1 )
    103 | 3336 | 23636 | UTILITY | COMMIT

以下示例从嵌套调用中发出 TRUNCATE。TRUNCATE 提交事务 (3344) 的所有外部和内部过程中迄今为止完成所有工作。它会启动新事务 (3345)。新事务在外部过程退出时提交。

CREATE OR REPLACE PROCEDURE sp_inner(c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO inner_table values (c);
  TRUNCATE outer_table;
  INSERT INTO inner_table values (d);
END;
$$;

CREATE OR REPLACE PROCEDURE sp_outer(a int, b int, c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO outer_table values (a);
  Call sp_inner(c, d);
  INSERT INTO outer_table values (b);
END;
$$;

Call sp_outer(1, 2, 3, 4);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |  pid  |  type   |                                                                                              stmt_text
--------+------+-------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    103 | 3344 | 23636 | UTILITY | Call sp_outer(1, 2, 3, 4);
    103 | 3344 | 23636 | QUERY   | INSERT INTO outer_table values ( $1 )
    103 | 3344 | 23636 | UTILITY | CALL sp_inner( $1 , $2 )
    103 | 3344 | 23636 | QUERY   | INSERT INTO inner_table values ( $1 )
    103 | 3344 | 23636 | UTILITY | TRUNCATE outer_table
    103 | 3344 | 23636 | UTILITY | COMMIT
    103 | 3345 | 23636 | QUERY   | INSERT INTO inner_table values ( $1 )
    103 | 3345 | 23636 | QUERY   | INSERT INTO outer_table values ( $1 )
    103 | 3345 | 23636 | UTILITY | COMMIT

下面的示例演示了在提交 TRUNCATE 语句时关闭游标 cur1。

CREATE OR REPLACE PROCEDURE sp_open_cursor_truncate()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  open cur1;
  TRUNCATE table test_table_b;
  Loop
    fetch cur1 into rec;
    raise info '%', rec.c1;
    exit when not found;
  End Loop;
END
$$;

call sp_open_cursor_truncate();
ERROR: cursor "cur1" does not exist
CONTEXT: PL/pgSQL function "sp_open_cursor_truncate" line 8 at fetch

下面的示例发出 TRUNCATE 语句，并且无法从明确的事务块内部调用。

CREATE OR REPLACE PROCEDURE sp_truncate_atomic() LANGUAGE plpgsql
AS $$
BEGIN
  TRUNCATE test_table_b;
END;
$$;

Begin;
  Call sp_truncate_atomic();
ERROR: TRUNCATE cannot be invoked from a procedure that is executing in an atomic context.
HINT: Try calling the procedure as a top-level call i.e. not from within an explicit transaction block. 
Or, if this procedure (or one of its ancestors in the call chain) was created with SET config options, recreate the procedure without them.
CONTEXT: SQL statement "TRUNCATE test_table_b"
PL/pgSQL function "sp_truncate_atomic" line 2 at SQL statement

以下示例显示，不是超级用户或表所有者的用户可以对表发出 TRUNCATE 语句。用户使用 Security Definer 存储过程执行此操作。该示例显示以下操作：

  • user1 创建表 test_tbl。

  • user1 创建存储过程 sp_truncate_test_tbl。

  • user1 将对于存储过程的 EXECUTE 权限授予 user2。

  • user2 运行此存储过程来截断表 test_tbl。该示例显示 TRUNCATE 命令之前和之后的行计数。

set session_authorization to user1;
create table test_tbl(id int, name varchar(20));
insert into test_tbl values (1,'john'), (2, 'mary');
CREATE OR REPLACE PROCEDURE sp_truncate_test_tbl() LANGUAGE plpgsql
AS $$
DECLARE
  tbl_rows int;
BEGIN
  select count(*) into tbl_rows from test_tbl;
  RAISE INFO 'RowCount before Truncate: %', tbl_rows;
  TRUNCATE test_tbl;
  select count(*) into tbl_rows from test_tbl;
  RAISE INFO 'RowCount after Truncate: %', tbl_rows;
END;
$$ SECURITY DEFINER;
grant execute on procedure sp_truncate_test_tbl() to user2;
reset session_authorization;


set session_authorization to user2;
call sp_truncate_test_tbl();
INFO:  RowCount before Truncate: 2
INFO:  RowCount after Truncate: 0
CALL
reset session_authorization;

以下示例两次发出 COMMIT。第一个 COMMIT 提交在事务 10363 中完成的所有工作，并隐式启动事务 10364。事务 10364 则由第二个 COMMIT 语句提交。

CREATE OR REPLACE PROCEDURE sp_commit(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table values (a);
  COMMIT;
  INSERT INTO test_table values (b);
  COMMIT;
END;
$$;

call sp_commit(1,2);

select userid, xid, pid, type, trim(text) as stmt_text
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;
 userid |  xid  | pid  |  type   |                                                                                    stmt_text
--------+-------+------+---------+-----------------------------------------------------------------------------------------------------------------
    100 | 10363 | 3089 | UTILITY | call sp_commit(1,2);
    100 | 10363 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10363 | 3089 | UTILITY | COMMIT
    100 | 10364 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10364 | 3089 | UTILITY | COMMIT

如果 sum_vals 大于 2，以下示例将发出 ROLLBACK 语句。第一个 ROLLBACK 语句回滚事务 10377 中完成的所有工作并启动新事务 10378。事务 10378 则在过程退出时提交。

CREATE OR REPLACE PROCEDURE sp_rollback(a int, b int) LANGUAGE plpgsql
AS $$
DECLARE
  sum_vals int;
BEGIN
  INSERT INTO test_table values (a);
  SELECT sum(c1) into sum_vals from test_table;
  IF sum_vals > 2 THEN
    ROLLBACK;
  END IF;
  
  INSERT INTO test_table values (b);
END;
$$;

call sp_rollback(1, 2);

select userid, xid, pid, type, trim(text) as stmt_text
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

userid |  xid  | pid  |  type   |                                                                                    stmt_text
--------+-------+------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    100 | 10377 | 3089 | UTILITY | call sp_rollback(1, 2);
    100 | 10377 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10377 | 3089 | QUERY   | SELECT sum(c1) from test_table
    100 | 10377 | 3089 | QUERY   | Undoing 1 transactions on table 133646 with current xid 10377 : 10377
    100 | 10378 | 3089 | QUERY   | INSERT INTO test_table values ( $1 )
    100 | 10378 | 3089 | UTILITY | COMMIT

非原子模式存储过程事务管理

在 NONATOMIC 模式下创建的存储过程，与在默认模式下创建的过程，其事务控制行为并不相同。与存储过程之外的 SQL 命令自动提交行为类似，NONATOMIC 过程中的每个 SQL 语句都在自己的事务中运行并自动提交。如果用户在 NONATOMIC 存储过程中启动显式事务块，则该块中的 SQL 语句不会自动提交。事务块控制其中的语句的提交或回滚。

在 NONATOMIC 存储过程中，您可以使用 START TRANSACTIOM 语句打开过程中的显式事务块。但是，如果其中已有一个打开的事务块，则此语句将没有任何效果，因为 Amazon Redshift 不支持子事务。先前的事务会继续执行。

在 NONATOMIC 过程中使用游标 FOR 循环时，请确保在遍历查询的结果之前打开一个显式事务块。否则，在自动提交循环内的 SQL 语句时，游标将关闭。

使用 NONATOMIC 模式行为时的一些注意事项如下：

  • 如果没有打开的事务块并且会话启用了自动提交，则存储过程中的每个 SQL 语句都会自动提交。

  • 如果从事务块中调用存储过程，您可以发布 COMMIT/ROLLBACK/TRUNCATE 语句来结束事务。这在默认模式下无法实现。

  • 您可以发布 START TRANSACTION 语句来启动存储过程中的事务块。

以下示例演示了使用 NONATOMIC 存储过程时的事务行为。以下所有示例的会话都已启用自动提交。

在以下示例中，NONATOMIC 存储过程有两个 INSERT 语句。当在事务块之外调用该过程时，该过程中的每个 INSERT 语句都会自动提交。

CREATE TABLE test_table_a(v int); 
CREATE TABLE test_table_b(v int); 

CREATE OR REPLACE PROCEDURE sp_nonatomic_insert_table_a(a int, b int) NONATOMIC AS
$$
BEGIN
    INSERT INTO test_table_a values (a);
    INSERT INTO test_table_b values (b);
END;
$$ 
LANGUAGE plpgsql;

Call sp_nonatomic_insert_table_a(1,2);

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1792 | 1073807554 | UTILITY | Call sp_nonatomic_insert_table_a(1,2);
      1 | 1792 | 1073807554 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1792 | 1073807554 | UTILITY | COMMIT
      1 | 1793 | 1073807554 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1793 | 1073807554 | UTILITY | COMMIT
(5 rows)

但是，当从 BEGIN..COMMIT 块中调用该过程时，所有语句都属于同一个事务（xid=1799）。

Begin;
  INSERT INTO test_table_a values (10);
  Call sp_nonatomic_insert_table_a(20,30);
  INSERT INTO test_table_b values (40);
Commit; 

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |                stmt_text                 
--------+------+------------+---------+------------------------------------------
      1 | 1799 | 1073914035 | UTILITY | Begin;
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_a values (10);
      1 | 1799 | 1073914035 | UTILITY | Call sp_nonatomic_insert_table_a(20,30);
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1799 | 1073914035 | QUERY   | INSERT INTO test_table_b values (40);
      1 | 1799 | 1073914035 | UTILITY | COMMIT
(7 rows)

在此示例中，START TRANSACTION...COMMIT 之间有两条 INSERT 语句。在事务块之外调用该过程时，两条 INSERT 语句位于同一个事务（xid=1866）中。

CREATE OR REPLACE PROCEDURE sp_nonatomic_txn_block(a int, b int) NONATOMIC AS
$$
BEGIN
    START TRANSACTION;
    INSERT INTO test_table_a values (a);
    INSERT INTO test_table_b values (b);
    COMMIT;
END;
$$ 
LANGUAGE plpgsql;

Call sp_nonatomic_txn_block(1,2);

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1865 | 1073823998 | UTILITY | Call sp_nonatomic_txn_block(1,2);
      1 | 1866 | 1073823998 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1866 | 1073823998 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1866 | 1073823998 | UTILITY | COMMIT
(4 rows)

从 BEGIN...COMMIT 块中调用该过程时，该过程中的 START TRANSACTION 不执行任何操作，因为已有一个打开的事务。过程中的 COMMIT 提交当前事务（xid = 1876）并启动新事务。

Begin;
  INSERT INTO test_table_a values (10);
  Call sp_nonatomic_txn_block(20,30);
  INSERT INTO test_table_b values (40);
Commit; 

Select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;

 userid | xid  |    pid     |  type   |               stmt_text                
--------+------+------------+---------+----------------------------------------
      1 | 1876 | 1073832133 | UTILITY | Begin;
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_a values (10);
      1 | 1876 | 1073832133 | UTILITY | Call sp_nonatomic_txn_block(20,30);
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_a values ( $1 )
      1 | 1876 | 1073832133 | QUERY   | INSERT INTO test_table_b values ( $1 )
      1 | 1876 | 1073832133 | UTILITY | COMMIT
      1 | 1878 | 1073832133 | QUERY   | INSERT INTO test_table_b values (40);
      1 | 1878 | 1073832133 | UTILITY | COMMIT
(8 rows)

此示例演示如何处理游标循环。表 test_table_a 具有三个值。目标是遍历这三个值并将这些值插入表 test_table_b 中。如果通过以下方法创建 NONATOMIC 存储过程，则在第一个循环中执行 INSERT 语句后，它将引发错误：游标“cur1”不存在。这是因为 INSERT 的自动提交会关闭打开的游标。

insert into test_table_a values (1), (2), (3);

CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  open cur1;
  Loop
    fetch cur1 into rec;
    exit when not found;
    raise info '%', rec.v;
    insert into test_table_b values (rec.v);
  End Loop;
END
$$;

CALL sp_nonatomic_cursor();

INFO:  1
ERROR:  cursor "cur1" does not exist
CONTEXT:  PL/pgSQL function "sp_nonatomic_cursor" line 7 at fetch

要使游标循环正常工作，请将其置于 START TRANSACTION...COMMIT 之间。

insert into test_table_a values (1), (2), (3);

CREATE OR REPLACE PROCEDURE sp_nonatomic_cursor() NONATOMIC
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  cur1 cursor for select * from test_table_a order by 1;
BEGIN
  START TRANSACTION;
  open cur1;
  Loop
    fetch cur1 into rec;
    exit when not found;
    raise info '%', rec.v;
    insert into test_table_b values (rec.v);
  End Loop;
  COMMIT;
END
$$;

CALL sp_nonatomic_cursor();

INFO:  1
INFO:  2
INFO:  3
CALL