トランザクション¶

トランザクションとは何ですか？¶

トランザクションは、アトミックユニットとして処理される一連の SQL ステートメントです。トランザクション内のすべてのステートメントは、一緒に適用（つまり、コミット）または取り消し（つまり、ロールバック）されます。Snowflakeトランザクションは、 ACID プロパティ を保証します。

トランザクションには、読み取りと書き込みの両方を含めることができます。

トランザクションは次のルールに従います。

• トランザクションが「ネスト」されることはありません。たとえば、コミットされた「内部」トランザクションをロールバックする「外部」トランザクションを作成したり、ロールバックされた「内部」トランザクションをコミットする「外部」トランザクションを作成したりすることはできません。

• トランザクションは単一のセッションに関連付けられます。複数のセッションは同じトランザクションを共有できません。同じセッションでスレッドが重複しているトランザクションの処理については、 トランザクションとマルチスレッド をご参照ください。

用語¶

このトピックの内容:

• 「 DDL 」という用語には、CTAS ステートメント（CREATE TABLE AS SELECT ...）およびその他の DDL ステートメントが含まれます。

• 用語「DML」は、 INSERT、 UPDATE、 DELETE、 MERGE、および TRUNCATE ステートメントを指します。

• 「クエリステートメント」という用語は、SELECTおよび CALL ステートメントを指します。

CALL ステートメント（ ストアドプロシージャ を呼び出す）は単一のステートメントですが、呼び出すストアドプロシージャには複数のステートメントを含めることができます。 ストアドプロシージャとトランザクションに対する特別なルール があります。

明示的なトランザクション¶

BEGIN ステートメントを実行することにより、トランザクションを明示的に開始できます。Snowflakeは、同義語 BEGIN WORK と BEGIN TRANSACTION をサポートしています。Snowflakeは、 BEGIN TRANSACTION の使用を推奨します。

COMMIT または ROLLBACK を実行することにより、トランザクションを明示的に終了できます。Snowflakeは、 COMMIT の同義語 COMMIT WORK と、 ROLLBACK の同義語 ROLLBACK WORK をサポートしています。

一般に、トランザクションがすでにアクティブな場合、 BEGIN TRANSACTION ステートメントはすべて無視されます。ただし、余分な BEGIN TRANSACTION ステートメントを使用すると、人間の読者にとって、 COMMIT （または ROLLBACK）ステートメントと対応する BEGIN TRANSACTION のペアリングがはるかに困難になるため、ユーザーは余分な BEGIN TRANSACTION を回避する必要があります。

このルールに対する1つの例外は、ネストされたストアドプロシージャの呼び出しです。詳細については、 スコープトランザクション をご参照ください。

暗黙的なトランザクション¶

トランザクションは、明示的な BEGIN TRANSACTION または COMMIT/ROLLBACK なしで、暗黙的に開始および終了できます。暗黙的なトランザクションは、明示的なトランザクションと同じように動作します。ただし、暗黙的なトランザクションの開始を決定するルールは、明示的なトランザクションの開始を決定するルールとは異なります。

停止と開始のルールは、ステートメントが DDL ステートメントか、 DML ステートメントか、クエリステートメントかによって異なります。ステートメントが DML またはクエリステートメントの場合、ルールは AUTOCOMMIT が有効かどうかによって異なります。

トランザクションの暗黙的および明示的な開始と終了の混合¶

紛らわしいコードを書かないようにするには、同じトランザクションで暗黙的および明示的な開始と終了を混在させないようにする必要があります。以下は正当ですが、お勧めしません。

• 暗黙的に開始されたトランザクションは、明示的な COMMIT または ROLLBACK で終了できます。

• 明示的に開始されたトランザクションは、暗黙的な COMMIT または ROLLBACK によって終了できます。

トランザクション内の失敗したステートメント¶

トランザクションはユニットとしてコミットまたはロールバックされますが、それはユニットとして成功または失敗したと言うこととまったく同じではありません。トランザクション内でステートメントが失敗した場合でも、トランザクションをロールバックするのではなく、コミットすることができます。

トランザクション内の DML ステートメントまたは CALL ステートメントが失敗すると、その失敗したステートメントによって行われた変更がロールバックされます。ただし、トランザクションがコミットまたはロールバックされるまで、トランザクションはアクティブなままになります。トランザクションがコミットされると、成功したステートメントによる変更が適用されます。

