Redshift Data APIにおけるトランザクションの挙動について確認してみた

八木優成

2023.03.17

こんにちは、データアナリティクス事業本部の八木です。
皆さん、Redshift Data API使ってますか？

Redshiftにクエリを実行する際、通常はVPC内のRedshiftエンドポイントに対してコネクションを確立し、クエリを実行しますが、Redshift Data APIはRedshiftのサービスエンドポイントを経由してクエリを実行します。
この機能により、プライベートサブネットに存在するRedshiftにVPC外からもクエリを実行できるなど、非常に便利な使い方ができます。

今回はこのRedshift Data APIでトランザクションを行った際の挙動について、確認してみました。

先に結論

BatchExecuteStatements APIでは BEGIN, COMMIT句なしでデフォルトでトランザクションが張られる
BatchExecuteStatements APIの挙動はAUTOCOMMITがオフの状態と同じ挙動になる

検証

前提環境

Redshift dc2.large x 1ノード
AWS CLI v2.11.3

事前準備

事前準備としてテーブルを作成しておきます。

CREATE TABLE users (
  id INT,
  name VARCHAR(50),
  age INT
);

BatchExecuteStatements APIで `BEGIN`, `COMMIT`句がない場合の挙動

こちらはドキュメントにも記載がある通り、単一のトランザクション内で実行されます。

BatchExecuteStatement API オペレーションの Sqls パラメータで SQL ステートメントが単一のトランザクションとして実行されます。
Amazon Redshift Data API の使用 - Amazon Redshift

以下のクエリをBatchExecuteStatements APIで実行してみます。

INSERT INTO users VALUES (1, '佐藤', 25);
INSERT INTO users VALUES (2, '鈴木', 30);
INSERT INTO users VALUES (3, '高橋', 45);
INSERT INTO users VALUES (4, '田中', 27);
INSERT INTO users VALUES (5, '渡辺', 38);

aws redshift-data batch-execute-statement \
  --cluster-identifier my-redshift \
  --database dev \
  --db-user admin \
  --sqls "[
    \"INSERT INTO users VALUES (1, '佐藤', 25);\",
    \"INSERT INTO users VALUES (2, '鈴木', 30);\",
    \"INSERT INTO users VALUES (3, '高橋', 45);\",
    \"INSERT INTO users VALUES (4, '田中', 27);\",
    \"INSERT INTO users VALUES (5, '渡辺', 38);\"
  ]"

クエリ履歴からトランザクションID（xid）を見てみると、単一トランザクション内で実行されていることが確認できました。

dev=# select userid, query, xid, starttime, trim(querytxt)  from stl_query where userid = 100 order by starttime desc limit 10;
 userid |  query  |  xid   |         starttime          |                                                          btrim
--------+---------+--------+----------------------------+--------------------------------------------------------------------------------------------------------------------------
    100 | 5000575 | 108362 | 2023-03-17 06:17:22.028129 | INSERT INTO users VALUES (5, '渡辺', 38);
    100 | 5000573 | 108362 | 2023-03-17 06:17:21.96298  | INSERT INTO users VALUES (4, '田中', 27);
    100 | 5000571 | 108362 | 2023-03-17 06:17:21.898675 | INSERT INTO users VALUES (3, '高橋', 45);
    100 | 5000569 | 108362 | 2023-03-17 06:17:21.834096 | INSERT INTO users VALUES (2, '鈴木', 30);
    100 | 5000567 | 108362 | 2023-03-17 06:17:21.768128 | INSERT INTO users VALUES (1, '佐藤', 25);
~~（略）~~

BatchExecuteStatements APIでは `BEGIN`, `COMMIT`句がある場合はどうなるのか

BEGIN, COMMIT句がない場合はドキュメントの通りでした。

ではクエリの中に明示的にBEGIN, COMMIT句を入れるとどうなるのでしょうか？BEGIN,COMMITを無視して単一トランザクションになるのでしょうか？それとも別のトランザクションが張られるのでしょうか？

試してみます！

以下のクエリをBatchExecuteStatements APIで実行してみます。

INSERT INTO users VALUES (6, '山田', 33);
INSERT INTO users VALUES (7, '中村', 50);
BEGIN;
INSERT INTO users VALUES (8, '小林', 29);
INSERT INTO users VALUES (9, '加藤', 42);
COMMIT;
INSERT INTO users VALUES (10, '吉田', 36);
INSERT INTO users VALUES (11, '八木', 24);

aws redshift-data batch-execute-statement \
  --cluster-identifier my-redshift \
  --database dev \
  --db-user admin \
  --sqls "[
    \"INSERT INTO users VALUES (6, '山田', 33);\",
    \"INSERT INTO users VALUES (7, '中村', 50);\",
    \"BEGIN;\",
    \"INSERT INTO users VALUES (8, '小林', 29);\",
    \"INSERT INTO users VALUES (9, '加藤', 42);\",
    \"COMMIT;\",
    \"INSERT INTO users VALUES (10, '吉田', 36);\",
    \"INSERT INTO users VALUES (11, '八木', 24);\"
  ]"

