【Snowflake】Hybrid Tableにデッドロックを仕掛ける - 行ロックを理解する

はじめに

Hybrid Tableは、OLAPとOTAPの両方の用途に対応した処理性能を示す、通常のテーブルとは異なるテーブル形式です。Snowflakeの通常のテーブルはOLAP用途に適合した処理性能でした。

上記の性能の違いのため、Hybrid Tableは行レベルロック、通常のテーブルはテーブルレベルロックという違いがあります。今回は、この違いを理解するため、Hybrid Tableと通常のテーブルにデッドロックを仕掛けます。

準備

下図のようにrootタスク起動で2つの子タスクによって、同じテーブルを同時更新します。

コード

同一トランザクション内で2つの同一テーブルの異なる行に対するupdateを定義します。2つのプロシージャ内でそれを順番逆にします。

テーブル作成

// create hybrid table
CREATE OR REPLACE HYBRID TABLE icecream (
id NUMBER PRIMARY KEY AUTOINCREMENT START 1 INCREMENT 1,
col1 VARCHAR NOT NULL,
col2 VARCHAR NOT NULL,
updated_ts_task1 timestamp_ntz,
updated_ts_task2 timestamp_ntz
);

INSERT INTO icecream
select
row_number() over (order by true) AS ID
, 'A' || (ID::varchar)
, 'B' || (ID::varchar)
, null
, null
from table(generator(rowcount => 10000))
;

rootタスク作成

// create root task
CREATE TASK ht_test.root_task
SCHEDULE = 'USING CRON 0 * * * * Asia/Tokyo'
warehouse = wh_survey_xs
AS
SELECT 1
;

子タスク1とプロシージャ作成

// create child task1
CREATE TASK ht_test.child_task1
warehouse = wh_survey_xs
AFTER
db.ht_test.root_task
AS
CALL db.ht_test.child_task1_proc()
;
CREATE OR REPLACE PROCEDURE ht_test.child_task1_proc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
START TRANSACTION;

UPDATE db.ht_test.icecream SET col2 = 'updated', updated_ts_task1 = current_timestamp() WHERE id < 5000;
UPDATE db.ht_test.icecream SET col2 = 'updated2', updated_ts_task1 = current_timestamp() WHERE id >= 5000 ;

COMMIT;
END;
$$
;

子タスク2とプロシージャ作成

// create child task2
CREATE TASK ht_test.child_task2
warehouse = wh_survey_xs
AFTER
db.ht_test.root_task
AS
CALL db.ht_test.child_task2_proc()
;

CREATE OR REPLACE PROCEDURE ht_test.child_task2_proc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
START TRANSACTION;

UPDATE db.ht_test.icecream SET col2 = 'updated3', updated_ts_task2 = current_timestamp() WHERE id >= 5000;
UPDATE db.ht_test.icecream SET col2 = 'updated4', updated_ts_task2 = current_timestamp() WHERE id < 5000;

COMMIT;
END;
$$
;

タスク起動

// child tasks resume
ALTER TASK ht_test.child_task1 RESUME;
ALTER TASK ht_test.child_task2 RESUME;

// execute root task
EXECUTE TASK db.ht_test.root_task;

通常テーブル用コード

上記と同じタスク（プロシージャ）を流用します。

テーブル作成

// create table
CREATE OR REPLACE TABLE n_icecream (
id NUMBER PRIMARY KEY AUTOINCREMENT START 1 INCREMENT 1,
col1 VARCHAR NOT NULL,
col2 VARCHAR NOT NULL,
updated_ts_task1 timestamp_ntz,
updated_ts_task2 timestamp_ntz
);

INSERT INTO n_icecream
select
row_number() over (order by true) AS ID
, 'A' || (ID::varchar)
, 'B' || (ID::varchar)
, null
, null
from table(generator(rowcount => 10000))
;

子タスク1用プロシージャ作成

// create procedures for tasks
CREATE OR REPLACE PROCEDURE ht_test.child_task1_proc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
START TRANSACTION;

UPDATE db.ht_test.n_icecream SET col2 = 'updated', updated_ts_task1 = current_timestamp() WHERE id < 5000;
UPDATE db.ht_test.n_icecream SET col2 = 'updated2', updated_ts_task1 = current_timestamp() WHERE id >= 5000 ;

COMMIT;
END;
$$
;

子タスク2用プロシージャ作成

CREATE OR REPLACE PROCEDURE ht_test.child_task2_proc()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
START TRANSACTION;

UPDATE db.ht_test.n_icecream SET col2 = 'updated3', updated_ts_task2 = current_timestamp() WHERE id >= 5000;
UPDATE db.ht_test.n_icecream SET col2 = 'updated4', updated_ts_task2 = current_timestamp() WHERE id < 5000;

COMMIT;
END;
$$
;

実行結果

まずは、Hybrid Table

データは以下のようになりました。updated_ts_task2がnullです。子タスク2がデータの更新を行っていないようです。

task historyを以下コードで確認

select '2' AS task, MAX(QUERY_START_TIME), MAX(COMPLETED_TIME)
from table(information_schema.task_history(
scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
result_limit => 10,
task_name=>'child_task2'))
union all
select '1' AS task, MAX(QUERY_START_TIME), MAX(COMPLETED_TIME)
from table(information_schema.task_history(
scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
result_limit => 10,
task_name=>'child_task1'));

子タスク2も完了時間が入力されており、終わっているようですが、失敗となってました。

以下エラー文。デッドロックがかかったようです。そして、子タスク2の実行がabortされたようです。

Uncaught exception of type 'STATEMENT_ERROR' on line 6 at position 8 : Statement from transaction 1717310139732000000, 1717310139725000000, 1717310139732000000 holds a lock that has not yet been released. Your statement '01b4bc0b-0001-7082-0000-e9b1002d6efe' was aborted because a deadlock was detected.

デッドロックは、2つのトランザクションがかけている行ロックの解除待ちが両トランザクションで発生することで発生します。今回は、デッドロックが発生し、エラーが発生したことで、子タスク2がabort、子タスク2のトランザクションによる行ロックが解除され、子タスク1が更新を行ったことが考えられます。

通常テーブル

子タスク2が後続で実行されて最終的に子タスク2によるupdateがかかりました。

task historyは以下のようになっており、子タスク2の実行が遅れています。

おわりに

• 上記の結果から、Hybrid Table特有の行レベルロックによりデッドロックを仕掛けることができました。
• 通常のテーブルはテーブルレベルのロックであるため、デッドロックはかかりませんでした。後続タスク（子タスク2）は、先行タスク（子タスク1）によるテーブルロックの解除を待ってから実行されたと考えられます。
• こまめにcommit（AUTO COMMIT）していれば、あまり気にする必要はないかもしれませんが、Hybrid Table使用時には、デッドロックに注意しましょう。