ablog

不器用で落着きのない技術者のメモ

Redshift で ISOLATION LEVEL を SERIALIZABLE から SNAPSHOT に変更する

AWS

書式

  • ISOLATION LEVEL を SNAPSHOT に変更する
ALTER DATABASE database_name ISOLATION LEVEL SNAPSHOT
  • 現在の ISOLATION LEVEL を確認する
    • concurrency_model が 1 なら SNAPSHOT、2 なら SERIALIZABLE
SELECT datname, datconfig FROM pg_database_info;

手順

  • Redshift クラスターにスーパーユーザーで接続する。
$ psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439"
  • データベースを作成する（dev データベースは ISOLATION LEVEL を変更できない）。
dev# create database sampledb;
  • 現在の ISOLATION LEVEL は 2 なので SERIALIZABLE。
dev=# SELECT datname, datconfig FROM pg_database_info;
   datname    |                  datconfig                   
--------------+----------------------------------------------
 dev          | {enable_query_profiler_instrumentation=true}
 padb_harvest | 
 template1    | 
 ads_db       | 
 template0    | 
 sampledb     | {concurrency_model=2} ★
(6 rows)
  • ISOLATION LEVEL を SNAPSHOT に変更する。
dev=# alter database sampledb isolation level snapshot;
ALTER DATABASE
Time: 547.586 ms
  • 現在の ISOLATION LEVEL は 1 なので SNAPSHOT に変わっている。
dev=# SELECT datname, datconfig FROM pg_database_info;
   datname    |                  datconfig                   
--------------+----------------------------------------------
 dev          | {enable_query_profiler_instrumentation=true}
 padb_harvest | 
 template1    | 
 ads_db       | 
 template0    | 
 sampledb     | {concurrency_model=1} ★
(6 rows)

補足

SERIALIZABLE から SNAPSHOT に変更後に SERIALIZABLE に戻すことは可能だし、ISOLATION LEVEL はトランザクション管理の設定であり、データには関係ないという当たり前のことを念の為検証してみた。

データサイズと変更時間は関係ない（データサイズが大きいと時間がかかるわけではない）
 % psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439" 

sampledb=# select database, sum(size)/1024 as "size(gb)" from SVV_TABLE_INFO group by database;
 database | size(gb) 
----------+----------
 sampledb |      441 ★ 441GB（サイズは大きくないが、空っぽではない）
(1 row)

% psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439"      

psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

dev=# \timing
Timing is on.
dev=# alter database sampledb isolation level snapshot;
ALTER DATABASE
Time: 710.297 ms ★1秒未満
dev=# alter database sampledb isolation level serializable;
ALTER DATABASE
Time: 640.207 ms ★1秒未満
スナップショットからリストアするとスナップショット取得時の ISOLATION LEVEL になる
  • Redshift は 1 クラスターで複数データベースを持つことができる、ISOLATION LEVEL はクラスターレベルではなくデータベース単位の設定であるため、スナップショットをリストアするとスナップショット取得時の ISOLATION LEVEL になる。と思われる。
% psql "host=redshift-cluster-poc-central-restore-20220904.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439"  

Password for user awsuser: 
psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

sampledb=# SELECT datname, datconfig FROM pg_database_info;
   datname    |                  datconfig                   
--------------+----------------------------------------------
 dev          | {enable_query_profiler_instrumentation=true}
 padb_harvest | 
 template1    | 
 ads_db       | 
 template0    | 
 sampledb     | {concurrency_model=1} ★
(6 rows)
ISOLATION LEVEL を変更してもデータは変わらない
  • SERIALIZABLE から SNAPSHOT に変更前にテーブルの件数、データ、統計情報を確認する
% psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439" 

sampledb=# select database, schema, "table", size, tbl_rows from svv_table_info order by "table";
 database | schema |   table    | size  |  tbl_rows  
