Amazon Redshift で Commit/Rollback せずにセッションを閉じた場合に commit されているか rollback されているか。
psql などの SQL クライアントで autocommit が有効な場合は DML を発行すると自動的に commit を発行するので、commit が発行する前に閉じられると rollback される。autocommit が無効な場合は明示的に commit を発行する前にセッションが閉じられると rollback される。
- autocommit 無効で明示的に commit するまえにセッションが強制終了されると rollback される。
awsuser 20230222_10:10:00 =# \set AUTOCOMMIT off awsuser 20230222_10:10:15 =# \echo :AUTOCOMMIT off awsuser 20230222_10:10:22 =# select * from test1; id | name ----+------ (0 rows) Time: 98.254 ms awsuser 20230222_10:10:27 =# insert into test1(id, name) values(1, 'keisuke'); INSERT 0 1 Time: 34.517 ms awsuser 20230222_10:12:43 =# ^Z zsh: suspended psql -a azekyohe@f84d8981cae5 ~ % kill -9 %1 azekyohe@f84d8981cae5 ~ % [1] + killed psql -a azekyohe@f84d8981cae5 ~ % psql -a "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439" \set PROMPT1 '%[%033[1;32m%]%n %`date '+%Y%m%d_%H:%M:%S'` %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 20230222_10:12:57 =# select * from test1; id | name ----+------ (0 rows) Time: 41.511 ms
- autocommit 有効で BEGIN、DML 発行後に END を発行する前にセッションが強制終了されると rollback される。
awsuser 20230222_10:22:33 =# \echo :AUTOCOMMIT on awsuser 20230222_10:22:36 =# begin; BEGIN Time: 15.368 ms awsuser 20230222_10:22:42 =# insert into test1(id, name) values(1, 'keisuke'); INSERT 0 1 Time: 38.501 ms awsuser 20230222_10:22:57 =# ^Z zsh: suspended psql -a azekyohe@f84d8981cae5 ~ % kill -9 %1 azekyohe@f84d8981cae5 ~ % [1] + killed psql -a azekyohe@f84d8981cae5 ~ % psql -a "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439" \set PROMPT1 '%[%033[1;32m%]%n %`date '+%Y%m%d_%H:%M:%S'` %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 20230222_10:23:07 =# select * from test1; id | name ----+------ (0 rows) Time: 31.572 ms
参考
Amazon Redshift supports a default automatic commit behavior in which each separately run SQL command commits individually. If you enclose a set of commands in a transaction block (defined by BEGIN and END statements), the block commits as one transaction, so you can roll it back if necessary. Exceptions to this behavior are the TRUNCATE and VACUUM commands, which automatically commit all outstanding changes made in the current transaction.
Some SQL clients issue BEGIN and COMMIT commands automatically, so the client controls whether a group of statements are run as a transaction or each individual statement is run as its own transaction. Check the documentation for the interface you are using. For example, when using the Amazon Redshift JDBC driver, a JDBC PreparedStatement with a query string that contains multiple (semicolon separated) SQL commands runs all the statements as a single transaction. In contrast, if you use SQL Workbench/J and set AUTO COMMIT ON, then if you run multiple statements, each statement runs as its own transaction.
Serializable isolation - Amazon Redshift
