【Snowflake】Snowflake ～ トランザクション ～

Snowflake

■ はじめに

　業務作業で、でかいデータを使っていて、
途中で止めなくてはいけない（Abort）場合に、
トランザクションを張っていたら、
ちゃんとロールバックしているかが気になったので
Snowflake の トランザクションについて調べてみた。
 => 結論からいうと、ロールバックしてくれるみたい
　（詳細は「実験３：ストアド内でbegin transaction/commit」を参照）

トランザクションに関する詳細は、以下の関連記事を参照のこと。

【DB】トランザクション / Transaction
https://dk521123.hatenablog.com/entry/2010/07/06/011843

目次

【１】SQL文
　１）BEGIN TRANSACTION
　２）COMMIT
　３）ROLLBACK
【２】コマンド
　１）SHOW TRANSACTIONS
　２）DESCRIBE TRANSACTION
　３）SHOW LOCKS
　４）SYSTEM$ABORT_TRANSACTION
【３】トランザクションの動作検証
　実験１：begin transaction/commit
　実験２：begin transaction/rollback
　実験３：ストアド内でbegin transaction/commit

【１】SQL文

１）BEGIN TRANSACTION

https://docs.snowflake.com/ja/sql-reference/sql/begin.html

-- 明示的なトランザクション開始
BEGIN TRANSACTION;

-- トランザクションに名前を付けたい場合
BEGIN TRANSACTION NAME demo_transaction_1;

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

より抜粋
~~~~~~~
Snowflakeは、同義語 BEGIN WORK と BEGIN TRANSACTION をサポートしています。
Snowflakeは、 BEGIN TRANSACTION の使用を推奨します。
~~~~~~~

２）COMMIT

-- トランザクションをコミット
COMMIT;

https://docs.snowflake.com/ja/sql-reference/sql/commit.html

３）ROLLBACK

-- トランザクションをロールバック
ROLLBACK;

https://docs.snowflake.com/ja/sql-reference/sql/rollback.html

【２】コマンド

１）SHOW TRANSACTIONS

-- 実行中のすべてのトランザクションをリスト

補足：select current_transaction() 

-- 以下でも確認できる
select current_transaction();

-----------------------+
 CURRENT_TRANSACTION() |
-----------------------+
 1432071523422         |
-----------------------+

-- select last_transaction();
-- だと最後に完了したトランザクションを取得

２）DESCRIBE TRANSACTION

https://docs.snowflake.com/ja/sql-reference/sql/desc-transaction.html
構文 

DESC[RIBE] TRANSACTION <transaction_id>;

例 

desc transaction 1651535571261000000;

３）SHOW LOCKS

https://docs.snowflake.com/ja/sql-reference/sql/show-locks.html

-- リソースをロックしている実行中のすべてのトランザクションを一覧表示
SHOW LOCKS;
--------------+--------+---------------+---------------------------------+---------+---------------------------------+--------------------------------------+
   session    | table  |  transaction  |     transaction_started_on      | status  |           acquired_on           |               query_id               |
--------------+--------+---------------+---------------------------------+---------+---------------------------------+--------------------------------------+
 103079321618 | ORDERS | 1442254688149 | Mon, 14 Sep 2015 11:18:08 -0700 | HOLDING | Mon, 14 Sep 2015 11:18:16 -0700 | 6a478582-9e8c-4603-b5bf-89b14c042e1a |
 103079325702 | ORDERS | 1442255439400 | Mon, 14 Sep 2015 11:30:39 -0700 | WAITING | [NULL]                          | 82fea8a6-a679-4de1-b6e9-7a80905831cf |
--------------+--------+---------------+---------------------------------+---------+---------------------------------+--------------------------------------+

４）SYSTEM$ABORT_TRANSACTION

構文 

-- 実行中のトランザクションを中断
SYSTEM$ABORT_TRANSACTION(<transaction_id>);

-- トランザクション IDsを取得するには、
-- SHOW TRANSACTIONS または SHOW LOCKS コマンドを使用

例 

select system$abort_transaction(1442254688149);

-----------------------------------------+
 SYSTEM$ABORT_TRANSACTION(1442254688149) |
