MySQLで、『グループごとの連番を不整合なくINSERTする方法』ってあるのでしょうか

Question

２ちゃんねるのようにスレッドごとに一意の連番が付与されるような機能を実現したいと思っているのですが、
良い方法が思いつきません。

書き込みテーブル
|ID(主キー、自動連番)|スレッドID|書き込み番号|内容|
|:--|:--:|--:|
|1|1|1|hogehoge|
|2|1|2|fugafuga|
|3|1|3|piyopiyo|
|4|2|1|foofoo|
|5|2|2|barbar|
|6|3|1|fizfiz|
|7|3|2|bazbaz|
|8|3|3|dardar|

パッと思いついたのは

INSERT INTO 書き込み(スレッドID, 書き込み番号, 内容)
VALUES (1, (SELECT MAX(書き込み番号) + 1 FROM 書き込みテーブル WHERE スレッドID=1), 'nyaaa');

こんな感じにSQL中でグループの書き込み番号MAX値+1を取得する方法ですが、
これだけでは場合によっては重複などの不整合を起こす可能性があるように思えます。
ユニーク制約で弾くことはできそうですが、できればエラー自体発生させたくありません。

良い感じに実現する方法はあるでしょうか・・・

お知恵を貸していただけると幸いです。

Answer 1

###序

最近はWEBプログラムもSQLも書いていないのでちょっとしたリハビリです。間違っていたらすいません。
気付いた人いたら、どんどん訂正してください。

###レス番管理テーブルを作ったらどうでしょう？

商業アプリとかでは割とよくやる手法ですが、レス番だけのテーブルを作ります。

スレッドID(PRIMARY KEY AUTO_INCREMENT)	レス番(INT NOT NULL)
12345	1
12346	5
：	：

###排他処理をしながらレス番を取得

該当スレッドに書き込みを行いたい時は、まずトランザクションを開始した後、レス番管理テーブルに対してスレッドID指定でSELECTをかけてレス番を取得しますが、その際、該当レコードをロックして悲観的排他処理を行います。

START TRANSACTION;
SELECT `レス番` AS MY_NUM FROM `レス番管理テーブル` WHERE `スレッドID` = '該当スレID' FOR UPDATE;

これで、別の接続が同じスレッドIDのレス番を取得しようとしても、上記のトランザクションがコミットされるまで読み取りが行えず、別の接続は待機します。

###書き込みテーブルに書き込みを行った後、レス番管理テーブルをUPDATEしてコミット

INSERT INTO `書き込みテーブル` 
    (`スレッドID`, `レス番`, `内容`) 
    VALUES 
    ('該当スレID', MY_NUM + 1, 'あいうえお');

UPDATE `レス番管理テーブル` SET `レス番` = MY_NUM + 1 WHERE `スレッドID` = '該当スレID';
COMMIT;

トランザクションがコミットされると、待機していた別の接続の試みが自動で実行されるはずです（タイムアウトしていない場合）。

###終わりに

なお、トランザクションが必須になるので、InnoDBなどのストレージエンジンを利用してください。

以上

Comments

[退会済みユーザー]

確か、AUTO_INCREMENT等は、制約に違反して書き込みが失敗した場合やロールバックを行った場合でも、オートナンバリングは戻らないはずなんですよね。
だから、連番の値が飛んでしまうので、レス番にAUTO_INCREMENTを利用するのはあまり得策ではないと考えます。

Answer 2

この手の処理はランク付けで対応します

create table tbl (ID int not null unique auto_increment,
tid int,content varchar(20));
insert into tbl(tid,content) values
(1,'hogehoge'),(1,'fugafuga'),(1,'piyopiyo'),
(2,'foofoo'),(2,'barbar'),
(3,'fizfiz'),(3,'bazbaz'),(3,'dardar');

select ID,tid,(select count(*)+1 from tbl as t2 where  t1.tid=t2.tid and t1.ID>t2.ID) as rank,content
from tbl as t1;

ランク付けを毎回やってもいいですが、コストの高い処理なので
必要に応じて、新規レコードを追加するときにupdateして
更新してもよいかと思います

Answer 3

シンプルな解決策は、スレッドごとにテーブルを作って書き込み番号をAUTO INCREMENTにすればいいかなと思います。
もちろん、テーブルが多くなりすぎることが予想できるので、スレッドの書き込みが1000件になったら過去分用テーブルにまとめていく感じかなと。