例えば、2つの有効な値と1つの無効な値を挿入する次のコードについて考えてみます。

CREATE TABLE table1 (i int);
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (1);
INSERT INTO table1 (i) VALUES ('This is not a valid integer.');    -- FAILS!
INSERT INTO table1 (i) VALUES (2);
COMMIT;
SELECT i FROM table1 ORDER BY i;

失敗した INSERT ステートメントの後のステートメントが実行された場合、最後の SELECT ステートメントの出力には、トランザクション内にある他のステートメントの1つが失敗した場合でも、整数値1および2の行が含まれます。

トランザクションとマルチスレッド¶

複数のセッションが同じトランザクションを共有することはできませんが、単一の接続を使用する複数の スレッド は同じセッションを共有するため、同じトランザクションを共有します。これにより、あるスレッドが別のスレッドで実行した作業をロールバックするなど、予期しない結果が生じる可能性があります。

この状況は、Snowflakeドライバー（Snowflake JDBC ドライバーなど）、またはコネクタ（Python用Snowflakeコネクタなど）を使用するクライアントアプリケーションがマルチスレッド化されている場合に発生する可能性があります。2つ以上のスレッドが同じ接続を共有する場合、それらのスレッドはその接続の現在のトランザクションも共有します。1つのスレッドによる BEGIN TRANSACTION、COMMIT、または ROLLBACK は、その共有接続を使用するすべてのスレッドに影響します。スレッドが非同期で実行されている場合、結果は予測できません。

同様に、1つのスレッドで AUTOCOMMIT 設定を変更すると、同じ接続を使用する他のすべてのスレッドの AUTOCOMMIT 設定に影響します。

Snowflakeでは、マルチスレッドクライアントプログラムで、少なくとも次のいずれかを実行することをお勧めします。

• スレッドごとに個別の接続を使用します。

  別々の接続を使用しても、コードが予測できない出力を生成する競合状態に陥ることがあることに注意してください。たとえば、別のスレッドがデータを更新しようとする前に、あるスレッドがデータを削除する場合があります。

• スレッドを非同期的にではなく同期的に実行して、ステップの実行順序を制御します。

重複しないトランザクション¶

以下のセクションでは、以下について説明します。

• トランザクション内でのストアドプロシージャの使用。

• ストアドプロシージャ内でのトランザクションの使用。

CREATE PROCEDURE p1()
...
$$
    begin transaction;
    statement C;
    statement D;
    commit;

    begin transaction;
    statement E;
    statement F;
    commit;
$$;

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

CALL p1();

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

BEGIN TRANSACTION;
statement C;
statement D;
COMMIT;

BEGIN TRANSACTION;
statement E;
statement F;
COMMIT;

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

スコープトランザクション¶

トランザクションを含む ストアドプロシージャ は、別のトランザクション内から呼び出すことができます。たとえば、ストアドプロシージャ内のトランザクションには、トランザクションを含む別のストアドプロシージャへの呼び出しを含めることができます。

Snowflakeでは、内部トランザクションはネストされたものとして扱われ ません。代わりに、内部トランザクションは 個別のトランザクション です。Snowflakeは、これらを「自律スコープトランザクション」（または単に「スコープトランザクション」）と呼びます。

各スコープトランザクションの開始点と終了点によって、トランザクションに含まれるステートメントが決まります。開始と終了は明示的または暗黙的です。各 SQL ステートメントがトランザクションの一部となるのは、1つのトランザクションに対してのみです。囲んだ ROLLBACK または COMMIT は、囲まれた COMMIT または ROLLBACK を元に戻すことはありません。

次の図は、2つのストアドプロシージャと2つのスコープトランザクションを示しています。この例では、各ストアドプロシージャに独自の独立したトランザクションが含まれています。最初のストアドプロシージャは2番目のストアドプロシージャを呼び出すため、プロシージャは時間的に重複しますが、内容は重複しません。網かけの内部ボックス内のステートメントすべては、1つのトランザクションにあります。他のすべてのステートメントは別のトランザクションにあります。

次の例では、トランザクションの境界はストアドプロシージャの境界とは異なります。外部ストアドプロシージャで開始されるトランザクションには、内部ストアドプロシージャのステートメントすべてではなく、その一部が含まれます。

