管理交易 - 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

非原子模式預存程序交易管理

在 NONATIC 模式中建立的預存程序與以預設模式建立的程序有不同的交易控制行為。與預存程序外部 SQL 命令的自動遞交行為類似，NONATOMIC 程序內的每個 SQL 陳述式都會在自己的交易中執行，並自動遞交。如果使用者在 NONATOMIC 預存程序中開始明確交易區塊，則區塊內的 SQL 陳述式不會自動確認。交易區塊控制項遞交或復原其中的陳述式。

在 NONATOMIC 預存程序中，您可以使用 START TRANSACTION 陳述式在程序內開啟明確的交易區塊。不過，如果已經有開啟的交易區塊，這個陳述式就不會執行任何動作，因為 Amazon Redshift 不支援子交易。上一筆交易會繼續進行。

當您在 NONATOMIC 程序內使用游標 FOR 迴圈時，請確定您在迭代查詢結果之前開啟明確的交易區塊。否則，當迴圈內的 SQL 陳述式自動遞交時，游標會關閉。

使用 NONATOMIC 模式行為時的一些注意事項如下：

  • 如果沒有開啟的交易區塊，且工作階段已自動遞交預存程序中的每個 SQL 陳述式設定為 ON。

  • 如果從交易區塊內呼叫預存程序，您可以發出 COMMIT/ROLLBACK/TRUNCATE 陳述式來結束交易。這在預設模式下是不可能的。

  • 您可以發出 START TRANSACTION 陳述式，以便在預存程序中開始交易區塊。

下列範例會示範使用 NONATOMIC 預存程序時的交易行為。下列所有範例的工作階段都將自動遞交設定為 ON。

在下列範例中，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)

在這個範例中，兩個 INSERT 陳述式在 START TRANSACTION...COMMIT 之間。當程序在交易區塊之外呼叫時，兩個 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 交易不會執行任何動作，因為已經有開啟的交易。程序中的 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