データベースのIncrement機能は、便利で安定的に使える。
個人的には、各テーブルのID値はよほど特殊な仕様じゃない限り、このセットにするのがいいだろう。
でも、仕事でデータベースの設計をしていると、これじゃ足りないという場面がよく発生する。
たとえば「ユーザーごとに連番を振りたい」とか、「カテゴリごとに1から始まる番号を持たせたい」といったケースだ。
単純なAUTO_INCREMENTでは、
テーブル全体で一意な連番しか作れないため、こういった“グループ単位の連番”には対応できない。
こういうとき、設計を雑にすると「とりあえずアプリ側でMAX+1取ればいいか」となりがちだけど、これは地味に危険だ。
並列処理が走った瞬間に、同じ番号が払い出される
レースコンディションが発生する。
つまり、気づいた頃には
データが壊れているやつ。
じゃあどうする？
結論からいうと、
「グループごとのカウンタを別テーブルで管理する」か、
「INSERT時に排他制御をかけながら採番する」という、
方法を取るのが現実的で安全だ。
まず王道なのが、カウンタ管理テーブルを用意するパターン。
例えばこんな感じの構成にする。
group_counters
- group_id (PK)
- current_value
そして、対象テーブルはこう。
orders
- id (PK)
- group_id
- group_seq
このときの流れはシンプルで、トランザクション内で以下をやる。
group_counters を SELECT ... FOR UPDATE でロック
current_value を +1
更新した値を orders.group_seq に使う
COMMIT
これだけで、グループ単位のインクリメントが安全に実現できる。
ポイントは
「FOR UPDATE」でしっかりロックを取ること。
これをやらないと、結局レースコンディションに戻る。
もっと軽量な方法
もう少し軽量にやりたい場合は、INSERT時にサブクエリでMAX+1を取る方法もある。
INSERT INTO orders (group_id, group_seq)
SELECT
1,
IFNULL(MAX(group_seq), 0) + 1
FROM orders
WHERE group_id = 1;
ただしこれは、単体で実行するとやはり競合に弱い。
必ずトランザクション＋適切なロック（例えば対象グループの範囲ロック）とセットで使う必要がある。
MySQLの
トランザクション分離レベルや
インデックス設計にも影響されるので、ちゃんと理解して使わないと事故る。
どんなときにこの仕組みを使うべきか
まず分かりやすいのが、「人間に見せる番号」のケース。注文番号やチケット番号みたいに、「同じユーザー内で連番」「同じ店舗内で連番」といった要件はよくある。
この場合、
グローバルなIDよりも意味のある連番の方がUX的にも扱いやすい。
逆に、内部IDとしてしか使わないなら、素直にAUTO_INCREMENTで十分。無理にグループ単位にする必要はない。
むしろ複雑さだけが増える。
あともう一つの判断軸は「並列性」。
アクセスが少ない管理画面レベルなら多少雑でも問題になりにくいが、
トラフィックが多いAPIでこれをやる場合は、ロック戦略をちゃんと考えないとボトルネックになる。
グループごとにロックが分散される設計にしておくのがコツ。
まとめると、MySQLのAUTO_INCREMENTは強力だけど、「グループ単位で意味のある連番が必要」になった瞬間に限界が来る。
そのときは、データベース側でしっかり排他制御をかけながら採番する設計に切り替えるのが安全で実用的だ。
この手の話は「知ってるかどうか」で設計の質が一段変わる。
地味だけど、現場でめちゃくちゃ効くテクニック。
ちょっと危険なのは、ロックした処理が途中で停止してしまって、長時間ロックになってしまうような障害につながる可能性もある。
高負荷対応
少し調べてみたところ、次のような方法があることがわかった。
CREATE TABLE project_seq (
project_id INT PRIMARY KEY,
seq INT NOT NULL
);
-- 初期値登録
INSERT INTO project_seq (project_id, seq) VALUES (1,0), (2,0);
-- 次の番号を安全に取得
UPDATE project_seq
SET seq = LAST_INSERT_ID(seq + 1)
WHERE project_id = 1;
SELECT LAST_INSERT_ID();
これ、何がいいかというと、「1クエリでインクリメント＋値の取得まで完結する」ところ。
LAST_INSERT_ID()って、AUTO_INCREMENTのときに使う関数というイメージが強いんだけど、実は「直前のUPDATEでセットした値」をセッション単位で保持できる。
ここがミソ。
つまり、
seq = LAST_INSERT_ID(seq + 1) でインクリメントしつつ値を保持して、
その直後に SELECT LAST_INSERT_ID() で安全に取得する。
という流れで、「他のセッションに影響されない連番取得」ができる。
しかもこの方法、明示的にSELECT ... FOR UPDATEを書かなくても、UPDATE自体が行ロックを取るので、自然と排他制御が効く。コードもシンプルになるし、ミスりにくい。
実際の処理イメージはこんな感じになる。
1. project_id を指定して UPDATE を叩く
2. その結果として seq が +1 される
3. 同じコネクションで LAST_INSERT_ID() を取得
4. その値を使って INSERT する
これで、プロジェクトごとの連番が安全に払い出される。
個人的見解
このやり方が特にハマるのは、「グループごとの採番をシンプルに書きたいとき」。
たとえば、プロジェクト単位のチケット番号や、ユーザー単位の投稿番号みたいなやつ。
わざわざトランザクションをガチガチに書かなくても、比較的安全に扱える。
ただし万能ではない。
まず、前提として「必ず同一コネクション内でLAST_INSERT_ID()を取得する」必要がある。
コネクションプールを使っている場合、ここを雑に扱うと普通に事故る。
また、
バッチ的に大量発行する場合や、
極端に高い並列性があるケースでは、やはり
ロック競合がボトルネックになる可能性はある。
それでも、「シンプルに、安全に、グループ別インクリメントを実現したい」という場面では、この方法はかなり実用的。
MySQLのAUTO_INCREMENTは確かに便利だけど、一歩踏み込むとこういうテクニックが効いてくる。
こういう“ちょい裏技っぽい正攻法”を知っていると、設計の引き出しが一気に増える。
地味だけど、現場でめちゃくちゃ役に立つハズ。
あとがき
今回紹介したインクリメントの方法は、「group_idごとに直列化される」ため、同一グループにアクセスが集中する設計ではボトルネックになる点に注意しなければいけない。
でも、安定的なインクリメント処理を構築できるのは、特に大規模システムのDB設計においては重要なポイントでもあると改めて感じることができた。
とりあえず、お試しできる仕事で使ってみることにしようっと！
0 件のコメント:
コメントを投稿