トランザクション

トランザクションは、1つの単位としてコミットまたはロールバックされる一連の SQL ステートメントです。

このトピックの内容:

概要

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

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

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

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

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

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

用語

このトピックの内容:

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

  • 「 DML 」という用語は、INSERT、UPDATE、DELETE、MERGE、および TRUNCATE を指します。

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

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

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

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

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

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

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

注釈

明示的なトランザクションには、 DML ステートメントとクエリステートメントのみを含める必要があります。DDL ステートメントは、暗黙的にアクティブトランザクションをコミットします(詳細については、 DDL セクションを参照)。

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

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

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

DDL

各 DDL ステートメントは個別のトランザクションとして実行されます。

トランザクションがアクティブなときに DDL ステートメントを実行すると、 DDL ステートメントは次のようになります。

  1. アクティブなトランザクションを暗黙的にコミットします。

  2. DDL ステートメントを個別のトランザクションとして実行します。

DDL ステートメントは独自のトランザクションであるため、 DDL ステートメントをロールバックすることはできません。DDL を含むトランザクションは、 ROLLBACK を実行する前に完了しています。

DDL ステートメントの直後に DML またはクエリステートメントが続く場合、その DML またはクエリステートメントは暗黙的に新しいトランザクションを開始します。

AUTOCOMMIT

Snowflakeは AUTOCOMMIT パラメーターをサポートしています。AUTOCOMMIT のデフォルト設定はオンです。

AUTOCOMMIT が有効になっている場合:

  • 明示的なトランザクションの外部にある各ステートメントは、それ自体の暗黙的な単一ステートメントのトランザクションの内部にあるかのように扱われます。つまり、そのステートメントは、成功すると自動的にコミットされ、失敗すると自動的にロールバックされます。

    明示的なトランザクション内のステートメントは、 AUTOCOMMIT の影響を受けません。たとえば、明示的な BEGIN TRANSACTION ... ROLLBACK 内のステートメントは、 AUTOCOMMIT が TRUE であってもロールバックされます。

AUTOCOMMIT が無効になっている場合、

  • 暗黙的な BEGIN TRANSACTION は次で実行されます。

    • トランザクションが終了した後の最初の DML ステートメントまたはクエリステートメント。これは、前のトランザクションが何で終了したかに関係なく当てはまります(例: DDL ステートメント、または明示的なコミットまたはロールバック)。

    • AUTOCOMMIT を無効にした後の最初の DML ステートメントまたはクエリステートメント。

  • 暗黙的な COMMIT は、次の場所で実行されます(トランザクションがすでにアクティブな場合)。

    • DDL ステートメントの実行。

    • 新しい値が TRUE または FALSE であるかどうか、また新しい値が前の値と異なるかどうかに関係なく、 ALTER SESSION SET AUTOCOMMIT ステートメントの実行。たとえば、すでに FALSE であるときに AUTOCOMMIT を FALSE に設定しても、暗黙の COMMIT が実行されます。

  • 暗黙的な ROLLBACK は、次の場所で実行されます(トランザクションがすでにアクティブな場合)。

    • セッションの終了。

    • ストアドプロシージャの終了。

      ストアドプロシージャのアクティブなトランザクションが、明示的または暗黙的に開始されたかどうかに関係なく、Snowflakeはアクティブなトランザクションをロールバックし、エラーメッセージを発行します。

ご用心

ストアドプロシージャ 内の 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;

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

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

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

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

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

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

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

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

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

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

一般に、前のセクションで説明したルールは、ストアドプロシージャにも適用されます。このセクションでは、ストアドプロシージャに固有の追加情報を提供します。

トランザクションは、ストアドプロシージャ内にある場合もあれば、ストアドプロシージャがトランザクション内にある場合もあります。ただし、トランザクションは、一部をストアドプロシージャの内部と外部に配置したり、あるストアドプロシージャで開始した後、異なるストアドプロシージャで終了したりすることはできません。

例:

  • ストアドプロシージャを呼び出す前にトランザクションを開始してから、ストアドプロシージャ内でトランザクションを完了することはできません。これを行おうとすると、Snowflakeは次のようなエラーを報告します Modifying a transaction that has started at a different scope is not allowed.

  • ストアドプロシージャ内でトランザクションを開始し、プロシージャから戻った後にトランザクションを完了することはできません。トランザクションがストアドプロシージャ内で開始され、ストアドプロシージャの終了時にまだアクティブである場合、エラーが発生し、トランザクションはロールバックされます。

これらのルールは、ネストされたストアドプロシージャにも適用されます。プロシージャ A がプロシージャ B を呼び出す場合、 BA で開始されたトランザクションを完了できません。その逆も同様です。 A の各 BEGIN TRANSACTION には A の対応する COMMIT (または ROLLBACK)が必要であり、 B の各 BEGIN TRANSACTION には B の対応する COMMIT (または ROLLBACK)が必要です。

