Zenn
😇

BEGIN 中に BEGIN をすると COMMIT される

2023/11/30に公開
MySQL
Advent calendar
tech

この記事は MySQL Advent Calendar 2023 2日目の記事です。 (ちょっとフライング。。)

今回は僕がマジか、と思ってしまった MySQL の挙動について共有させていただきます。

BEGIN 中に BEGIN をすると COMMIT される

結論から言うとこれだけです ^^;;

アプリエンジニアの方からお問い合わせをいただいた時にはこのことを意識したことすらなかったのでトランザクションの終了は COMMIT or ROLLBACK にてされるのだという先入観で動いていました。

ざっくりと言うとこんな感じ

  • BEGIN;
    • INSERT INTO testtable VALUES (1);
      • BEGIN;

このタイミングで別のターミナルを立ち上げて中身を確認するとなんと testtable には 1 という値が入っています。

個人的にはまじかーー、な挙動だったのでもしかしたら過去に僕もこれがらみのプログラムバグを仕込んだんじゃないかと内心ヒヤヒヤしております。。

やってみる

ちなみに今回使ったのは MySQL 8.0.32 です。

mysql > SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

mysql > SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql > DESC testtable;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.04 sec)

実際にやってみましょう、まずは BEGIN をしてから INSERT をしてみます。

  • SESSION A
sessionA > SELECT * FROM testtable;
Empty set (0.01 sec)

sessionA > BEGIN;
Query OK, 0 rows affected (0.00 sec)

sessionA > INSERT INTO testtable VALUES (1);
Query OK, 1 row affected (0.00 sec)

このタイミングでは当然ですが COMMIT されていないデータは見えません。

  • SESSION B
sessionB > SELECT * FROM testtable;
Empty set (0.00 sec)

続いて SESSION A に戻ってもう一度 BEGIN を打ちます。

  • SESSION A
sessionA > BEGIN;
Query OK, 0 rows affected (0.00 sec)

このタイミングで　SESSION B から testtable を SELECT してみると。。。

  • SESSION B
sessionB > SELECT * FROM testtable;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

なんとデータが見れる状態に・・・
ちなみに SESSION A を ROLLBACK してみると

  • SESSION A
sessionA > ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
  • SESSION B
sessionB > SELECT * FROM testtable;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

はい、見れました！！
いや。。個人的には見れないで欲しかった、もうちょっと言うと直感的に BEGIN の中の BEGIN は無視してくれたら嬉しいなとか思ったりもしたりしたり。。

とはいえこの仕様を知っているか知らないか、でその挙動を受け入れてアプリケーションに反映できる稼働化が決まるので覚えておくべき一つの仕様ですよねｗ

僕が昔書いたコードの中でループ中に BEGIN を書いちゃってたりしないか。。。不安になりました。。

information_schema を見てみる

トランザクションの状態は information_schema.INNODB_TRX を見れば良さげ？ということで見てみました。

まずは sessionA で BEGIN

  • SESSION A
sessionA > BEGIN;
Query OK, 0 rows affected (0.00 sec)

別のセッションから information_schema.INNODB_TRX をのぞいてみます。

  • SESSION C
sessionC > SELECT * FROM information_schema.INNODB_TRX\G
Empty set (0.00 sec)

START TRANSACTION のタイミングではまだレコードがないんですよね。と言うことで BEGIN している SESSION A からレコードを入れてみます。

  • SESSION A
sessionA > INSERT INTO testtable VALUES (1);
Query OK, 1 row affected (0.00 sec)

中身を確認

  • SESSION C
sessionC > SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 2585
                 trx_state: RUNNING
               trx_started: 2023-12-01 10:08:15
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 15
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 0
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

お！ ちゃんと入ってるYO！
改めて SESSION B からデータを取得してみるとまぁ入ってないですよね。

sessionB > SELECT * FROM testtable;
Empty set (0.00 sec)

じゃぁここでもう一回 BEGIN してみましょうか

  • SESSION A
sessionA > BEGIN;
Query OK, 0 rows affected (0.00 sec)

さて INNODB_TRX はどうなってるかな、と

  • SESSION C
sessionC > SELECT * FROM information_schema.INNODB_TRX\G
Empty set (0.01 sec)

予想通り、無くなっておりました、データ自体も COMMIT されている状態なのでまぁそうなんでしょうｗ

  • SESSION B
sessionB > SELECT * FROM testtable;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

ちなみにその他にも
TRANSACTION の中で ALTER TABLE を実行する、UNLOCK TABLES を実行する、でも同じような状況になります。
いろいろありますねー(笑)

明日のイルカのお友達は

DBRE Summit でもお世話になった @amamanamam さんから 「EXPLAINフォーマットを作ってみるぞの話」 です！
タイトルだけでそそられちゃいますねｗ
今から楽しみです！

Discussion