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

2023.03.17

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、データアナリティクス事業本部の八木です。
皆さん、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でトランザクションを実行した際の挙動について、確認してみました。
1回のAPIリクエストで複数トランザクションを使う機会は少ないかと思いますが、誰かの役に立てば幸いです。

以上、八木でした!