Delete + Insertをトランザクション（begin/end）で囲んで実行中に他セッションからの Insert はブロックされ（待たされ）る。
- Delete + Insertするセッション
% psql -a "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439" Password for user awsuser: \set PROMPT1 '%[%033[1;32m%]%n %`date +%H:%M` %R%#%[%033[0m%] ' \set PROMPT2 '%[%033[1;32m%]%R%#%[%033[0m%] ' \timing on Timing is on. \set HISTSIZE 1000000 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. awsuser 07:48 =# \x Expanded display is on. awsuser 07:48 =# select * from STV_DB_ISOLATION_LEVEL where db_name = 'sampledb'; -[ RECORD 1 ]---+--------------------------------------------------------------------------------------------------------------------------------- db_name | sampledb isolation_level | Snapshot Isolation Time: 222.836 ms awsuser 07:48 =# create table if not exists cities_ser(cityid integer not null, city varchar(10) not null, state char(10) not null); CREATE TABLE Time: 626.279 ms awsuser 07:49 =# insert into cities_ser values (1, 'Nagoya', 'Aichi'); INSERT 0 1 Time: 679.750 ms awsuser 07:49 =# insert into cities_ser values (2, 'Hakata', 'Fukuoka'); INSERT 0 1 Time: 984.175 ms awsuser 07:49 =# select * from cities_ser; -[ RECORD 1 ]------ cityid | 1 city | Nagoya state | Aichi -[ RECORD 2 ]------ cityid | 2 city | Hakata state | Fukuoka Time: 25.928 ms awsuser 07:49 =# begin; BEGIN Time: 13.240 ms awsuser 07:49 =# delete from cities_ser where cityid in (1 ,2); DELETE 2 Time: 48.685 ms awsuser 07:50 =# insert into cities_ser values (1, 'Nagoya', 'Aichi2'); INSERT 0 1 Time: 28.497 ms awsuser 07:51 =# insert into cities_ser values (2, 'Hakata', 'Fukuoka2'); INSERT 0 1 Time: 30.318 ms awsuser 07:51 =# end; COMMIT Time: 640.922 ms awsuser 07:51 =#
- 別セッションから Insert する
% psql -a "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com (http://redshift-cluster-poc-central.ceyg6jv96hfq.ap-northeast-1.redshift.amazonaws.com/) user=awsuser dbname=sampledb port=5439" Password for user awsuser: \set PROMPT1 '%[%033[1;32m%]%n %`date +%H:%M` %R%#%[%033[0m%] ' \set PROMPT2 '%[%033[1;32m%]%R%#%[%033[0m%] ' \timing on Timing is on. \set HISTSIZE 1000000 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. awsuser 07:50 =# select * from cities_ser; cityid | city | state --------+--------+------------ 1 | Nagoya | Aichi 2 | Hakata | Fukuoka (2 rows) Time: 31.174 ms awsuser 07:50 =# insert into cities_ser values (3, 'Wakayama', 'Wakayama'); INSERT 0 1 Time: 20389.333 ms (00:20.389) ★ ← end でトランザクションが終了するまで待たされる awsuser 07:51 =# select * from cities_ser; cityid | city | state --------+----------+------------ 1 | Nagoya | Aichi2 2 | Hakata | Fukuoka2 3 | Wakayama | Wakayama (3 rows) Time: 26.491 ms awsuser 07:51 =#