----------+--------+------------+-------+------------
 sampledb | public | customer   |   216 |    3000000
 sampledb | public | lineorer10 | 50152 | 1200075804
 sampledb | public | lineorer2  | 50152 | 1200075804
 sampledb | public | lineorer3  | 50152 | 1200075804
 sampledb | public | lineorer4  | 50152 | 1200075804
 sampledb | public | lineorer5  | 50152 | 1200075804
 sampledb | public | lineorer6  | 50152 | 1200075804
 sampledb | public | lineorer7  | 50152 | 1200075804
 sampledb | public | lineorer8  | 50152 | 1200075804
 sampledb | public | lineorer9  | 50152 | 1200075804
(10 rows)

sampledb=# select count(*) from lineorer10;
   count    
------------
 1200075804
(1 row)

sampledb=# select *  from lineorer10 limit 3;
 lo_orderkey | lo_linenumber | lo_custkey | lo_partkey | lo_suppkey | lo_orderdate | lo_orderpriority | lo_shippriority | lo_quantity | lo_extendedprice | lo_ordertotalprice | lo_discount | lo_revenue | lo_supplycost | lo_tax | lo_commitdate | lo_shipmode 
-------------+---------------+------------+------------+------------+--------------+------------------+-----------------+-------------+------------------+--------------------+-------------+------------+---------------+--------+---------------+-------------
   418072356 |             1 |    1639619 |     189212 |     619162 |     19941013 | 4-NOT SPECI      | 0               |           0 |                0 |                  0 |          10 |          0 |         78072 |      5 |      19941118 | MAIL
   418072418 |             7 |    2665321 |     500707 |     408194 |     19940620 | 5-LOW            | 0               |          10 |          1707680 |           27679083 |           0 |    1707680 |        102460 |      8 |      19940904 | SHIP
   418072483 |             2 |    1069580 |     184362 |     843926 |     19940123 | 3-MEDIUM         | 0               |          16 |          2314176 |            6005933 |           1 |    2291034 |         86781 |      8 |      19940301 | SHIP
(3 rows)
  • SERIALIZABLE から SNAPSHOT に変更する。
% psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439" 
psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

dev=# SELECT datname, datconfig FROM pg_database_info;
   datname    |                  datconfig                   
--------------+----------------------------------------------
 dev          | {enable_query_profiler_instrumentation=true}
 padb_harvest | 
 template1    | 
 ads_db       | 
 template0    | 
 sampledb     | {concurrency_model=2}
(6 rows)

dev=#  \timing
Timing is on.
dev=# alter database sampledb isolation level snapshot;
ALTER DATABASE
Time: 702.584 ms
dev=# SELECT datname, datconfig FROM pg_database_info;
   datname    |                  datconfig                   
--------------+----------------------------------------------
 dev          | {enable_query_profiler_instrumentation=true}
 padb_harvest | 
 template1    | 
 ads_db       | 
 template0    | 
 sampledb     | {concurrency_model=1}
(6 rows)

Time: 20.695 ms
  • SERIALIZABLE から SNAPSHOT に変更後に、データが変わっていないか確認する。
 % psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439" 
Password for user awsuser: 
psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

sampledb=# select database, schema, "table", size, tbl_rows from svv_table_info order by "table";
 database | schema |   table    | size  |  tbl_rows  
----------+--------+------------+-------+------------
 sampledb | public | customer   |   216 |    3000000
 sampledb | public | lineorer10 | 50152 | 1200075804
 sampledb | public | lineorer2  | 50152 | 1200075804
 sampledb | public | lineorer3  | 50152 | 1200075804
 sampledb | public | lineorer4  | 50152 | 1200075804
 sampledb | public | lineorer5  | 50152 | 1200075804
 sampledb | public | lineorer6  | 50152 | 1200075804
 sampledb | public | lineorer7  | 50152 | 1200075804
 sampledb | public | lineorer8  | 50152 | 1200075804
 sampledb | public | lineorer9  | 50152 | 1200075804
(10 rows)

sampledb=# select count(*) from lineorer10;
   count    
------------
 1200075804
(1 row)

sampledb=# select *  from lineorer10 limit 3;
 lo_orderkey | lo_linenumber | lo_custkey | lo_partkey | lo_suppkey | lo_orderdate | lo_orderpriority | lo_shippriority | lo_quantity | lo_extendedprice | lo_ordertotalprice | lo_discount | lo_revenue | lo_supplycost | lo_tax | lo_commitdate | lo_shipmode 