上記のコードでは、2番目のストアドプロシージャに、最初のトランザクションのスコープにあるステートメント（SP2_T1_S2および SP2_T1_S3）が含まれています。網かけの内部ボックス内のステートメント SP2_T2_S1のみが、2番目のトランザクションのスコープ内にあります。

次の例は、トランザクションが同じストアドプロシージャ内で開始および終了しない場合に発生する問題を示しています。この例には、 BEGIN ステートメントと同じ数の COMMIT ステートメントが含まれています。ただし、 BEGIN ステートメントと COMMIT ステートメントが適切にペアリングされていないため、この例には2つのエラーが含まれています。

• 外部ストアドプロシージャはスコープトランザクションを開始しますが、明示的に完了しません。したがって、そのスコープトランザクションは、そのストアドプロシージャの最後にエラーを発生し、アクティブなトランザクションは暗黙的にロールバックされます。

• 2番目のストアドプロシージャには COMMIT が含まれていますが、そのストアドプロシージャには対応する BEGIN がありません。この COMMIT は、最初のストアドプロシージャで開始された、開いているトランザクションをコミット しません。代わりに、不適切にペアリングされた COMMIT にはエラーが発生します。

次の例は、時間的に重複する3つのスコープトランザクションを示しています。この例では、ストアドプロシージャ p1() がトランザクション内から別のストアドプロシージャ p2() を呼び出し、 p2() には独自のトランザクションが含まれているため、 p2() で開始されたトランザクションも独立して実行されます。（この例では、疑似コードを使用。）

CREATE PROCEDURE p2()
...
$$
    BEGIN TRANSACTION;
    statement C;
    COMMIT;
$$;

CREATE PROCEDURE p1()
...
$$
    BEGIN TRANSACTION;
    statement B;
    CALL p2();
    statement D;
    COMMIT;
$$;

BEGIN TRANSACTION;
statement A;
CALL p1();
statement E;
COMMIT;

Copy

これらの3つのスコープトランザクションでは、

• ストアドプロシージャの外部にあるトランザクションには、ステートメント A および E が含まれています。

• ストアドプロシージャ p1() のトランザクションに、ステートメント B と D が含まれています

• p2() のトランザクションに、ステートメント C が含まれています。

スコープトランザクションのルールは、再帰的なストアドプロシージャ呼び出しにも適用されます。再帰呼び出しは、単にネストされた呼び出しの特定のタイプであり、ネストされた呼び出しと同じトランザクションルールに従います。

AUTOCOMMIT がオフの場合、暗黙のトランザクションとストアドプロシージャの組み合わせには特に注意してください。ストアドプロシージャの終了時に誤ってトランザクションをアクティブのままにした場合、トランザクションはロールバックされます。

たとえば、次の疑似コード例では、ストアドプロシージャの最後に暗黙の ROLLBACK が発生します。

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;
COMMIT WORK;

この例では、 AUTOCOMMIT を設定するコマンドは、アクティブなトランザクションをコミットします。新しいトランザクションはすぐには開始されません。ストアドプロシージャには、新しいトランザクションを暗黙的に開始する DML ステートメントが含まれています。その暗黙の BEGIN TRANSACTION には、ストアドプロシージャに一致する COMMIT または ROLLBACK がありません。ストアドプロシージャの最後にアクティブなトランザクションがあるため、そのアクティブなトランザクションは暗黙的にロールバックされます。

ストアドプロシージャ全体を1つのトランザクションで実行する場合は、ストアドプロシージャを呼び出す前にトランザクションを開始し、呼び出し後にトランザクションをコミットします。

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
BEGIN TRANSACTION;
CALL p1;
COMMIT WORK;

この場合、 BEGIN と COMMIT は適切にペアになっており、コードはエラーなしで実行されます。

別の方法としては、次の疑似コード例のように、 BEGIN TRANSACTION と COMMIT の両方をストアドプロシージャ内に配置します。

CREATE PROCEDURE p1() ...
$$
    BEGIN TRANSACTION;
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
    COMMIT WORK;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;

スコープトランザクションで BEGIN/COMMIT ブロックを適切にペアリングしないと、Snowflakeはエラーを報告します。このエラーは、ストアドプロシージャの完了を妨げたり、囲んでいるトランザクションのコミットを妨げたりするなど、さらに影響を与える可能性があります。たとえば、次の擬似コードの例では、囲んでいるストアドプロシージャの一部のステートメントと、囲まれたストアドプロシージャがロールバックされます。