クエリ履歴を確認すると、以下のようなっていました。

dev=# select userid, query, xid, starttime, trim(querytxt)  from stl_query where userid = 100 order by starttime desc limit 10;
 userid |  query  |  xid   |         starttime          |                                                          btrim
--------+---------+--------+----------------------------+--------------------------------------------------------------------------------------------------------------------------
    100 | 5000786 | 109061 | 2023-03-17 06:31:20.032541 | INSERT INTO users VALUES (11, '八木', 24);
    100 | 5000784 | 109061 | 2023-03-17 06:31:19.943083 | INSERT INTO users VALUES (10, '吉田', 36);
    100 | 5000782 | 109060 | 2023-03-17 06:31:19.773401 | INSERT INTO users VALUES (9, '加藤', 42);
    100 | 5000780 | 109060 | 2023-03-17 06:31:19.711847 | INSERT INTO users VALUES (8, '小林', 29);
    100 | 5000778 | 109060 | 2023-03-17 06:31:19.563472 | INSERT INTO users VALUES (7, '中村', 50);
    100 | 5000776 | 109060 | 2023-03-17 06:31:19.502955 | INSERT INTO users VALUES (6, '山田', 33);

COMMIT句の部分で一度コミットされ、その後別トランザクションが始まっています。
そう、AUTOCOMMITがオフの状態と同じ挙動になっています。

試しにpsqlコマンドでAUTOCOMMITをオフにして、同じクエリを実行してみましょう。

dev=# \set AUTOCOMMIT off
dev=# INSERT INTO users VALUES (6, '山田', 33);
INSERT 0 1
dev=*# INSERT INTO users VALUES (7, '中村', 50);
INSERT 0 1
dev=*# BEGIN;
BEGIN
dev=*# INSERT INTO users VALUES (8, '小林', 29);
INSERT 0 1
dev=*# INSERT INTO users VALUES (9, '加藤', 42);
INSERT 0 1
dev=*# COMMIT;
COMMIT
dev=# INSERT INTO users VALUES (10, '吉田', 36);
INSERT 0 1
dev=*# INSERT INTO users VALUES (11, '八木', 24);
INSERT 0 1
dev=*# COMMIT;
COMMIT
dev=# select userid, query, xid, starttime, trim(querytxt)  from stl_query where userid = 100 order by starttime desc limit 10;
 userid |  query  |  xid   |         starttime          |                                                          btrim
--------+---------+--------+----------------------------+--------------------------------------------------------------------------------------------------------------------------
    100 | 5000962 | 109668 | 2023-03-17 06:43:28.068416 | INSERT INTO users VALUES (11, '八木', 24);
    100 | 5000958 | 109668 | 2023-03-17 06:43:22.663047 | INSERT INTO users VALUES (10, '吉田', 36);
    100 | 5000956 | 109650 | 2023-03-17 06:43:12.814659 | INSERT INTO users VALUES (9, '加藤', 42);
    100 | 5000954 | 109650 | 2023-03-17 06:43:07.581172 | INSERT INTO users VALUES (8, '小林', 29);
    100 | 5000949 | 109650 | 2023-03-17 06:42:53.03408  | INSERT INTO users VALUES (7, '中村', 50);
    100 | 5000947 | 109650 | 2023-03-17 06:42:45.553056 | INSERT INTO users VALUES (6, '山田', 33);

同じ結果となりました。Data APIからの実行と同様にCOMMITの部分で一度トランザクションが終了し、新たなトランザクションが開始されています。

最初のCOMMITのあとにエラーが発生するクエリを実行しても、COMMIT部分までは反映されていることが確認できます。

INSERT INTO users VALUES (6, '山田', 33);
INSERT INTO users VALUES (7, '中村', 50);
BEGIN;
INSERT INTO users VALUES (8, '小林', 29);
INSERT INTO users VALUES (9, '加藤', 42);
COMMIT;
INSERT INTO users VALUES (10, '吉田', 36);
INSERT INTO users VALUES ('This is invalid query');

aws redshift-data batch-execute-statement \
  --cluster-identifier my-redshift \
  --database dev \
  --db-user admin \
  --sqls "[
    \"INSERT INTO users VALUES (6, '山田', 33);\",
    \"INSERT INTO users VALUES (7, '中村', 50);\",
    \"BEGIN;\",
    \"INSERT INTO users VALUES (8, '小林', 29);\",
    \"INSERT INTO users VALUES (9, '加藤', 42);\",
    \"COMMIT;\",
    \"INSERT INTO users VALUES (10, '吉田', 36);\",
    \"INSERT INTO users VALUES ('This is invalid query');\"
  ]"

