Amazon Redshift
数据库开发人员指南
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

管理事务

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

但是,当您从用户指定的事务块(由 BEGIN...COMMIT 定义)中调用存储过程时,存储过程中的所有语句在用户指定事务的上下文中运行。过程不会在退出时隐式提交。调用方控制过程提交或回退。

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

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

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

  • COMMIT – 会回滚当前事务中完成的所有工作,并隐式开始新事务。有关更多信息,请参阅 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 语句仅用于分组。它们不启动或结束事务。有关更多信息,请参阅

以下示例演示在从明确的事务块中调用存储过程时的事务行为。从存储过程外部发布了两个插入语句,从内部发布一个语句,这都属于同一个事务 (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

以下示例两次发出 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