なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLのトランザクション制御がキモい話

MySQL Casual Advent Calendar 2016 - Qiitaの5日目の記事です。

AdventCalendar自体初参加でドキドキ。

トランザクションの開始は、BEGINしたときじゃない!

MySQLでは、BEGIN(START TRANSACTION。長いので、以下、特筆すべき場合以外は「BEGIN」で)を宣言しても、内部的にはまだトランザクションを開始してません。


SQLを投げたタイミングで、トランザクション開始になります。


このとき、更新のない、FOR UPDATEもないSELECT文でも、トランザクションが開始されます。


なにそれきもい。

「AutoCommit=ON/OFF」による違い

AutoCommit=ONのとき

トランザクションSQLを発行するたびにBEGIN/COMMITで完了し、ロールバックできません。
複数SQLを束ねて1つのトランザクションにしたいときに、BEGINを宣言すると、COMMIT/ROLLBACKするまでの間、AutoCommitがOFFな状態になります。


まあこれはわかる。
AutoCommit=ONがデフォルトなのはキモいけどね。


で、BEGIN宣言した後に、最初に投げたSQLのタイミングでトランザクション開始となります。
更新のない、FOR UPDATEもないSELECT文でも、トランザクションが開始されます。


なにそれキモい。

AutoCommit=OFFのとき

トランザクションの開始には、BEGINの宣言すらいりません。トランザクション開始していないときにSQL投げると、そこから勝手にトランザクションが始まります。


しつこいですが、更新のない、FOR UPDATEもないSELECT文でも、トランザクションが開始されます。
なにそれきもい。


副作用

「SELECTして処理対象が1件以上あったら、次の処理に進む。0件だったら、後続処理すべてスキップして終了する。」って、わりとよくありますよね。


これを、ここに書いたとおり実装すると、トランザクションが開始しっぱなしになります。


クライアントセッション自体を完全に終了(切断)する場合は問題はないのですが、コネクションプーリングなどでセッションを使いまわす場合が危険です。


大雑把ですが、こんな感じのプログラムです。

処理0)セッションにはAutoCommit=0が適用されている。
処理1)セッションオブジェクトを受け取る
処理2)SELECT ~ (暗黙のトランザクション開始)
処理2-1)1件以上:対象件数分、後続処理をしてCOMMIT/ROLLBACKしたあと、3へ
処理2-2)0件:なにもしないで3へ
処理3)処理終了につき、セッションオブジェクトを返却する

処理2-2を通った後に処理3で戻されてきたセッションは、トランザクションが開始したままです。


この時、処理2で実行したSELECT文でタッチしたテーブルすべてについて、メタデータロックを取っています。


トランザクション終了時に、持っているメタデータロックが解放されますが、この例だと、メタデータロックを持ちっぱなしになります。


メタデータロックが保持されたままだと、他のセッションからDDLが投げられませんので、定時実行ジョブで日別PARTITIONの切り替えなんかを組み込んでいると、思いっきりブロックされます。


メタデータロックが取れなくてDDLが待たされた状態のところに、後からDMLがやってくると、そのDMLも待たされます。




いつも1秒で帰ってくるSQLが数十分たっても返ってこない、みたいなことがあったら、このケースにハマっていることを疑ってみていいと思います。


犯人となるセッションを探すのは大変です。そのあたりは、以前のエントリにも書いたとおりです。
atsuizo.hatenadiary.jp


特に犯人が誰なのかという情報はMySQL5.7で初めてperformance_schema.metadata_locksが実装されましたので、ソレ以前のバージョンでは、他のテーブルの情報から類推していくしかありませんでした。


なお、真犯人は、「トランザクション制御を意識しないでコード書いた奴」です。


ていうか、メタデータロック問題を抜きにしても、次にたまたまコネクションプールから同じセッションを掴んだ処理にトランザクションのCOMMIT/ROLLBACKを委ねるとかありえないから。


このケースの場合、処理2-2でもCOMMITもするか、処理2のブロック内ではCOMMITせず、処理3の返却前にまとめてCOMMITすることで、この問題は回避できます。

ベスト・プラクティス?

異論は認める。今時点の自分の感覚として、これがいいんじゃね、くらい。

グローバルレベルのシステム変数:AutoCommit=ON

手作業でデータメンテするときにCOMMITし忘れで作業ミスする可能性が減る。
間違った更新を直前回避することを優先したい場合はOFFで、と思うが、そもそも直接叩いてデータメンテするのやめようぜ。

セッションレベルのシステム変数:AutoCommit=ON

コネクション生成は共通部品的に処理するかもしれないけど、ここでAutoCommit=OFFするのは余計なお世話。

