Snowflakeのトランザクションを試してみた
かわばたです。
表題のとおり、Snowflakeのトランザクション処理について試していきます。
【公式ドキュメント】
対象読者
- Snowflakeのトランザクション処理について興味のある方
検証環境と事前準備
検証環境
- SnowflakeトライアルアカウントEnterprise版
事前準備
使用するテーブル等を作成しました。
-- 使用するロールとウェアハウスを設定
USE ROLE SYSADMIN;
USE WAREHOUSE compute_wh;
-- データベースとスキーマを作成
CREATE OR REPLACE DATABASE transaction_hands_on_db;
USE DATABASE transaction_hands_on_db;
CREATE OR REPLACE SCHEMA public;
USE SCHEMA public;
-- テスト用のテーブルを作成
CREATE OR REPLACE TABLE tx_test (
id INT,
description VARCHAR(50),
inserted_at TIMESTAMP_LTZ
);
トランザクションの概要
トランザクションは、アトミックユニットとして処理される一連の SQL ステートメントです。トランザクション内のすべてのステートメントは、一緒に適用（コミット）または取り消し（ロールバック）されます。Snowflakeトランザクションは、 ACIDプロパティを保証します。
上記ドキュメントからの引用となりますが、ざっくりな説明は一連のSQL文を一つの単位として処理し、すべてが成功するか、すべてが失敗するかのどちらかになる仕組みとなります。
ACID特性を保証しており詳細は下記のとおりです。
- Atomicity（原子性）: トランザクション内のすべての操作が成功するか、すべてが失敗するか
- Consistency（一貫性）: データベースの整合性が保たれる
- Isolation（分離性）: 複数のトランザクションが同時実行されても互いに影響しない
- Durability（永続性）: コミットされた変更は永続的に保存される
AUTOCOMMITについて
SnowflakeはAUTOCOMMITパラメータをサポートしています。AUTOCOMMITのデフォルト設定はTRUE（有効）です。
Snowflakeのデフォルトの動作モードは「自動コミット」です。これは、明示的な$BEGIN TRANSACTION;$がない状態で実行された個々のSQLステートメントが、それぞれ独立した単一のトランザクションとして扱われることを意味します。ステートメントが正常に完了すれば即座にコミットされ、失敗すれば自動的にロールバックされます。
READ COMMITTED分離レベルについて
現在、テーブルでサポートされている分離レベルはREAD COMMITTEDのみです。READ COMMITTED分離レベルでは、文の実行開始前にコミットされたデータのみが参照されます。コミットされていないデータは参照されません。
Snowflakeがサポートするのは$READ COMMITTED$のみです。
READ COMMITTEDは、ダーティリード（未コミットデータの読み取り）を防ぐ一方で、ノンリピータブルリード（同じクエリでも実行タイミングによって結果が変わる）は許可する分離レベルです。
実際に試してみた
デフォルト設定 (AUTOCOMMIT = TRUE) でのDML実行
$AUTOCOMMIT$の設定を確認します。
SHOW PARAMETERS LIKE '%AUTOCOMMIT%';
下記のとおり、value列がtrueになっていることが確認できました。
tx_test テーブルにデータを1行挿入します。
-- データの挿入
INSERT INTO tx_test (id, description, inserted_at) VALUES (1, 'Autocommit ON', CURRENT_TIMESTAMP());
直後に、別のセッションからテーブルをSELECTします。
SELECT * FROM tx_test;
当然、挿入したid = 1のデータが即座に確認できます。
$AUTOCOMMIT = TRUE$の典型的な動作です。実行されたINSERT文は、成功した瞬間に自動的にコミットされる「暗黙的な単一ステートメントトランザクション」として扱われます。
AUTOCOMMIT = FALSEへの変更とDMLの挙動変化
$ALTER SESSION$コマンドで、$AUTOCOMMIT$をFALSEに変更します。
ALTER SESSION SET AUTOCOMMIT = FALSE;
$AUTOCOMMIT$の設定を確認します。
SHOW PARAMETERS LIKE '%AUTOCOMMIT%';
下記のとおり、値がfalseとなっていることが確認できました。
新しいデータを挿入します。
INSERT INTO tx_test (id, description, inserted_at) VALUES (2, 'Autocommit OFF', CURRENT_TIMESTAMP());
直後に、別のセッションからテーブルをSELECTします。
SELECT * FROM tx_test;
IDが2のものを挿入しましたが、挿入されていないことが確認できました。
$COMMIT;$を明示的に実行してトランザクションを終了させます。
COMMIT;
再度SELECT文で確認すると、挿入が完了していました。
$AUTOCOMMIT = FALSE$の環境では、$COMMIT;$または$ROLLBACK;$が明示的に実行されるまで継続します。そのため、コミット前の変更は他のセッションからは確認できない形となります。
BEGIN TRANSACTIONによる複数DMLの処理
$AUTOCOMMIT$をデフォルトの$AUTOCOMMIT = TRUE$に戻しておきます。
ALTER SESSION SET AUTOCOMMIT = TRUE;
$BEGIN TRANSACTION;$を実行します。
BEGIN TRANSACTION;
複数のDMLを実行します。(insertとupdate)
-- セッション1で実行
INSERT INTO tx_test (id, description, inserted_at) VALUES (3, 'Explicit TXN - Insert', CURRENT_TIMESTAMP());
UPDATE tx_test SET description = 'Explicit TXN - Update' WHERE id = 1;
id=3の行は見えず、id=1のdescriptionも更新されていないことが確認できます。
$COMMIT;$を明示的に実行してトランザクションを終了させます。
COMMIT;
今度は INSERT と UPDATE の両方の変更が一度に反映されていることが確認できました。
ROLLBACKによる処理の取り消し
トランザクションの開始と削除を下記のとおり行います。
BEGIN TRANSACTION;
DELETE FROM tx_test WHERE id = 2;
SELECT文で確認すると、下記のとおりid=2が削除されていることが分かります。
ここでコミットの代わりに$ROLLBACK;$を実行します。
ROLLBACK;
SELECT文で確認すると、下記のとおり削除されたレコードが元に戻っていることが確認できます。
DDLステートメントが引き起こす「暗黙的コミット」
他のデータベースシステムでは$BEGIN$と$COMMIT$のブロック内であればDDLステートメントもトランザクションの一部となるケースが多いですが、Snowflakeでは挙動が異なります。
トランザクションの開始と挿入を下記のとおり行います。
BEGIN TRANSACTION;
INSERT INTO tx_test (id, description, inserted_at) VALUES (999, 'Before DDL', CURRENT_TIMESTAMP());
SELECT文で確認すると、下記のとおりid=999はコミットされる前なのでクエリ結果がでません。
トランザクションの途中でCREATE TABLEのようなDDL文を実行します。
CREATE TABLE dummy_table (col1 INT);
再度SELECT文で確認すると、下記のとおりid=999が暗黙的にコミットされていることが分かります。
最後に
基本的には$AUTOCOMMIT = TRUE$のままで、必要な際は明示的に$BEGIN TRANSACTIONを使用してトランザクション制御を行う形が良いと思います。（$BEGIN TRANSACTIONが優先度が高いので、$AUTOCOMMIT = TRUE$のまま制御できる）
最後にDDLの暗黙的コミットは特に注意が必要で、あまりないと思いますがトランザクション制御とDDL文を組み合わせている場合は確認が必要です。
この記事が何かの参考になれば幸いです！
