SET AUTOCOMMIT=0後の動作を誤解していたらしいの巻

PHP+MySQL環境でmysql_pconnect関数を使っていて、謎な挙動に悩まされていたらしいが、その原因が「SET AUTOCOMMIT=0」にあるらしいということで。実は、問題が浮上して調査するまでは「SET AUTOCOMMIT=0」が問題だとは微塵も思っていなかった。なので、「SET AOTOCOMMIT=0」を実行したときの動作を誤解していたのだろうということで、実際に手を動かして記憶を上書きすることにしたらしい。
一応、参考までに、MySQLにおけるトランザクション関連の構文は以下の辺りに。

とりあえず、「SET AUTOCOMMIT=0」とトランザクションの開始/コミット/ロールバックとの関係を調べることにする。

「START TRANSACTION」と「BEGIN」「BEGIN WORK」は、導入されたバージョンの違い程度らしいので、それ以上深入りせずに「START TRANSACTION」を使うことにする
mysql_pconnectを使ったテストは面倒なので、mysql_connectで生成されたコネクションを使いまわして複数の処理を続けて実行することで、mysql_pconnectがコネクションを使いまわす状況を擬似的に再現することにする

前提とするテーブルは次のようなものとする。

CREATE TABLE hoge(
      id BIGINT AUTO_INCREMENT
    , title VARCHAR(32) NOT NULL
    , tt DATETIME NOT NULL
    , ts TIMESTAMP
    , PRIMARY KEY(id)
)

実行するクエリとして以下の4通りを考える。(各ケースにおいて、○印がついている部分を実行する)

	Case 1	Case 2	Case 3	Case 4
SET AUTOCOMMIT=0	○	○
START TRANSACTION	○	○	○	○
INSERT(A) (*1)	○	○	○	○
COMMIT	○		○
ROLLBACK		○		○
INSERT(B) (*2)	○	○	○	○

(*1), (*2)はそれぞれ以下のようなクエリ(@AUTOCOMMIT@の部分は、自動コミットモード(0:OFF, 1:ON)に置き換える)

-- (*1)
INSERT INTO hoge(title, tt) VALUES('ac=@AUTOCOMMIT@:A', 'yyyy-mm-dd hh:mm:ss')
-- (*2)
INSERT INTO hoge(title, tt) VALUES('ac=@AUTOCOMMIT@:B', 'yyyy-mm-dd hh:mm:ss')

これで何を見ているかというと、「SET AUTOCOMMIT=0」の影響が、あるトランザクションをコミット/ロールバックした後も続くのかどうかという点(←ここが誤解している点)。
実行結果は次のとおり。(MySQL 4.1.24/5.0.67/5.1.32のいずれでも同じ結果だったので、代表してMySQL 4.1.24の結果)(実行したプログラムは別途)

=== test(MySQL41, 0, true, 2011-04-03 22:14:12) ===
    === con-1 ===
        === SET AUTOCOMMIT = 0                                                     (autocommit = 0) ===
        === START TRANSACTION                                                      (autocommit = 0) ===
        === INSERT INTO hoge(title, tt) VALUES('ac=0:A', '2011-04-03 22:14:12')    (autocommit = 0) ===
        === COMMIT                                                                 (autocommit = 0) ===
        === INSERT INTO hoge(title, tt) VALUES('ac=0:B', '2011-04-03 22:14:12')    (autocommit = 0) ===
    === con-2 ===
        === SELECT id, tt, title, ts FROM hoge WHERE tt = '2011-04-03 22:14:12'    (autocommit = 1) ===
            id=268, title=ac=0:A    , tt=2011-04-03 22:14:12
=== test(MySQL41, 0, false, 2011-04-03 22:14:13) ===
    === con-1 ===
        === SET AUTOCOMMIT = 0                                                     (autocommit = 0) ===
        === START TRANSACTION                                                      (autocommit = 0) ===
        === INSERT INTO hoge(title, tt) VALUES('ac=0:A', '2011-04-03 22:14:13')    (autocommit = 0) ===
        === ROLLBACK                                                               (autocommit = 0) ===
        === INSERT INTO hoge(title, tt) VALUES('ac=0:B', '2011-04-03 22:14:13')    (autocommit = 0) ===
    === con-2 ===
        === SELECT id, tt, title, ts FROM hoge WHERE tt = '2011-04-03 22:14:13'    (autocommit = 1) ===
=== test(MySQL41, 1, true, 2011-04-03 22:14:14) ===
    === con-1 ===
        === START TRANSACTION                                                      (autocommit = 1) ===
        === INSERT INTO hoge(title, tt) VALUES('ac=1:A', '2011-04-03 22:14:14')    (autocommit = 1) ===
        === COMMIT                                                                 (autocommit = 1) ===
        === INSERT INTO hoge(title, tt) VALUES('ac=1:B', '2011-04-03 22:14:14')    (autocommit = 1) ===
    === con-2 ===
        === SELECT id, tt, title, ts FROM hoge WHERE tt = '2011-04-03 22:14:14'    (autocommit = 1) ===
            id=272, title=ac=1:A    , tt=2011-04-03 22:14:14
            id=273, title=ac=1:B    , tt=2011-04-03 22:14:14
=== test(MySQL41, 1, false, 2011-04-03 22:14:16) ===
    === con-1 ===
        === START TRANSACTION                                                      (autocommit = 1) ===
        === INSERT INTO hoge(title, tt) VALUES('ac=1:A', '2011-04-03 22:14:16')    (autocommit = 1) ===
        === ROLLBACK                                                               (autocommit = 1) ===
        === INSERT INTO hoge(title, tt) VALUES('ac=1:B', '2011-04-03 22:14:16')    (autocommit = 1) ===
    === con-2 ===
        === SELECT id, tt, title, ts FROM hoge WHERE tt = '2011-04-03 22:14:16'    (autocommit = 1) ===
            id=275, title=ac=1:B    , tt=2011-04-03 22:14:16

title列に「ac=n:A」をINSERTする処理はトランザクションを明示的に開始しているので、コミットしたかロールバックしたかでINSERT処理が反映されるかどうかが決まる。
一方、「ac=n:B」をINSERTする処理はトランザクションの開始を明示していないので、「SET AUTOCOMMIT=0」を実行したかどうかに依存している。
これが何を意味するかというと、mysql_pconnectを使用しているアプリにおいて一度「SET AUTOCOMMIT=0」が実行されてしまったら、コネクションが使いまわされる限り自動コミットモードがOFFになるので、いろいろと予期せぬ出来事(何)が起こることになる。
それを知らずに「単一のDMLだからトランザクション要らないや」と言った瞬間に、INSERTしたのにINSERTされてない、みたいな現象に悩まされることになる。なので、トランザクションの開始を宣言していないのにコミット/ロールバックを発行するという、ある種の気持ち悪さを覚えるコードを書くことになる。うーん、謎だ(謎)。
ちなみに、使用したプログラム(ユーザーID、パスワード、ホスト名、ポート番号は編集してある(謎))。

2011-0403-mysql.php

今まで、どう誤解していたかというと、「SET AUTOCOMMIT=0」することはトランザクションを開始するためのSQL文を実行することと同等だと思っていたらしい。よくよく考えるとそんなわけない罠。
今まで、どうして誤解していたかというと‥(つづく(謎))

HHeLiBeXの日記正道編

日々の記憶の記録とメモ‥

SET AUTOCOMMIT=0後の動作を誤解していたらしいの巻