MySQLのトランザクション制御がキモい話
MySQL Casual Advent Calendar 2016 - Qiitaの5日目の記事です。
AdventCalendar自体初参加でドキドキ。
トランザクションの開始は、BEGINしたときじゃない！
MySQLでは、BEGIN（START TRANSACTION。長いので、以下、特筆すべき場合以外は「BEGIN」で）を宣言しても、内部的にはまだトランザクションを開始してません。
SQLを投げたタイミングで、トランザクション開始になります。
このとき、更新のない、FOR UPDATEもないSELECT文でも、トランザクションが開始されます。
なにそれきもい。
「AutoCommit=ON/OFF」による違い
副作用
「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するのは余計なお世話。
念のため、エビデンス
各タイミングで、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（＝MySQLのinnodb_trx）には何も現れません。
やはりSQL発行時にトランザクション開始となります。ここでV$TRANSACTIONに現れます。
ただしSELECTのときはトランザクションは開始されません！
というわけで、Oracle文化を引きずってMySQL（AutoCommit=OFFをメインで使う）案件に入ると、
- トランザクション開始宣言をしないでSQLぶん投げる
- SELECTでもトランザクションが開始されることを知らずにSELECT投げ、後続処理がないときにCOMMITし忘れる。
- トランザクションを開始しっぱなしになる
というパターンでハマる可能性が大ですね。
