管理交易 - Amazon Redshift
預設模式預存程序交易管理非原子模式預存程序交易管理

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

管理交易

您可以建立具有預設交易管理行為或非原子行為的預存程序。

預設模式預存程序交易管理

預設的交易模式自動認可行為會導致個別執行的每個SQL命令單獨認可。對預存程序的呼叫會被視為單一SQL命令。程序內的SQL陳述式的行為就好像它們位於交易區塊中，該交易區塊會在呼叫開始和結束時在呼叫結束時隱含地開始。對另一個程序的巢狀呼叫會與其他任何SQL陳述式一樣處理，並在與呼叫者相同交易的內容中運作。如需自動遞交行為的相關資訊，請參閱可序列化隔離。

但是，假設您從使用者指定的交易區塊內呼叫預存程序 (由BEGIN... 定義) COMMIT). 在此情況下，預存程序中的所有陳述式都會在使用者指定交易的內容中執行。程序不會隱含地在結束時遞交。發起人控制程序遞交或轉返。

如果執行預存程序時發生任何錯誤，您在目前交易中做的所有變更都會復原。

您可以使用下列交易來控制預存程序中的陳述式：

  • COMMIT— 提交在當前事務中完成的所有工作，並隱式開始一個新的事務。如需詳細資訊，請參閱COMMIT。

  • ROLLBACK— 回復目前交易中完成的工作，並隱含地開始新的交易。如需詳細資訊，請參閱ROLLBACK。

TRUNCATE是您可以從預存程序中發出並影響交易管理的另一個陳述式。在 Amazon Redshift 中，隱式TRUNCATE發出提交。在預存程序的範圍內，此行為不變。當TRUNCATE陳述式從預存程序中發出時，它會認可目前的交易，並開始新的交易。如需詳細資訊，請參閱TRUNCATE。

在COMMIT、ROLLBACK或TRUNCATE陳述式之後的所有陳述式都會在新交易的內容中執行。它們會這樣做COMMIT，直到遇到ROLLBACK、或TRUNCATE陳述式或預存程序結束為止。

當您在預存程序中使用COMMITROLLBACK、或TRUNCATE陳述式時，會套用下列條件約束：

  • 如果從交易區塊內呼叫預存程序，就無法發出COMMITROLLBACK、或TRUNCATE陳述式。這項限制適用於預存程序本身和任何巢狀程序呼叫內部。

  • 如果預存程序是使用SET config選項建立的，就無法發出COMMITROLLBACK、或TRUNCATE陳述式。這項限制適用於預存程序本身和任何巢狀程序呼叫內部。

  • 處理COMMIT、ROLLBACK或TRUNCATE陳述式時，任何開啟 (明確或隱含) 的游標都會自動關閉。如需了解明確和隱含游標的限制，請參閱預存程序限制。

此外，您無法執行COMMIT或ROLLBACK使用動態SQL。但是，您可以TRUNCATE使用動態運行SQL。如需詳細資訊，請參閱動態 SQL。

使用預存程序時，請考慮 PL/PG SQL 中的 BEGIN AND 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

下列範例會在插入到之後發出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從巢狀呼叫發出 a。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命令的自動認可行為類似，程序內的每個SQL陳述式都會在自己的交易中執行，並自動認可。NONATOMIC如果使用者在NONATOMIC預存程序中開始明確交易區塊，則區塊內的SQL陳述式不會自動認可。交易區塊控制項遞交或復原其中的陳述式。

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

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

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

  • 如果沒有打開的事務塊，存儲過程中的每個SQL語句都會自動提交，並且會話自動提交設置為 ON。

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

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

下列範例會示範使用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陳述式介於 STARTTRANSACTION... 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塊，程序STARTTRANSACTION內部什麼都不做，因為已經有一個開放的事務。程序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儲過程以下面的方式創建，它會拋出錯誤光標 "cur1" 在第一個循環中執行INSERT語句之後不存在。這是因為 auto 提交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

為了使光標循環工作，請將其放在 STARTTRANSACTION... 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