Snowflakeのワークシートでのトランザクションと変数の挙動まとめ(小ネタ)

Snowflakeウェブインターフェースのワークシート(Worksheet)上で、トランザクションやセッション変数がどのように動作するかを検証する。
2020.07.30

データアナリティクス事業本部、池田です。
Snowflakeのウェブインターフェースには、ワークシート という機能があり、ブラウザからクエリを発行することができます。 もちろん公式ドキュメントでも説明があり、弊社でもブログにされていました。

セッションはブラウザ上のワークシートごとらしいのですが、 「じゃあトランザクションどうなるの?」 「途中でブラウザ落ちたらどうなるの?」 「COMMITし忘れたらどうなるの?」 「そういえばクエリで 変数 が使えたけど、あいつらいつまで生きるの?」 と疑問に思いましたので、検証することにしました。

検証内容&結果

検証の方針・内容

Snowflakeワークシート上で、
【トランザクションを開始】 →【セッション変数を宣言&テーブルを更新】 →【何らかの操作など(下記)】 →【トランザクション・セッション変数を確認】
というフローで検証します。
また、上記フローなら影響は無いと思いますが、 AUTOCOMMIT (自動コミット)は有効のままで検証しています。

検証パターンは以下の8つです。

  1. COMMIT; クエリを発行する
  2. ROLLBACK; クエリを発行する
  3. 新たに発行したクエリ(DML)でエラーが発生する
  4. ワークシートを閉じる
  5. ブラウザを閉じる
  6. Snowflakeウェブインターフェースからログアウトする
  7. ブラウザをリロードする
  8. 時間を空ける

次節に結果を載せますが、 是非それぞれトランザクションとセッション変数がどうなるか予想してお楽しみ下さい!

結果

検証部分が長くなるので、先に結果とよく使いそうなクエリまとめを載せます。

以下のような結果となりました。

No. 検証パターン トランザクション セッション変数 備考
1 COMMIT 終了 有効 トランザクション内で宣言した変数はそのまま残る
2 ROLLBACK 終了 有効 ロールバックしても変数は更新される
3 クエリでエラー発生 継続 有効 エラー原因のクエリのみロールバックされる
エラー以外のクエリはCOMMITにより確定できる
4 ワークシートを閉じる 継続 有効
5 ブラウザを閉じる 継続 ※備考 削除 同じワークシートを使用しても、トランザクションを再開できない
6 ログアウト 終了 削除 COMMITはされない
7 ブラウザのリロード 継続 有効
8 時間を空ける 終了することがある 有効

(あくまで検証の結果であり、ブラウザなどの環境やバージョン、設定により異なる可能性があることはご了承下さい。)


続いて、トランザクションやセッション変数でのよく使いそうなクエリの紹介です。

トランザクション系クエリ

  • 実行中の全てのトランザクションの確認
    SHOW TRANSACTIONS;
  • 現在の(ワークシートの)トランザクションの確認
    SELECT CURRENT_TRANSACTION();
  • トランザクションの中止
    SELECT SYSTEM$ABORT_TRANSACTION({トランザクションのID});
  • 自動コミット設定の確認
    SHOW parameters LIKE 'AUTOCOMMIT';

セッション変数系クエリ

  • 変数の設定
    SET {変数名} = '{値}';
    SET ({変数名1}, {変数名2}, {変数名3}) = ('{値1}', '{値2}', '{値3}');
  • 変数の確認
    SHOW VARIABLES;
  • 変数の削除
    UNSET {変数名};

検証

今回、更新対象として使用したテーブルです。
WS_TEST

CREATE TABLE WS_TEST
("STR" STRING NOT NULL);

ワークシートは3つ使います。

  • 実行用ワークシート
    BEGIN;
    
    SET SESSION_VAR = 'SESSION VAR 1';
    
    INSERT INTO WS_TEST VALUES('test1');
    
    -- COMMIT;
    -- ROLLBACK;
    -- INSERT INTO ERROR_TABLE VALUES('xxx'); -- エラーになるクエリ
    
    SHOW VARIABLES;
    

    このワークシートで、トランザクションを開始( BEGIN )し、 セッション変数を設定し、テーブルに対して登録処理を行います。

  • トランザクション確認用ワークシート
    SHOW TRANSACTIONS;

    トランザクションの状態を確認します。

  • テーブル確認用ワークシート
    SELECT COUNT(*) FROM WS_TEST;

    テーブルにデータが格納されたか確認します。
    (検証パターンごとに全件削除し、0件の状態でスタートします。)

COMMIT クエリを発行する

まずは基本的なパターンです。

  1. トランザクションを開始・セッション変数を設定・テーブルへINSERT (↓選択部分を一括実行)

  2. トランザクションが作成され、 running 状態であることが分かります

  3. この時点では別ワークシート(別セッション)からはテーブルの更新は見えません

  4. COMMITして、変数の状態を確認します

    トランザクション終了後もセッション変数はそのまま参照できる ようです

  5. トランザクションが無くなっています

  6. テーブルが変更されています

ROLLBACK クエリを発行する

