Zenn
DATUM STUDIOPublicationへの投稿
😵‍💫

[小ネタ]Snowflake で 自動コミットが無効になると何が起こるか知ってるか？

あれ
2025/03/09に公開
Snowflake
tech

A. 私が混乱する。 それだけなら大して業務に影響ないな！ヨシ！

冗談はさておき。
Snowflake ではデフォで自動コミットが有効になっています。そのため、DML・クエリの実行時、明示的にトランザクションを開始・終了せずとも、ステートメントが成功すると自動的にコミットされます。
なのですが、アカウントレベルで自動コミットが無効（AUTOCOMMIT = false）なアカウントを触っていたら、成功したはずの DML（具体的には COPY INTO）がテーブルに反映されていなかったので、混乱した…というお話です。

トランザクションのページを読んでいる方にとっては常識的な内容です！

https://docs.snowflake.com/ja/sql-reference/transactions

事の発端

やったこと、現象

  • Streamlit in Snowflake アプリを作成
  • Streamlit アプリのコード中で Snowpark の Session.sql() を使って COPY INTO <テーブル> コマンドを実行
  • COPY INTO <テーブル> の結果、
    1. ロード成功した行数が1以上になっているのを確認（Session.sql() の戻り値から取得）
    2. Session.table(<テーブル>) で、ロード対象テーブルに追加レコードが存在しているのを確認（★）

この状態で、

  • ブラウザの別タブで Snowsight ワークシートを開く
  • select * from <テーブル> を実行

すると、（★）でロードに成功したはずのレコードが、ない ？？

原因

他の Snowflake アカウントに同じアプリのコードを持ち込んで、同じ処理をしてみると、ロードが成功したらちゃんとレコードが存在していました。なんじゃこりゃ...
この現象が発生するアカウント、発生しないアカウント、何が違うんだ...？

調べていくと、この現象が発生するアカウントでは、アカウントレベルで自動コミットが無効化（AUTOCOMMIT = false）されていることがわかりました。自動コミットが無効化されているとこうなります。

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

  • 暗黙の BEGIN TRANSACTION は次の場所で実行されます。
    • トランザクションが終了した後の最初の DML ステートメント。これは、前のトランザクションが何で終了したかに関係なく当てはまります（例: DDL ステートメント、または明示的な COMMIT または ROLLBACK）。
    • AUTOCOMMIT を無効にした後の最初の DML ステートメント。
  • 暗黙的な COMMIT は、次の場所で実行されます（トランザクションがすでにアクティブな場合）。
    • DDL ステートメントが実行される場合。
    • 新しい値が TRUE か FALSE か、また新しい値が前の値と異なるかどうかに関係なく、 ALTER SESSION SET AUTOCOMMIT ステートメントが実行される場合。たとえば、すでに FALSE であるときに AUTOCOMMIT を FALSE に設定しても、暗黙の COMMIT が実行されます。
  • 暗黙的な ROLLBACK は、次の場所で実行されます（トランザクションがすでにアクティブな場合）。
    • セッションの終了時。
    • ストアドプロシージャの終了時。
      ストアドプロシージャのアクティブなトランザクションが、明示的または暗黙的に開始されたかどうかに関係なく、Snowflakeはアクティブなトランザクションをロールバックし、エラーメッセージを発行します。

あ、こりゃあ、コミットされてないだけだわ。。。ちゃんちゃん。

せっかくだから検証しよう

私は AUTOCOMMIT を全然わかっていないということがわかったので、AUTOCOMMIT が無効になっているときの動作をちょっと検証してみます。

前提

アカウントレベルで AUTOCOMMIT = false な状態で進めます。
（検証のため、トライアルアカウントで AUTOCOMMIT を false にするところから始めます）

コミット動作の確認用に TEST テーブルを置いておきます。

1️⃣ 試しに Streamlit in Snowflake でDMLを実行してみる

まず初期化。

単純に insert するだけのコマンドを含んだコードを書きます。

コード
# Import python packages
import streamlit as st
from snowflake.snowpark.context import get_active_session
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col

session = get_active_session()

tableName = 'TEST.PUBLIC.TEST'

res = session.sql(f'''
    insert into {tableName}
    values ('1', 'hoge')
''').collect()
st.table(res)

st.table(session.table(tableName))
# session.sql('commit').collect()

SiS では insert 出来ているように見えるものの、、、

別ワークシートで見ると、insertされていない。現象が再現しました。

commit で明示的にコミットすれば、他のシートでも insert されたレコードが見えます。

2️⃣ 試しにストアドプロシージャでDMLを実行してみる

Python ワークシートで試してみる。単純に insert するだけの Python（Snowpark）ストアドを書きます。

コード
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col

def main(session: snowpark.Session): 
    tableName = 'TEST.PUBLIC.TEST'
    session.sql(f'''
        insert into {tableName}
        values ('1', 'hoge')
    ''').collect()

    return session.table(tableName)

実行すると怒られました。ストアドならエラーで原因がわかった、ということか...。

こちらも、commit を入れて明示的にコミットすれば解消します。（なんだかんだで4回程この関数を実行しているの図）

おわりに

トランザクションのベストプラクティスには「Snowflakeは、AUTOCOMMIT を有効にして、可能な限り明示的なトランザクションを使用することをお勧めします。」とも記載されていたりします（2025年3月9日現在）。AUTOCOMMIT = false のご利用は計画的に。

DATUM STUDIO
DATUM STUDIOPublication

Discussion