結果

dev=# select userid, query, xid, starttime, trim(querytxt)  from stl_query where userid = 100 order by starttime desc limit 10;
 userid |  query  |  xid   |         starttime          |                                                          btrim
--------+---------+--------+----------------------------+--------------------------------------------------------------------------------------------------------------------------
    100 | 5001062 | 110040 | 2023-03-17 06:50:50.846407 | Undoing 1 transactions on table 308887 with current xid 110040 : 110040
    100 | 5001061 | 110040 | 2023-03-17 06:50:50.750476 | INSERT INTO users VALUES (10, '吉田', 36);
    100 | 5001059 | 110039 | 2023-03-17 06:50:50.537952 | INSERT INTO users VALUES (9, '加藤', 42);
    100 | 5001057 | 110039 | 2023-03-17 06:50:50.439077 | INSERT INTO users VALUES (8, '小林', 29);
    100 | 5001055 | 110039 | 2023-03-17 06:50:50.21342  | INSERT INTO users VALUES (7, '中村', 50);
    100 | 5001053 | 110039 | 2023-03-17 06:50:50.11737  | INSERT INTO users VALUES (6, '山田', 33);
~~（略）~~
dev=# select * from users;
 id | name | age
----+------+-----
  6 | 山田 |  33
  7 | 中村 |  50
  8 | 小林 |  29
  9 | 加藤 |  42
(4 rows)

さいごに

今回はRedshift Data APIでトランザクションを実行した際の挙動について、確認してみました。
１回のAPIリクエストで複数トランザクションを使う機会は少ないかと思いますが、誰かの役に立てば幸いです。

以上、八木でした！

Redshift Data APIにおけるトランザクションの挙動について確認してみた

先に結論

検証

前提環境

事前準備

BatchExecuteStatements APIで `BEGIN`, `COMMIT`句がない場合の挙動

BatchExecuteStatements APIでは `BEGIN`, `COMMIT`句がある場合はどうなるのか

さいごに

イベント

EVENT【4/6（木）】サーバーレス環境をより高速化するMomentoのご紹介〜キャッシュの最適化、拡張、管理をSaaSで自動化する〜

EVENT【4/26（水）リモート】クラスメソッドの会社説明会〜フリーランスエンジニア編〜を開催します

EVENT【4/20（木）リモート】クラスメソッドの24卒向け会社説明会〜最新技術で爆速成長！新卒から活躍する秘訣〜

EVENT【4/12（水）】Alteryx Designer Cloudはじめの一歩

EVENT【4/19（水）リモート】第二新卒・若手エンジニア向け会社説明会～クラスメソッドで踏み出すクラウドエンジニアのキャリア～

EVENT【4/14（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0』導入実践ウェビナー

Redshift Data APIにおけるトランザクションの挙動について確認してみた

先に結論

検証

前提環境

事前準備

BatchExecuteStatements APIで BEGIN, COMMIT句がない場合の挙動

BatchExecuteStatements APIでは BEGIN, COMMIT句がある場合はどうなるのか

さいごに

イベント

EVENT【4/6（木）】サーバーレス環境をより高速化するMomentoのご紹介〜キャッシュの最適化、拡張、管理をSaaSで自動化する〜

EVENT【4/26（水）リモート】クラスメソッドの会社説明会〜フリーランスエンジニア編〜を開催します

EVENT【4/20（木）リモート】クラスメソッドの24卒向け会社説明会 〜最新技術で爆速成長！新卒から活躍する秘訣〜

EVENT【4/12（水）】Alteryx Designer Cloudはじめの一歩

EVENT【4/19（水）リモート】第二新卒・若手エンジニア向け会社説明会 ～クラスメソッドで踏み出すクラウドエンジニアのキャリア～

EVENT【4/14（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0』導入実践ウェビナー

関連記事

CodeDeployにおいて、特定のEC2へのデプロイが失敗した時の対処方法

Amazon Athena for Apache Sparkをコンソールから使ってみよう！

【アップデート】Security Hub 修復ソリューションが v2.0.0 に更新！統合コントロールに対応しました

Amazon Athena for Apache SparkでS3バケット上のデータを可視化してみた

BatchExecuteStatements APIで `BEGIN`, `COMMIT`句がない場合の挙動

BatchExecuteStatements APIでは `BEGIN`, `COMMIT`句がある場合はどうなるのか

EVENT【4/20（木）リモート】クラスメソッドの24卒向け会社説明会〜最新技術で爆速成長！新卒から活躍する秘訣〜

EVENT【4/19（水）リモート】第二新卒・若手エンジニア向け会社説明会～クラスメソッドで踏み出すクラウドエンジニアのキャリア～