基本パターンその2。

  1. トランザクションを開始・セッション変数を設定・テーブルへINSERT

  2. (前の検証パターンとは別のIDで)トランザクションが作成され、 running 状態であることが分かります

  3. ROLLBACKして、変数の状態を確認します

    ROLLBACKしてもセッション変数は更新されたまま のようです

  4. トランザクションが無くなっています

  5. 変更は破棄され、テーブルは変更されていません

新たに発行したクエリ(DML)でエラーが発生する

これもありそうなパターンだと思います。

  1. トランザクションを開始・セッション変数を設定・テーブルへINSERT

  2. トランザクションが作成され、 running 状態であることが分かります

  3. 存在しないテーブルに対してDMLを発行し、エラーを発生させます

  4. セッション変数は更新された状態です

  5. トランザクションは running 状態で継続しています

  6. テーブルは変更されていません

  7. COMMITします

  8. トランザクションが無くなっています

  9. テーブルが変更されています

ドキュメント によると、

明示的に開始されたトランザクションで実行された DML ステートメントが失敗した場合、 DML によって行われた変更はロールバックされます。 ただし、トランザクションがコミットまたはロールバックされるまで、 トランザクションは開いたままになります。

とのことですが、 ロールバックされるのは、エラーのクエリだけ のようです。
(ちなみにエラー後に別の正常なINSERT文を発行してもCOMMITできました。)

ワークシートを閉じる

ワークシートのタブを閉じてみます。

  1. トランザクションを開始・セッション変数を設定・テーブルへINSERT

  2. トランザクションが作成され、 running 状態であることが分かります

  3. トランザクションを開始していたワークシートを閉じます
    トランザクションは running 状態で継続しています

  4. 当該ワークシートを復活させます

  5. COMMITして、変数の状態を確認します

    ⇒セッション変数はそのまま参照できるようです

  6. テーブルが変更されています

何事も無かったように復帰してきましたね…

ブラウザを閉じる

ブラウザが落ちて「あーー」ってなるイメージです。
※以下ではChromeを使用しています。

  1. トランザクションを開始・セッション変数を設定・テーブルへINSERT

  2. トランザクションが作成され、 running 状態であることが分かります

  3. ~~~ブラウザを閉じます~~~
  4. 同じIDでトランザクションが running 状態で継続しています

  5. COMMITして、変数の状態を確認します

    セッション変数は削除 されていたようです

  6. まだトランザクションが running 状態で継続しています

  7. テーブルも変更されていません

    ⇒ワークシート上のセッションは新しくなっており、以前のトランザクションとは紐づいていない ようです

  8. ABORT_TRANSACTION でトランザクションを中止します

  9. トランザクションが無くなっています

別途、ブラウザを閉じた後に SELECT CURRENT_TRANSACTION(); でトランザクションを確認してみましたが、 やはりワークシートとトランザクションの紐づけが無くなっているようでした。
(ちなみにタブを閉じた場合も同じような挙動になりました。)

これは気をつけたいパターンですね。

Snowflakeウェブインターフェースからログアウトする

  1. トランザクションを開始・セッション変数を設定・テーブルへINSERT

  2. トランザクションが作成され、 running 状態であることが分かります

  3. ウェブインターフェースからログアウトします

  4. 再度ログインすると、トランザクションが無くなっています

  5. テーブルは変更されていません

    ⇒ログアウトでは、トランザクションは終了するが、COMMITはされない ようです

  6. セッション変数は削除されています

ブラウザをリロードする

  1. トランザクションを開始・セッション変数を設定・テーブルへINSERT

  2. トランザクションが作成され、 running 状態であることが分かります

  3. ~~~ブラウザをリロードします~~~
  4. 同じIDでトランザクションが running 状態で継続しています

  5. COMMITして、変数の状態を確認します

    ⇒セッション変数はそのまま参照できるようです

  6. テーブルが変更されています

このパターンも何事も無かったように復帰してきましたね。

時間を空ける

  1. トランザクションを開始・セッション変数を設定・テーブルへINSERT

  2. トランザクションが作成され、 running 状態であることが分かります

  3. ~~~約11時間後~~~
  4. 同じIDでトランザクションが running 状態で継続しています

  5. ワークシート上でもトランザクションを確認します

    ⇒トランザクションは継続しています

  6. COMMITして、変数の状態を確認します

    ⇒セッション変数は有効でした

  7. テーブルが変更されています

11時間後の時点では、トランザクションは有効なようです。(最大で13時間まで検証しました…)
ところが、何度かこのパターンを繰り返すと、 トランザクションが終了させられて↓のようにエラーが生じることがありました。

ちなみに、この状態でCOMMITしても、テーブルへの変更は反映されませんでした。

念のため、 LOCK_TIMEOUTSTATEMENT_TIMEOUT_IN_SECONDS を変更して検証してみましたが、影響はなく、 上記トランザクション終了の発生条件を特定することはできませんでした……

おわりに

私としてはけっこう予想外な結果となりました。 全体的にトランザクションしぶといなーと思いました。
ドキュメントには ロックしてしまった場合 の話もあるので、そちらも併せてご覧いただけたらと思います。

参考文献