ストアドプロシージャにトランザクションが含まれている場合、そのトランザクションには、ストアドプロシージャの本体の一部またはすべてを含めることができます。たとえば、次のストアドプロシージャでは、一部のステートメントのみがトランザクションに含まれています。(この例とそれに続く例のいくつかでは、簡単にするために疑似コードを使用。)

create procedure ...
    as
    $$
        ...
        statement1;

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;

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

以下のセクションでは、次を説明します。

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

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

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

最も単純なケースでは、次の条件が満たされた場合、ストアドプロシージャはトランザクション内にあると見なされます。

  • ストアドプロシージャが呼び出される前に、 BEGIN TRANSACTION が実行されます。

  • 対応する COMMIT (または ROLLBACK)は、ストアドプロシージャの完了後に実行されます。

  • ストアドプロシージャの本体には、明示的および暗黙的な BEGIN TRANSACTION および COMMIT (または ROLLBACK)は含まれていません。

トランザクション内のストアドプロシージャは、囲んでいるトランザクションのルールに従います。

  • トランザクションがコミットされると、プロシージャ内のすべてのステートメントがコミットされます。

  • トランザクションがロールバックされると、プロシージャ内のすべてのステートメントがロールバックされます。

次の擬似コードは、トランザクション内で完全に呼び出されるストアドプロシージャを示しています。

CREATE PROCEDURE my_procedure()
...
AS
$$
    statement X;
    statement Y;
$$;

BEGIN TRANSACTION;
statement W;
CALL my_procedure();
statement Z;
COMMIT;

これは、ステートメントの次のシーケンスを実行するのと同じです。

begin;
statement W;
statement X;
statement Y;
statement Z;
commit;

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

ストアドプロシージャ内で0、1、または複数のトランザクションを実行できます。次の擬似コードは、1つのストアドプロシージャ内の2つのトランザクションの例を示しています。

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;

このコードでは、4つの別々のトランザクションが実行されます。各トランザクションは、プロシージャの外部で開始および完了するか、プロシージャの内部で開始および完了します。トランザクションの一部がストアドプロシージャの内部で、別の一部がプロシージャの外部というように、境界を越えて分割されたトランザクションはありません。別のトランザクションにネストされているトランザクションはありません。

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

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

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

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

注釈

「内部」および「外部」という用語は、ネストされたストアドプロシージャの呼び出しなど、ネストされた操作を説明するときに一般的に使用されます。ただし、Snowflakeでのトランザクションは実際には「ネスト」されていません。したがって、トランザクションを参照する際の混乱を減らすために、このドキュメントでは、「内部」および「外部」ではなく、「囲まれた」および「囲む」という用語を頻繁に使用します。

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

Illustration of two stored procedures, each with its own scoped transaction.

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

Illustration of two stored procedures and two scoped transactions, in which one transaction includes some statements from the inner stored procedure as well as all statements from the outer stored procedure.

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

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

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

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

Illustration of two stored procedures that create improperly-scoped transactions.

次の例は、時間的に重複する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;

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

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

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

  • 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;

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

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

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;

この例では、挿入される値は「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 分離

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

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

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

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

リソースのロック

トランザクション操作は、リソースが変更されている間に、テーブルなどのリソースのロックを取得します。ロックは、ロックが解除されるまで他のステートメントがリソースを変更するのをブロックします。

ほとんどの場合、次のガイドラインが適用されます。

  • COMMIT 操作(AUTOCOMMIT と明示的な COMMIT の両方を含む)はリソースをロックしますが、通常は短時間だけです。

  • UPDATE、DELETE、および MERGE ステートメントは、他の UPDATE、 DELETE、および MERGE ステートメントと並行して実行することを一般的に妨げるロックを保持します。

  • ほとんどの INSERT および COPY ステートメントは、新しいパーティションのみを書き込みます。これらのステートメントは、多くの場合、他の INSERT および COPY 操作と並行して実行できます。また、 UPDATE、 DELETE、または MERGE ステートメントと並行して実行できる場合もあります。

ステートメントによって保持されているロックは、トランザクションの COMMIT または ROLLBACK で解放されます。

ロック待機タイムアウト

ブロックされたステートメントは、待機していたリソースのロックを取得するか、リソースが利用可能になるのを待ってタイムアウトします。ステートメントがブロックする時間(秒単位)は、 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.                                                                 |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+

デッドロック

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

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

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

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

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

トランザクションのコマンドと機能

Snowflakeは、トランザクションをサポートするために次の SQL コマンドを提供します。

さらに、Snowflakeは、セッション内のトランザクションに関する情報を取得するために、次のコンテキスト関数を提供します。

トランザクションの中止

