ほげほげ見聞録

技術メモ、備忘録、使い方はそのうち覚える

MySQLのautocommitが原因でセーブポイントのエラーが出た時のメモ

事の始まり

PHPUnitでtestsuiteを実行したら以下エラーが発生。

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT LEVEL2 does not exist


エラー自体は公式のマニュアルにある。
セーブポイントが削除されているのに操作すると発生…という事は分かった。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.4 SAVEPOINT、ROLLBACK TO SAVEPOINT、および RELEASE SAVEPOINT 構文

原因調査

エラーが起きるのは以下のテスト。

DBにテストデータを作る
→DB操作のテスト
→テストデータ消去と「ALTER TABLE t1 AUTO_INCREMENT=1」実行

この流れの「DB操作のテスト」内に実行するコミットで前述のエラーが発生している。

発生条件は以下。

  • testsuiteで複数テストを実行
  • 最後の「ALTER TABLE」を実行(代わりに「UPDATE」など実行してもエラーは出ない)

条件を絞る

テスト範囲を絞ってみると、エラーが起きる流れが分かった。テスト2でトランザクションの開始が増えるとセーブポイントのレベルの数が変わる。

  1. テスト1でトランザクションの開始とセーブポイントの作成をして、コミットしないままALTER TABLEを実行
  2. テスト2でトランザクションの開始とセーブポイントの作成をして、コミット(ここでエラー)
    解決方法はテスト1でコミットを実行する事だと分かった。
    しかし、原因が分からない。

チョットデキル人に聞いてみる

Google先生に張り付いて検索頑張るのも良いけど、詳しい人に教えを乞うのもたまには良いんじゃないかなと。
TwitterMySQL Casual(http://mysql-casual.org/https://mysql-casual.slack.com/)というSlackが存在する事を知っていたので、参加して聞いてみることにした。

世界のyokuさんからの回答抜粋

ALTER TABLEは暗黙のコミットを引き起こすのでそれが原因かもしれません
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.3 暗黙的なコミットを発生させるステートメント


「暗黙のコミット」という機能があるらしい。
というわけで、フレームワーク内で起こっているのは以下の挙動のようだ。

テスト1でコミット時にセーブポイント1作成
→ALTER TABLEでMySQLの方でコミットされてセーブポイント1が消える(セーブポイントのレベルはフレームワーク内に残る)
→テスト2でセーブポイント2作成
→ネストしているトランザクションを順次コミットする際に、消えたセーブポイント2をRELEASEしようとしてエラー


また、ERROR 1305が発生する以下の一連のクエリも教えていただいた。
確かにALTER TABLE実行後にコミット(RELEASE)しようとしてERROR 1305が発生している。

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

mysql57 5> SAVEPOINT LEVEL1;
Query OK, 0 rows affected (0.00 sec)

mysql57 5> ALTER TABLE t1 Engine = InnoDB;
Query OK, 0 rows affected (0.32 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql57 5> RELEASE SAVEPOINT LEVEL1;
ERROR 1305 (42000): SAVEPOINT LEVEL1 does not exist

mysql57 5> SAVEPOINT LEVEL2;
Query OK, 0 rows affected (0.00 sec)

mysql57 5> RELEASE SAVEPOINT LEVEL2;
ERROR 1305 (42000): SAVEPOINT LEVEL2 does not exist


んでもって、以下autocommitをオフにした場合のクエリを教えていただいた。

mysql57 5> SET autocommit= 0;
Query OK, 0 rows affected (0.07 sec)

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

mysql57 5> SAVEPOINT LEVEL1;
Query OK, 0 rows affected (0.00 sec)

mysql57 5> ALTER TABLE t1 Engine = InnoDB;
Query OK, 0 rows affected (0.19 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql57 5> RELEASE SAVEPOINT LEVEL1;
ERROR 1305 (42000): SAVEPOINT LEVEL1 does not exist

mysql57 5> SAVEPOINT LEVEL2;
Query OK, 0 rows affected (0.00 sec)

mysql57 5> RELEASE SAVEPOINT LEVEL2;

Query OK, 0 rows affected (0.00 sec)


autocommitをオフにしてテストを実行してみる

テスト1のALTER TABLE前に`SET autocommit= 0`を実行したところ、テスト2のコミット時にセーブポイントのエラーが出なくなった。

参考

なにはともあれマニュアルを読む。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文

autocommitの挙動の話。
MySQLのトランザクション制御がキモい話 - なからなLife
MySQLのAUTOCOMMIT(オートコミット)覚え書き | OpenGroove

autocommitはセッション内でしか有効にならないが、my.cnfでも設定できるらしい。
MySQLのautocommitとトランザクション分離レベルのメモ - Qiita

まとめ

何もない時は一般的なDB知識でクエリを作って実行させるだけだが、謎のエラーに出くわした時に困るので使用DBMSのマニュアルはこまめに確認しておこう。
関連コミュニティなどで詳しい人に助けを求めるととても勉強になる。

回答の詳細はMySQL Casualに参加すると見られるので、気になる人は登録するといいんじゃなかろうか。
自分の質問文は分かりにくいので、もっとうまく質問できるようになりたいなぁ。