MySQLのInnoDBに対してAUTOCOMMITが無効であるのにも関わらずCOMMITするのを忘れていた。

MySQLのInnoDBに対してAUTOCOMMIT = 0の状態でsqlを発行しCOMMITしなかった(するのを忘れた)場合、次のようになった(idはAUTO_INCREMENT)。

a. スクリプト実行前

id, value
1 taro
2 jiro
3 saburo

b. スクリプト実行中(shiroを追加するsqlを発行/COMMITせずにスクリプト終了)

id, value
1 taro
2 jiro
3 saburo
4 shiro

c. スクリプト実行後

id, value
1 taro
2 jiro
3 saburo

d. 同じsqlを再度発行し、きちんとCOMMITした場合。

id, value
1 taro
2 jiro
3 saburo
5 shiro

そもそもの原因は、「sqlをphpスクリプト上で発行してもDBに反映されないが、同sqlをphpMyAdminから直接発行すると正常に反映される」という現象が発生し、ついでに対象となるテーブルのid(AUTO_INCREMENT)がなぜか飛び番号になってしまったこと。いろいろと調べた結果、原因は単純にAUTOCOMMITの設定値の問題だった。

MySQLでInnoDBテーブルを利用していた場合、トランザクション時の挙動はAUTOCOMMITの値によって異なる。AUTOCOMMIT = 1の場合は、ユーザーが明示的にmysql_query(“BEGIN”)としなくともトランザクションを自動的に設定してコミットまで行ってくれる。AUTOCOMMIT = 0の場合は、ユーザーがmysql_query(“BEGIN”)とすることが前提となっており、またコミットには同じく明示的にmysql_query(“COMMIT”)が必要となる(もしくはmysql_query(“ROLLBACK”)とすることで反映をキャンセルする)。

参照:13.5.10.2. InnoDB と AUTOCOMMIT

DBの更新については通常であれば次のように常にAUTOCOMMIT = 0として明示的にBEGINおよびCOMMIT/ROLLBACKを行うようにしていたが、今回は例外的に下記を通さずにsqlを個別に発行しなければならない状況になり、かつ、その発行の直前にAUTOCOMMIT = 0を発行していることを忘れていたためにいくらか悩んでしまった。

$dbHandle = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die('Could not connect: ' . mysql_error());
mysql_select_db("TEST");

$sql = "INSERT INTO table_name ...";

mysql_query("SET AUTOCOMMIT = 0");
mysql_query("BEGIN");

try {
	$result = mysql_query($sql);	
	if (!$result) {
		throw new Exception('exception : ' . mysql_error()) ;
	}	
	mysql_query("COMMIT");

} catch(Exception $e) {
	echo $e->getMessage;	
	mysql_query("ROLLBACK");
}

if (!$dbHandle) {
    mysql_close($dbHandle) ;
}

ちなみにAUTOCOMMITの値は下記sqlによって取得する

SELECT @@autocommit;

なお上記のテーブルの履歴で、dにおいて追加されたshiroのidが5となっているのは次の理由からだと思われる。

1. bにおいて発行されたsqlによってidが一つ使用(予約?)される。
2. cにおいてCOMMITなしに接続が切れることによるROLLBACKでレコードは元に戻されるがidは元に戻らない。
3. よってdの段階においては一旦使用されたidを飛ばしてその次の5が使用される。

参照:13.5.10.9. 暗黙的なトランザクション コミットとロールバック

もし自動コミット モードがオフで、最後のトランザクションを明示的にコミットせずに接続を閉じると、MySQL はそのトランザクションをロールバックします。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA