管理交易 - 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 陳述式僅可用來分組,而無法用來啟動或結束交易。如需詳細資訊,請參閱 區塊

下列範例示範從明確交易區塊內呼叫預存程序時的交易行為。從預存程序外發出的兩個 insert 陳述式和從它之內發出的一個 insert 陳述式,都是相同交易 (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

下列範例顯示游標 cur1 已在 TRUNCATE 陳述式遞交時關閉。

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 交易。第二個 COMMIT 陳述式則會遞交 10364 交易。

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