Write and Run

it's a simple way, but the only way.

トランザクション中の文の失敗の扱いの違い

(読みづらいタイトルだな)

ことの発端はこのツイート。

さすがの 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 を同梱しているのでそのとおりにやれば試せます(たぶん)。

github.com