MySQLにはSEQUENCEがない

必要があって検索したら出てきたのだが…これはテーブルをロックしないとレースコンディションの問題が発生するだろう。なのにロックをかけるのはアンチパターンだとなっている。処理速度しか見ていないのだろうか？

ほぼ同時に二つのプロセスが「データのインサート」を行い、そのあと「最後に追加されたIDの問い合わせ」をした場合、処理の順番が「インサート」「インサート」「問い合わせ」「問い合わせ」になってしまう可能性がある。そうすると、二つの違うプロセスに同じ番号が返ってしまう。

データをインサートする前にライトロックをかけておくと、同じタイミングの処理でも「ロック」「処理待ち」「インサート」「問い合わせ」「ロック解除」「ロック」「インサート」「問い合わせ」「ロック解除」になるので問題が無いはず。

このサンプルでいうと、「採番テーブルの削除処理に失敗する」「なぜか消えていない採番テーブルのデータがある」という事態で問題が発生したことが分かるだろう。

「問い合わせのアトム性」の問題だろうか？（検索すると「アトム性」がほとんど出てこないので、用語の記憶間違いがありそうだ。レースコンディションに絡んでatomicという表現はあるから、日本語訳が別にあるのだろうか。）
この件の場合、「インサート」と「読み込み」が「必ずセットで連続して行われる（不可分に行われる）」という「アトム性」の保証がないためにレースコンディションが発生する。

個人的に考えると、「データをインサートする際に別のユニーク制約付きの列にデータ（乱数）を書き込んで、それを元に連番を読み出す」という方式にすると思う。（というか、今から実装しなきゃならないのか？）

ユニーク制約のある列にインサートできたかを確認すれば、ロックしなくても他のプロセスから独立していることをユニーク制約が保証してくれる。あとはインサートしたデータを元に普通にセレクトすればいい。この場合、インサートが成功した時点で読み込める数字が確定しているのでアトム性の問題が無い。

元の例でいえば「採番テーブル」には「id」の他にもう一つユニークなフィールドを設定する事になる。そして LAST_INSERT_IDは使わない。（というか前に別の案件でLAST_INSERT_IDの使用を考えた時も、レースコンディションが排除できなくて使うのをやめた記憶がある。）

……まぁそもそもMySQLにSEQUENCEがないのが悪い。PostgreSQLにはあるのに。*1

（追記）MySQLのlast_insert_idは接続ごとに違う答えを返しそうなので、この問題は起きないのかもしれないが、そもそも戻ってくる値が想像以上に使えなさそうでもっと困ることが分かった。

あと、Webサーバーには「DB接続のプール」とかがあるので動作が確信できない点も気になるか。そこまで考えるとSEQUENCE機能以外信頼できないのか。

*1:というか、PostgreSQLにあったからそれを使う設計をしたのに、それをMySQLしか使えない場所に移植する羽目になったところでSEQUENCEがない事を知ったので愚痴っている。