-----------------------------------------+
 Aborted transaction id: 1442254688149   |
-----------------------------------------+

【３】トランザクションの動作検証

* 以下のサイトも色々と実験しているので、確認してみるといいかも。

https://dev.classmethod.jp/articles/snowflake-worksheet-tips/

実験１：begin transaction/commit

https://docs.snowflake.com/ja/sql-reference/transactions.html#failed-statements-within-a-transaction

-- より抜粋

-- 準備
create table table1 (i int);

-- トランザクション開始
begin transaction;

-- INSERT開始
insert into table1 (i) values (1);

-- トランザクション確認（トランザクションあり）
select current_transaction();
-- 確認（データが入っている）
select i from table1 order by i;

insert into table1 (i) values ('This is not a valid integer.');    -- FAILS!（無視して次に実行する）

-- コミット
commit;

-- トランザクション確認（コミットしたので、トランザクションなし）
select current_transaction();
-- 確認（コミットした場合、データが入っている）
select i from table1 order by i;

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

-- 後片付け
drop table table1;

実験２：begin transaction/rollback

https://docs.snowflake.com/ja/sql-reference/sql/rollback.html#examples

-- より抜粋

-- 準備
create table a1(i int);

-- [0] 検証前にデータ確認
select count(*) from a1;

----------+
 COUNT(*) |
----------+
 0        |
----------+

-- [1] トランザクション開始
begin name t4;

-- [2] トランザクション確認
select current_transaction();

-----------------------+
 CURRENT_TRANSACTION() |
-----------------------+
 1432071523422         |
-----------------------+

-- [3] トランザクション中にINSERT
insert into a1 values (1), (2);

-------------------------+
 number of rows inserted |
-------------------------+
 2                       |
-------------------------+

-- 確認（ロールバック前で、データが入っている）
select i from a1 order by i;

-- [4] ロールバック
rollback;

-- [5] ロールバックしたから、[3]のINSERTは戻され、カウントは0
select count(*) from a1;

----------+
 COUNT(*) |
----------+
 0        |
----------+

-- [6] トランザクション確認（ロールバックしたのでNULL）
select current_transaction();

-----------------------+
 CURRENT_TRANSACTION() |
-----------------------+
 [NULL]                |
-----------------------+

-- [7] 最終実行されたトランザクションを確認
select last_transaction();

--------------------+
 LAST_TRANSACTION() |
--------------------+
 1432071523422      |
--------------------+

-- 後片付け
drop table a1;

実験３：ストアド内でbegin transaction/commit

-- [1] ダミーテーブル作成
create table dummy_data (id integer);

-- [2] 実行前に最終トランザクションを確認
select last_transaction();

-- [3] 実行
execute immediate $$
declare
  counter integer;
begin
  counter := 0;
  -- トランザクション開始
  begin transaction name ex3;

  -- 重い処理開始（この間で中断したらどうなる？）
  -- 中断の仕方の詳細は、以下の関連記事に記載。
  -- https://dk521123.hatenablog.com/entry/2022/12/09/152837
  loop
    counter := counter + 1;
    -- ！！注意！！この数は、環境によって調整を、、、
    if (counter > 100000) then
      break;
    end if;
    insert into dummy_data (id) values (:counter);
  end loop;

  -- コミット
  commit;

  return counter;
end;
$$
;

-- [4] 実行途中でAbortボタン押下
-- メッセージ「Stored procedure execution error: 
-- Scoped transaction started in stored procedure is incomplete
--  and it was rolled back.」
-- => メッセージからロールバックしてくれているみたい

-- [5] 確認
select count(*) from dummy_data; -- 0 なのでロールバックされている模様
select current_transaction(); -- NULL (トランザクションなし)
select last_transaction(); -- [2]と比較（別のトランザクションになっている）

-- 後片付け
drop table dummy_data;

関連記事

Snowflake ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～ 入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～ クエリの中断 ～
https://dk521123.hatenablog.com/entry/2022/12/09/152837
【DB】トランザクション / Transaction
https://dk521123.hatenablog.com/entry/2010/07/06/011843