トランザクションがセッションで実行されていて、セッションが突然切断され、トランザクションのコミットまたはロールバックが妨げられた場合、トランザクションはリソースで保持しているロックを含め、デタッチされた状態のままになります。これが発生した場合、トランザクションの中止が必要となる場合があります。

実行中のトランザクションを中止するには、トランザクションを開始したユーザーまたはアカウント管理者が、システム関数 SYSTEM$ABORT_TRANSACTION を呼び出します。

トランザクションがユーザーによって中止されない場合は、

  • 別のトランザクションが同じテーブルでロックを取得するのをブロックし、 かつ 5分間アイドル状態にあるトランザクションは、自動的に中止され、ロールバックされます。

  • 他のトランザクションが同じテーブルを変更するのをブロック せず、4時間よりも古いトランザクションは、自動的に中止され、ロールバックされます。

ベストプラクティス

  • トランザクションには、関連するステートメントが含まれている必要があり、一緒に正常に完了または失敗する必要があります。例えば、あるアカウントからお金を引き出し、同じお金を別のアカウントに預けるなどです。ロールバックが発生した場合、支払人または受取人のいずれかが最終的にお金を受け取ります。お金が「消える」ことはありません(一方のアカウントから引き落とされるが、もう一方のアカウントに預金されることはありません)。

    一般に、1つのトランザクションには関連するステートメントのみを含める必要があります。ステートメントの粒度を低くすると、トランザクションがロールバックされるときに、実際にはロールバックする必要がなかった有用な作業がロールバックされる可能性があることを意味します。

  • トランザクションが大きいと、パフォーマンスが向上する場合があります。

    前の箇条書きでは、本当にコミットまたはロールバックする必要のあるステートメントのみをグループ化することの重要性を強調しましたが、より大きなトランザクションが役立つ場合があります。Snowflakeでは、ほとんどのデータベースと同様に、トランザクションの管理はリソースを消費します。例えば、1つのトランザクションに10行を挿入する方が、10の個別のトランザクションにそれぞれ1行を挿入するよりも一般的に高速で安価です。複数のステートメントを1つのトランザクションに組み合わせると、パフォーマンスを向上させることができます。

  • トランザクションが大きすぎると、並列処理が減少したり、デッドロックが増加したりする可能性があります。パフォーマンスを向上させるために無関係なステートメントをグループ化することにした場合(前の箇条書きで説明したように)、トランザクションがリソースの ロック を取得する可能性があり、他のクエリを遅らせたり、 デッドロック につながる可能性があることに注意してください。

  • Snowflakeは、AUTOCOMMIT を有効にして、可能な限り明示的なトランザクションを使用することをお勧めします。明示的なトランザクションを使用すると、人間の読者はトランザクションの開始位置と終了位置を簡単に確認できます。これを AUTOCOMMIT と組み合わせると、たとえばストアドプロシージャの最後で、コードに意図しないロールバックが発生する可能性が低くなります。

  • 単に新しいトランザクションを暗黙的に開始するために AUTOCOMMIT を変更することは避けてください。代わりに、 BEGIN TRANSACTION を使用して、新しいトランザクションが開始される場所をより明確にします。

  • 連続して複数の BEGIN TRANSACTION ステートメントを実行することは避けてください。余分な BEGIN TRANSACTION ステートメントを使用すると、トランザクションが実際に開始される場所を確認しにくくなり、 COMMIT/ROLLBACK コマンドを対応する BEGIN TRANSACTION と組み合わせることが難しくなります。

トランザクション ID

すべてのSnowflakeトランザクションには、トランザクションの ID として機能する一意の開始時間(ミリ秒の精度)が割り当てられます。トランザクションの開始時間の例については、 BEGIN TRANSACTION の をご参照ください。

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

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

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

テーブルを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 |
|----|
+----+

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

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

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

次のいくつかの例では、以下に示すテーブルとストアドプロシージャを使用します。適切なパラメータを渡すことにより、呼び出し元は、ストアドプロシージャ内で BEGIN TRANSACTIONCOMMIT、 および 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 "";
$$;

3つのレベルの中間レベルをコミットする

この例には、3トランザクションが含まれています。この例では、「中間」レベル(最も外側のトランザクションに囲まれ、最も内側のトランザクションを囲むトランザクション)をコミットします。これにより、最も外側のトランザクションと最も内側のトランザクションがロールバックされます。

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;

その結果、中間トランザクション(12、21、および23)の行のみがコミットされます。外部トランザクションと内部トランザクションの行はコミットされません。

-- 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 |
+----+------------+

3つのレベルの中間レベルをロールバックする

この例には、3トランザクションが含まれています。この例では、「中間」レベル(最も外側のトランザクションに囲まれ、最も内側のトランザクションを囲むトランザクション)をロールバックします。これにより、最も外側のトランザクションと最も内側のトランザクションがコミットされます。

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;

その結果、中間トランザクション(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          |
|----+---------------|
|  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 |
+-----------+