やりたいこと
MySQLを使ってジョブキューを実装する時（など）に、テーブルのある行を何らかの理由でマークしたいことがあります。例えば各行が処理するジョブを表しているとして
- 処理されていないジョブに、処理中マークを付ける
- 処理が終わったジョブに、処理済みだとマークを付ける
などなど。そしてジョブ処理は並列に行われることにすると、マーク処理はアトミックでなければなりません（複数のスレッドが、自分がマークに成功したと思い込むと、ジョブの処理が複数動きかねません）。
もちろん強いトランザクションを付ければ原理的にはどうとでもなるのですが、ジョブが複数のスレッド・プロセスで分散実行する場合を考えると、同期によるコストを極力下げたいものです。
こんな感じの前提のもと、MySQL/SQLではマーク処理をどう書くのか、という点について調べてみました。
ざっくりした方針
問題をもう少し分割します。
1. マークされていない行をどうやって見つけるのか
2. 行を極力低コストにマークするにはどうすればいいのか
1.については、何らかのタイミングでSELECT文が発行されているものとします。例えば定期的に……。効率的な方法については、この記事では検討しません。理想的にはイベント駆動になるとよさそうですが。
2.については、ロックフリーのアルゴリズムで見られるような、Compare-And-Swap(CAS)に類似したテクニックでマークをします。つまり「指定した行が期待通り未マークであったなら、マーク済みの行へと更新する」ということをアトミックにやります。もちろん実際は行レベルでのロックが入っているはずですが、実行コストは極力抑えられると期待されます。
具体的な実装例
ジョブを表すテーブルを用意します。テーブルには、ジョブを一意に決定するidと、マーク済みかどうかを表すmarkerがあります。
CREATE TABLE job_table (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
marker BOOLEAN
) ENGINE=InnoDB;
未マークのジョブがあるかどうかを調べるには、SELECT文を発行します。ここでは単に最大1件取得します。
SELECT id, marker FROM job_table WHERE marker=0 LIMIT 1;
取得した未マークのジョブをマークするには、次のUPDATE文を使います。
UPDATE job_table SET marker=1 WHERE id=<id> AND marker=0;
<id>には、SELECT文で取得したマークしたいジョブのIDを与えます。WHERE節にmarker=0があることで、未マークであることを確認しつつマークを付けることができるようになっています。
マークが成功したかどうかは、SQL文発行後のいわゆるrows affectedを調べればいいです。JDBCでいえばexecuteUpdate()かな。1が返ってくればマークできたことになります。
まとめ
例では単なる2値をとるmarkerでしたが
- 自分が期待している値であることを
WHERE節で表す - UPDATEによって、UPDATEのWHERE節に引っかからないように行を書き換えることで、UPDATE操作が1回のみ成功する
これらのテクニックは汎用性があるもののように感じますし、もっと複雑な状態変化をつくることもできます。
参考
-
http://www.engineyard.co.jp/blog/2013/5-subtle-ways-youre-using-mysql-as-a-queue-and-why-itll-bite-you/
- この記事に書いたロックの問題以外にも、MySQLのテーブルをキューとして利用する場合の典型的な問題についての方針が述べられています