CREATE or replace PROCEDURE outer_sp1()
...
AS
$$
    insert 'osp1_alpha';
    BEGIN WORK;
    insert 'osp1_beta';
    CALL inner_sp2();
    INSERT 'osp1_delta';
    COMMIT WORK;
    INSERT 'osp1_omega';
$$;

CREATE or replace PROCEDURE inner_sp2()
...
AS
$$
    BEGIN WORK;
    insert 'isp2';
    -- Missing COMMIT, so implicitly rolls back!
$$;

CALL outer_sp1();

SELECT * FROM st;

Copy

この例では、挿入される値は「osp1_alpha」のみです。COMMIT が BEGIN と正しくペアリングされていないため、他の値は挿入されません。エラーは次のように処理されます。

1. 手順 inner_sp2() が終了すると、Snowflakeは、 inner_sp2() の BEGIN に対応する COMMIT （または ROLLBACK）がないことを検出します。

   1. Snowflakeは、 inner_sp2() で開始されたスコープトランザクションを暗黙的にロールバックします。

   2. inner_sp2() への CALL が失敗したため、Snowflakeもエラーを発行します。

2. inner_sp2() の CALL が失敗し、その CALL ステートメントが outer_sp1() にあったため、ストアドプロシージャ outer_sp1() 自体も失敗し、続行せずにエラーを返します。

3. outer_sp1() は実行を終了しないため、

   • 値「osp1_delta」および「osp1_omega」の INSERT ステートメントは実行されません。

   • outer_sp1() の開いているトランザクションは、コミットされるのではなく暗黙的にロールバックされるため、値「osp1_beta」の挿入がコミットされることはありません。

READ COMMITTED 分離¶

READ COMMITTED 分離では、ステートメントは、ステートメントの開始前にコミットされたデータのみを参照します。コミットされていないデータは表示されません。

ステートメントが複数ステートメントトランザクション内で実行される場合：

• ステートメントは、 ステートメント が始まる前にコミットされたデータのみを参照します。 同じトランザクション内の連続する2つのステートメントは、最初のステートメントと2番目のステートメントの実行の間に別のトランザクションがコミットされると、異なるデータを見ることができます。

• ステートメントは、それらの変更がまだコミットされていない場合でも、同じトランザクション 内で 実行された以前のステートメントによって行われた変更を 確認します。

ロック待機タイムアウト¶

ブロックされたステートメントは、待機していたリソースのロックを取得するか、リソースが利用可能になるのを待ってタイムアウトします。ステートメントがブロックする時間（秒単位）は、 LOCK_TIMEOUT パラメーターを設定することで構成できます。

例えば、現在のセッションのロックタイムアウトを2時間（7200秒）に変更する場合：

ALTER SESSION SET LOCK_TIMEOUT=7200;

SHOW PARAMETERS LIKE 'lock%';

+--------------+-------+---------+---------+-------------------------------------------------------------------------------+
| key          | value | default | level   | description                                                                   |
|--------------+-------+---------+---------+-------------------------------------------------------------------------------|
| LOCK_TIMEOUT | 7200  | 43200   | SESSION | Number of seconds to wait while trying to lock a resource, before timing out  |
|              |       |         |         | and aborting the statement. A value of 0 turns off lock waiting i.e. the      |
|              |       |         |         | statement must acquire the lock immediately or abort. If multiple resources   |
|              |       |         |         | need to be locked by the statement, the timeout applies separately to each    |
|              |       |         |         | lock attempt.                                                                 |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+

Copy

デッドロック¶

デッドロックは、並行トランザクションが互いにロックされているリソースを待機しているときに発生します。

Snowflakeでは、自動コミット DML またはクエリステートメントを同時に実行している間、デッドロックは発生しません。ただし、明示的に開始されたトランザクションと各トランザクションの複数のステートメントでデッドロックが発生する可能性があります。Snowflakeはデッドロックを検出し、デッドロックの一部である最新のステートメントを対象として選択します。ステートメントはロールバックされますが、トランザクション自体はアクティブなままであり、コミットまたはロールバックする必要があります。

デッドロックの検出には時間がかかる場合があります。

ステートメントエラーを許可してトランザクションを中止する¶

トランザクション内のステートメントエラーでトランザクションを中止するには、セッションレベルまたはアカウントレベルで TRANSACTION_ABORT_ON_ERROR パラメーターを設定します。

LOCK_WAIT_HISTORY ビューでブロックされたトランザクションの分析¶

