Amazon Redshift
データベース開発者ガイド

トランザクションの管理

デフォルトの自動コミット動作では、個別に実行された 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 ステートメントはグループ化のみを目的としている点を考慮してください。トランザクションを開始または終了するものではありません。詳細については、「ブロック」を参照してください。

次の例は、ストアドプロシージャを明示的なトランザクションブロック内から呼び出した場合のトランザクション動作を示しています。ストアドプロシージャの外部から発行された 2 つの挿入ステートメントと内部から発行された 1 つの挿入ステートメントは、すべて同じトランザクション (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

それに対して、明示的なトランザクションブロックの外部から同じステートメントが発行され、セッションの自動コミットがオンに設定されている場合の例を示します。この場合、各ステートメントは独自のトランザクションで実行されます。

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

次の例では、TRUNCATE ステートメントを test_table_a 内に挿入後に発行しています。.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 を 2 回発行しています。最初の COMMIT では、トランザクション 10363 で行われたすべての作業をコミットし、トランザクション 10364 を暗黙的に開始します。トランザクション 10364 は、2 番目の 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