トランザクション中の文の失敗の扱いの違い
(読みづらいタイトルだな)
ことの発端はこのツイート。
MySQLは、以下を満たさないという理解でいいのか？
— imaharu (@imaharuTech) July 2, 2020
エラーが出た時にPostgreSQLのようにロールバックを行わないので
Atomicity（原子性）・・・トランザクションの実行結果は「全て成功」か「全て失敗」のいずれかでなければならない#mysql
さすがの MySQL でもそこを破ってくることはないだろうと思いつつ、トランザクション野郎としてはちゃんと確かめねばならないと思い、早朝にも関わらず布団から出てラップトップを開いた(午前10時)。
実験1
以下のような docker-compose.yml と sql/script.sql を用意し、実験をする。
version: '3.3'
services:
db:
image: mysql:8
environment:
MYSQL_DATABASE: test_db
MYSQL_USER: user
MYSQL_PASSWORD: password
MYSQL_ROOT_PASSWORD: rootpassword
volumes:
- "./sql:/tmp/sql"
create table tbl(id int primary key); insert into tbl values (1); begin; insert into tbl values (1); insert into tbl values (2); commit;
まずはおもむろに mysql のプロンプトを開き、sql/script.sql の内容を1行ずつ手で実行する。
(余計な warning などは消した)
# mysql -h 127.0.0.1 -u user -ppassword test_db mysql> create table tbl(id int primary key); Query OK, 0 rows affected (0.02 sec) mysql> insert into tbl values (1); Query OK, 1 row affected (0.01 sec) mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> insert into tbl values (1); ERROR 1062 (23000): Duplicate entry '1' for key 'tbl.PRIMARY' mysql> insert into tbl values (2); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from tbl; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)
なんと、insert into tbl values (1); が失敗したのにコミットできている。
一旦考察と仮説
トランザクション中の文が失敗しても、その失敗をクライアントに通知できているなら原子性違反にはならないのではないか。
つまり、クライアントに失敗を通知できない非対話実行(バッチ実行)では途中で実行が止まるはずである。
実験2(非対話実行)
上で作ったテーブルは適当に drop して、次の実験をする。
仮説では非対話実行では途中で実行が止まり、結果として id=1 の行しか残らないはずである。 (余計な warning などは消した)
# mysql -h 127.0.0.1 -u user -ppassword test_db < /tmp/sql/script.sql ERROR 1062 (23000) at line 4: Duplicate entry '1' for key 'tbl.PRIMARY' # mysql -h 127.0.0.1 -u user -ppassword test_db mysql> select * from tbl; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
仮説どおり、4行目のエラーで実行が止まり、1行だけのテーブルになった。
実験3(PostgreSQL)
さて、ここで気になるのは PostgreSQL の挙動である。
私は仕事でも趣味でも PostgreSQL やその派生を使うことが多い。
PostgreSQL は対話モードでも途中でコケたらコミット不能になるはずであるが、いい機会なのでちゃんと確かめておこう。
MySQL 同様、docker-compose を用いて適当に用意する。
version: '3.3'
services:
db:
image: postgres:12
environment:
POSTGRES_PASSWORD: password
volumes:
- "./sql:/tmp/sql"
そしてまずは対話実行で確認。
# psql -U postgres postgres=# create table tbl(id int primary key); CREATE TABLE postgres=# insert into tbl values (1); INSERT 0 1 postgres=# begin; BEGIN postgres=# insert into tbl values (1); ERROR: duplicate key value violates unique constraint "tbl_pkey" DETAIL: Key (id)=(1) already exists. postgres=# insert into tbl values (2); ERROR: current transaction is aborted, commands ignored until end of transaction block postgres=# commit; ROLLBACK postgres=# select * from tbl; id ---- 1 (1 row)
うむ、やはり途中でコケると後続の文は無視される。実家のような安心感。
ついでに非対話実行もためそう。
# psql -U postgres < /tmp/sql/script.sql CREATE TABLE INSERT 0 1 BEGIN ERROR: duplicate key value violates unique constraint "tbl_pkey" DETAIL: Key (id)=(1) already exists. ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK # psql -U postgres postgres=# select * from tbl; id ---- 1 (1 row)
こちらも同様。納得。
まとめ
MySQL は対話実行だとトランザクション中の文がコケても勝手にトランザクションがアボートしたりせず、トランザクションを続行することができる。
MySQL でも非対話実行なら途中の文がコケるとバッチ全体の実行が停止する。
PostgreSQL を使っている身からすると MySQL のこの挙動は結構新鮮だった。
長いトランザクションの途中でも衝突する可能性のある INSERT を投機的に(?)実行して衝突の有無で分岐したりできるので便利かもしれない。
なお、PostgreSQL でそのようなことをしたい場合は SAVEPOINT を使って予防線を張っておくとよい。長大なトランザクションを失敗一撃で無に帰さずに済む。
追証用資料
適当な英語で書かれた README を同梱しているのでそのとおりにやれば試せます(たぶん)。