LOCK_WAIT_HISTORY ビュー は、ブロックされたトランザクションの分析に役立つトランザクションの詳細を返します。出力の各行には、ロックを待機しているトランザクションの詳細と、そのロックを保持しているか、そのロックを待機しているトランザクションの詳細が含まれます。

たとえば、次のシナリオを考慮します。

このシナリオでは、次のデータが返されます。

• トランザクションBは、ロックを待機しているトランザクションです。

• トランザクションBは、タイムスタンプT1でロックをリクエストしました。

• トランザクションAは、ロックを保持するトランザクションです。

• トランザクションAのクエリ2は、ブロッカークエリです。

クエリ2は、トランザクションB（ロックを待機しているトランザクション）が待機を開始したトランザクションA（ロックを保持しているトランザクション）の最初のステートメントであるため、ブロッカークエリです。

ただし、トランザクションAの後のクエリ（クエリ5）もロックを取得したことに注意してください。これらトランザクションの後続の同時実行により、トランザクションAでロックを取得する別のクエリで、トランザクションBがブロックされる可能性があります。したがって、最初のブロッカートランザクションのクエリすべてを調査する必要があります。

スコープトランザクションとストアドプロシージャの簡単な例¶

これは、スコープトランザクションの簡単な使用例です。ストアドプロシージャには、値12の行を挿入してからロールバックするトランザクションが含まれています。外部トランザクションがコミットします。出力は、外部トランザクションのスコープ内のすべての行が保持され、内部トランザクションのスコープ内の行は保持されないことを示しています。

ストアドプロシージャの一部のみが独自のトランザクション内にあるため、ストアドプロシージャ内にあるが、ストアドプロシージャのトランザクション外にあるINSERTにより挿入された値は保持されることに注意してください。

テーブルを2つ作成します。

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);

ストアドプロシージャを作成します。

create procedure sp1()
returns varchar
language javascript
AS
$$
    // This is part of the outer transaction that started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;

ストアドプロシージャを呼び出します。

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1();
insert into tracker_1 values (09, 'outer_zulu');
commit;

結果には、00、11、13、および09が含まれている必要があります。ID =12の行は含めないでください。この行は、ロールバックされた囲まれたトランザクションのスコープ内にありました。他のすべての行は外部トランザクションのスコープ内にあり、コミットされました。特に、IDs 11と13の行はストアドプロシージャ内にありますが、最も内側のトランザクションの外側にあることに注意してください。それらは囲まれたトランザクションのスコープ内にあり、一緒にコミットされました。

select id, name FROM tracker_1
union all
select id, name FROM tracker_2
order by id;
+----+-------------+
| ID | NAME        |
|----+-------------|
|  0 | outer_alpha |
|  9 | outer_zulu  |
| 11 | p1_alpha    |
| 13 | p1_charlie  |
+----+-------------+

トランザクションの正常完了とは関係なく情報をログに記録する¶

これは、スコープトランザクションを使用する方法の簡単で実用的な例です。この例では、トランザクションは特定の情報をログに記録します。ログに記録された情報は、トランザクション自体が正常完了したか失敗したかに関係なく保持されます。この手法は、それぞれが正常完了したかどうかに関係なく、試行されたすべてのアクションを追跡するために使用できます。

テーブルを2つ作成します。

create table data_table (id integer);
create table log_table (message varchar);

ストアドプロシージャを作成します。

create procedure log_message(MESSAGE VARCHAR)
returns varchar
language javascript
AS
$$
    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into log_table values ('" + MESSAGE + "')"}
        );
    snowflake.execute (
        {sqlText: "commit"}
        );

    // Dummy value.
    return "";
$$;

