SQLite3で「database schema has changed」のログが出るとき

この前自分のサイトにアクセスが急増する出来事があって、そのときにwebサーバに見たことないエラーログが出力されていた。

下記のような感じのログ。

code:17,database schema has changed

このエラーは、PDOのプリペアドステートメントを使ってデータベース(sqlite3)を更新する箇所で発生していた。

エラー文の意味を英語ブログを読み解きながら調べてみたら、どうも以下のようなことが起きたらしい（日本語ブログには全然説明がなかったぞ！）

プリペアドステートメントの機能として、SQL構文のチェックの高速化のためにDBの構造（スキーマ）をキャッシュする、というものがあるらしいんだけど、このキャッシュを利用する際に、もし他のプロセスがDBテーブルの登録・削除処理行って構造（スキーマ）が変わってしまっていた場合、キャッシュが利用できなくなったとみなしてエラーを発生させる、という動作をするようだ。このとき掲題のようなエラーログが出力される。

コードでいうと以下のようなところでエラーが発生する（PHP 5の処理）。

  //DBに接続
  try {
    $db = new PDO('sqlite:hogege/tes.sqlite3');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  } catch (Exception $e) {
    logging_err("cannot open sql database ".$e->getTraceAsString());
    return (-1);
  }
  //SQL文発行
  try {
    //★ここの処理中でDBのスキーマが変更される  
    $sql = "select * from hoge_table where user_id = :user_id";
    $stmt = $db->prepare($sql);
    $stmt->bindValue(':user_id', 1234, PDO::PARAM_INT);
    $stmt->execute();
  } catch (PDOException $e){
    //★このエラーパスに入る
    $err = $db->errorInfo();
    $errcode = $err[1];
    $errmsg = $err[2];
    logging_err("code:".$errcode.",".$errmsg);
    unset($db);
    return (-1);
  }

つまり今回のエラーは、あるユーザーがサイトを閲覧しているときにちょうど同じタイミングで他のユーザーが登録処理を行ってしまって、そのせいでプリペアドキャッシュが使えなくなってSQLの読み込みエラーが発生した、というようなことが起きたんだと思う（登録処理はうまくいったけど閲覧処理が失敗していた）。

う～ん・・、こんなんでいちいちエラーを吐いてたら使い物にならないやんsqlite・・。データの矛盾が生じないようにうまく排他をしていても、キャッシュの考慮まではできていないという感じなんだろうか。キャッシュがなかったら直でSQL発行にいくようなモードがあるかと思って探したんだけど見つからなかったし、ちょっと困りものだなこの現象は・・。

どうしたらこのエラーを回避できるかいろいろ試してみたけど、どうもテーブルを変更する処理のところでトランザクションを設けるとうまいこといくようだ。

コードでいうと下記のような感じ（PHP 5の処理）。

  try {
    $db->exec("BEGIN EXCLUSIVE;");★
    $sql = "create table hoge_table (hogege integer, fugaga text)";
    $stmt = $db->prepare($sql);
    $stmt->execute();
  } catch (PDOException $e){
    $db->exec("ROLLBACK;");★
    $err = $db->errorInfo();
    $errcode = $err[1];
    $errmsg = $err[2];
    logging_err("code:".$errcode.",".$errmsg);
    unset($db);
    return (-1);
  }
  $db->exec("COMMIT;");★

おそらく「EXCLUSIVE」のトランザクションを入れると、テーブル変更処理のところで厳密にREAD処理も排他してくれるようになるんだと思う。

sqlite3ではDB更新処理のときにデフォルトで「DEFERRED」というロックがかかるらしいんだけど、正直今回のようなエラーが発生するようでは「DEFERRED」は使い物にならなさそう。今度からテーブル操作をするときは必ず上記の「EXCLUSIVE」を使うようにしよう。

こういうのってデータベースをいじる人にとっては常識的なことなのかな、自分がすごい基本レベルのポカをしているようでなんか不安になってきた・・。