トランザクションの開始:「BEGIN」の宣言必須。

BEGIN宣言前に投げたSQLについてはAutoCommit=OFF状態に遷移するので、処理対象の有無を確認するSELECT(処理1)の段階では、トランザクションは開始されません。


あくまで、更新系のSQLを投げる際に、その整合性を取りたい範囲を開始する直前にBEGIN宣言することで、トランザクション開始≒BEGINになります。


トランザクションは、「要件上必要な整合性を保てる長さのうち、最も短いものにする。間には余計な処理は挟まない。」とするのが吉。

念のため、エビデンス

各タイミングで、sysdateとinformation_schema.innodb_trxを叩いて、どのタイミングでトランザクションが開始されているかを確認します。


innodb_trx.trx_queryが表示されてしまうのは、トランザクションを開始した自身のセッションで参照しているからってことで、ご愛嬌。

AutoCommit=1のケース

BEGIN宣言前は、SELECTを実行してもトランザクションは開始されません。
BEGIN宣言後は、SELECTを実行したところでトランザクションが開始されています。

mysql> select @@global.autocommit,@@session.autocommit;
+---------------------+----------------------+
| @@global.autocommit | @@session.autocommit |
+---------------------+----------------------+
|                   1 |                    1 |
+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:54:12 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from test_tbl t where col_id = 'x'; -- 存在しないデータを検索
Empty set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

mysql> -- begin宣言後にSELECT
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:54:24 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

mysql> select * from test_tbl t where col_id = 'x'; -- 存在しないデータを検索
Empty set (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:54:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id  | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query                                   | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 8592398 | RUNNING   | 2016-11-25 14:54:31 | NULL                  | NULL             |          0 |                   9 | select * from information_schema.innodb_trx | NULL                |                 0 |                 0 |                0 |                   360 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                     10000 |                0 |                          0 |
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:54:49 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)
AutoCommit=0のケース

BEGIN宣言前でも、SELECTを実行したところでトランザクションが開始されています。
そこで一旦COMMITしてトランザクションを終了させます。
BEGIN宣言後は、SELECTを実行したところでトランザクションが開始されています。

mysql> set @@session.autocommit=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.autocommit,@@session.autocommit;
+---------------------+----------------------+
| @@global.autocommit | @@session.autocommit |
+---------------------+----------------------+
|                   1 |                    0 |
+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:56:12 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from test_tbl t where col_id = 'x'; -- 存在しないデータを検索
Empty set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id  | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query                                   | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 8592399 | RUNNING   | 2016-11-25 14:56:12 | NULL                  | NULL             |          0 |                   9 | select * from information_schema.innodb_trx | NULL                |                 0 |                 0 |                0 |                   360 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                     10000 |                0 |                          0 |
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:56:27 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

mysql> -- begin宣言後にSELECT
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:56:39 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:57:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from test_tbl t where col_id = 'x'; -- 存在しないデータを検索
Empty set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id  | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query                                   | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 8592400 | RUNNING   | 2016-11-25 14:57:02 | NULL                  | NULL             |          0 |                   9 | select * from information_schema.innodb_trx | NULL                |                 0 |                 0 |                0 |                   360 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                     10000 |                0 |                          0 |
+---------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2016-11-25 14:57:15 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

Oracleどうなんだ?

そもそも、AutoCommit=OFFがデフォルトです。


ただし、SQL*PlusはCOMMIT打たずにEXITすると暗黙COMMIT、Windowの×ボタンで落とすとROLLBACKという、別のところで制御されています。


接続時点でトランザクションを開始宣言した状態(=MySQLでBEGIN宣言した状態)です。
COMMITすると、そこで一旦トランザクションが終わりますが、次のトランザクションが開始(BEGIN宣言状態まで)されます。


この状態では、V$TRANSACTION(=MySQLinnodb_trx)には何も現れません。


やはりSQL発行時にトランザクション開始となります。ここでV$TRANSACTIONに現れます。


ただしSELECTのときはトランザクションは開始されません!



というわけで、Oracle文化を引きずってMySQL(AutoCommit=OFFをメインで使う)案件に入ると、

  1. トランザクション開始宣言をしないでSQLぶん投げる
  2. SELECTでもトランザクションが開始されることを知らずにSELECT投げ、後続処理がないときにCOMMITし忘れる。
  3. トランザクションを開始しっぱなしになる

というパターンでハマる可能性が大ですね。


まとめ


MySQL触り始めたときにキモいキモいと思うこの感覚、忘れずにいたいですね。



次回は、12/7(水)、@mita2さんの予定です。