create procedure update_data()
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into data_table (id) values (17)"}
        );
    snowflake.execute (
        {sqlText: "call log_message('You should see this saved.')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // Dummy value.
    return "";
$$;

ストアドプロシージャを呼び出します。

begin transaction;
call update_data();
rollback;

トランザクションがロールバックされたため、データテーブルは空です。

select * from data_table;
+----+
| ID |
|----|
+----+

ただし、ログテーブルは空ではありません。ログテーブルへの挿入は、data_tableへの挿入とは別のトランザクションで実行されました。

select * from log_table;
+----------------------------+
| MESSAGE                    |
|----------------------------|
| You should see this saved. |
+----------------------------+

スコープトランザクションとストアドプロシージャの例¶

次のいくつかの例では、以下に示すテーブルとストアドプロシージャを使用します。適切なパラメータを渡すことにより、呼び出し元は、ストアドプロシージャ内で BEGIN TRANSACTION、 COMMIT、 および ROLLBACK ステートメントが実行される場所を制御できます。

テーブルを作成します。

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);
create table tracker_3 (id integer, name varchar);

このプロシージャは、囲んでいるストアドプロシージャであり、渡されたパラメーターに応じて、囲んでいるトランザクションを作成できます。

create procedure sp1_outer(
    USE_BEGIN varchar,
    USE_INNER_BEGIN varchar,
    USE_INNER_COMMIT_OR_ROLLBACK varchar,
    USE_COMMIT_OR_ROLLBACK varchar
    )
returns varchar
language javascript
AS
$$
    // This should be part of the outer transaction started before this
    // stored procedure was called. This should be committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    // Call (and optionally begin/commit-or-rollback) an inner stored proc...
    var command = "call sp2_inner('";
    command = command.concat(USE_INNER_BEGIN);
    command = command.concat("', '");
    command = command.concat(USE_INNER_COMMIT_OR_ROLLBACK);
    command = command.concat( "')" );
    snowflake.execute (
        {sqlText: command}
        );
    if (USE_COMMIT_OR_ROLLBACK != '') {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;

このプロシージャは内部ストアドプロシージャであり、渡されたパラメーターに応じて、囲まれたトランザクションを作成できます。

create procedure sp2_inner(
    USE_BEGIN varchar,
    USE_COMMIT_OR_ROLLBACK varchar)
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (21, 'p2_alpha')"}
        );

    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_3 values (22, 'p2_bravo')"}
        );
    if (USE_COMMIT_OR_ROLLBACK != '')  {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    snowflake.execute (
        {sqlText: "insert into tracker_2 values (23, 'p2_charlie')"}
        );

    // Dummy value.
    return "";
$$;

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'rollback', 'commit');
insert into tracker_1 values (09, 'outer_charlie');
rollback;

-- Should return only 12, 21, 23.
select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+------------+
| ID | NAME       |
|----+------------|
| 12 | p1_bravo   |
| 21 | p2_alpha   |
| 23 | p2_charlie |
+----+------------+

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'commit', 'rollback');
insert into tracker_1 values (09, 'outer_charlie');
commit;

select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+---------------+
| ID | NAME          |
|----+---------------|
|  0 | outer_alpha   |
|  9 | outer_charlie |
| 11 | p1_alpha      |
| 13 | p1_charlie    |
| 22 | p2_bravo      |
+----+---------------+

ストアドプロシージャのトランザクションでのエラー処理の使用¶

次のコードは、ストアドプロシージャ内のトランザクションの簡単なエラー処理を示しています。パラメータ値「fail」が渡される場合、ストアドプロシージャは、存在する2つのテーブルと存在しない1つのテーブルから削除を試み、ストアドプロシージャはエラーをキャッチして、エラーメッセージを返します。パラメータ値「fail」が渡されない場合、プロシージャは存在する2つのテーブルから削除を試み、正常完了します。

テーブルとストアドプロシージャを作成します。

begin transaction;

create table parent(id integer);
create table child (child_id integer, parent_ID integer);

-- ----------------------------------------------------- --
-- Wrap multiple related statements in a transaction,
-- and use try/catch to commit or roll back.
-- ----------------------------------------------------- --
-- Create the procedure
create or replace procedure cleanup(FORCE_FAILURE varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "begin transaction;"} );
  try {
      snowflake.execute( {sqlText: "delete from child where parent_id = 1;"} );
      snowflake.execute( {sqlText: "delete from parent where id = 1;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "delete from no_such_table;"} );
          }
      snowflake.execute( {sqlText: "commit;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "rollback;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

commit;

ストアドプロシージャを呼び出して、エラーを強制します。

call cleanup('fail');
+----------------------------------------------------------+
| CLEANUP                                                  |
|----------------------------------------------------------|
| Failed: SQL compilation error:                           |
| Object 'NO_SUCH_TABLE' does not exist or not authorized. |
+----------------------------------------------------------+

エラーを強制せずにストアドプロシージャを呼び出します。

call cleanup('do not fail');
+-----------+
| CLEANUP   |
|-----------|
| Succeeded |
+-----------+