-------------+---------------+------------+------------+------------+--------------+------------------+-----------------+-------------+------------------+--------------------+-------------+------------+---------------+--------+---------------+-------------
   418072356 |             1 |    1639619 |     189212 |     619162 |     19941013 | 4-NOT SPECI      | 0               |           0 |                0 |                  0 |          10 |          0 |         78072 |      5 |      19941118 | MAIL
   418072418 |             7 |    2665321 |     500707 |     408194 |     19940620 | 5-LOW            | 0               |          10 |          1707680 |           27679083 |           0 |    1707680 |        102460 |      8 |      19940904 | SHIP
   418072483 |             2 |    1069580 |     184362 |     843926 |     19940123 | 3-MEDIUM         | 0               |          16 |          2314176 |            6005933 |           1 |    2291034 |         86781 |      8 |      19940301 | SHIP
(3 rows)
  • SERIALIZABLE から SNAPSHOT に戻す。
% psql "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439"
psql (14.3, server 8.0.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

dev=# \timing
Timing is on.
dev=# alter database sampledb isolation level serializable;
ALTER DATABASE
Time: 645.485 ms

dev=# SELECT datname, datconfig FROM pg_database_info;
   datname    |                  datconfig                   
--------------+----------------------------------------------
 dev          | {enable_query_profiler_instrumentation=true}
 padb_harvest | 
 template1    | 
 ads_db       | 
 template0    | 
 sampledb     | {concurrency_model=2}
(6 rows)
  • SNAPSHOT から SERIALIZABLE に戻した後に、データが変わっていないか確認する。
sampledb=# select database, schema, "table", size, tbl_rows from svv_table_info order by "table";
 database | schema |   table    | size  |  tbl_rows  
----------+--------+------------+-------+------------
 sampledb | public | customer   |   216 |    3000000
 sampledb | public | lineorer10 | 50152 | 1200075804
 sampledb | public | lineorer2  | 50152 | 1200075804
 sampledb | public | lineorer3  | 50152 | 1200075804
 sampledb | public | lineorer4  | 50152 | 1200075804
 sampledb | public | lineorer5  | 50152 | 1200075804
 sampledb | public | lineorer6  | 50152 | 1200075804
 sampledb | public | lineorer7  | 50152 | 1200075804
 sampledb | public | lineorer8  | 50152 | 1200075804
 sampledb | public | lineorer9  | 50152 | 1200075804
(10 rows)

sampledb=# select count(*) from lineorer10;
   count    
------------
 1200075804
(1 row)

sampledb=# select *  from lineorer10 limit 3;
 lo_orderkey | lo_linenumber | lo_custkey | lo_partkey | lo_suppkey | lo_orderdate | lo_orderpriority | lo_shippriority | lo_quantity | lo_extendedprice | lo_ordertotalprice | lo_discount | lo_revenue | lo_supplycost | lo_tax | lo_commitdate | lo_shipmode 
-------------+---------------+------------+------------+------------+--------------+------------------+-----------------+-------------+------------------+--------------------+-------------+------------+---------------+--------+---------------+-------------
   418072356 |             1 |    1639619 |     189212 |     619162 |     19941013 | 4-NOT SPECI      | 0               |           0 |                0 |                  0 |          10 |          0 |         78072 |      5 |      19941118 | MAIL
   418072418 |             7 |    2665321 |     500707 |     408194 |     19940620 | 5-LOW            | 0               |          10 |          1707680 |           27679083 |           0 |    1707680 |        102460 |      8 |      19940904 | SHIP
   418072483 |             2 |    1069580 |     184362 |     843926 |     19940123 | 3-MEDIUM         | 0               |          16 |          2314176 |            6005933 |           1 |    2291034 |         86781 |      8 |      19940301 | SHIP
(3 rows)

検証環境

  • 構成
 % aws redshift describe-clusters --cluster-identifier redshift-cluster-poc-central

{
    "Clusters": [
        {
            "ClusterIdentifier": "redshift-cluster-poc-central",
            "NodeType": "ra3.4xlarge",
            "ClusterStatus": "available",
            "ClusterAvailabilityStatus": "Available",
            "MasterUsername": "awsuser",
            "DBName": "dev",
            "Endpoint": {
                "Address": "redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com",
                "Port": 5439
            },
...
            "AvailabilityZone": "ap-northeast-1a",
...
            "NumberOfNodes": 2,
...
  • バージョン
dev=# select * from version();
                                                          version                                                          
---------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.40677

参考

Amazon Redshift は、同時トランザクションのスナップショット分離のサポートを開始しました。Amazon Redshift は、SQL 標準に従って、ダーティリード、ノンリピータブルリード、およびファントムリードを防止します。Amazon Redshift は、トランザクションをシリアル化するために SERIALIZABLE と SNAPSHOT ISOLATION の 2 つのオプションを提供します。SERIALIZABLE オプションは、厳密なシリアル化を実装します。このオプションでは、結果を同時に実行されているトランザクションのシリアル順序にマッピングできないと、トランザクションが失敗する可能性があります。SNAPSHOT ISOLATION オプションで同時実行性を高めることができ、同じテーブル内で異なる行への同時変更が正常に行われます。どちらのオプションでも、トランザクションはデータベースの最新のコミット済みのバージョン、つまりスナップショットで引き続き動作します。

Amazon Redshift でプロビジョニングされたクラスターが、デフォルトオプションとして SERIALIZABLE を使用します。Amazon Redshift サーバーレスデータウェアハウスが、デフォルトのオプションとして SNAPSHOT ISOLATION を使用します。これは、Amazon Redshift CREATE または ALTER DATABASE ISOLATION LEVEL SQL コマンドを使用して変更できます。SNAPSHOT ISOLATION は、Amazon Redshift を利用可能なすべての商用 AWSリージョンで利用できます。Redshift 分離レベルの使用方法の詳細については、Amazon Redshift のドキュメントを参照してください。

Amazon Redshift で、同時トランザクションのための新しいスナップショット分離レベルのサポートを開始 
ALTER DATABASE database_name
[ RENAME TO new_name 
| OWNER TO new_owner
| CONNECTION LIMIT { limit | UNLIMITED } 
| COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ]
[ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } ]
ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT }

A clause that specifies the isolation level used when queries run against a database.

  • SERIALIZABLE isolation – provides full serializability for concurrent transactions. For more information, see Serializable isolation.
  • SNAPSHOT isolation – provides an isolation level with protection against update and delete conflicts.

For more information about isolation levels, see CREATE DATABASE.
Consider the following items when altering the isolation level of a database:

  • You must have the superuser or CREATE DATABASE privilege to the current database to change the database isolation level.
  • You can't alter the isolation level of the dev database.
  • You can't alter the isolation level within a transaction block.
  • The alter isolation level command fails if other users are connected to the database.
  • The alter isolation level command can alter the isolation level settings of the current session.
ALTER DATABASE - Amazon Redshift

STV_DB_ISOLATION_LEVEL

STV_DB_ISOLATION_LEVEL displays the current isolation level for databases. For more information about isolation levels, see CREATE DATABASE.
STV_DB_ISOLATION_LEVEL is visible only to superusers. For more information, see Visibility of data in system tables and views.

Table columns
Column name Data type Description
db_name character(128) The database name.
isolation_level character(20) The isolation level of the database. Possible values include Serializable and Snapshot Isolation.
STV_DB_ISOLATION_LEVEL - Amazon Redshift

データベースが実行されている同時実行モデルを、次のように表示できます。

  • PG_DATABASE_INFO ビューに対してクエリを実行します。
SELECT datname, datconfig FROM pg_database_info;

データベースごとの分離レベルは、concurrency_model キーの隣に表示されます。1 の値は、SNAPSHOT を表します。2 の値は、SERIALIZABLE を表します。

CREATE DATABASE - Amazon Redshift

直列化可能分離エラーを修正する方法

エラー: 1023 詳細: Redshift テーブルで直列化可能分離に関する違反が発生しました
Amazon Redshift で直列化可能な分離エラーが検出されると、次のようなエラーメッセージが表示されます。

ERROR:1023 DETAIL: Serializable isolation violation on table in Redshift
直列化可能分離 - Amazon Redshift