ある条件の最大値+1を初番するにはロックが必要ですか？

質問

以下のテーブルでcolumn毎に連番を振る場合、
テーブルロックが必要でしょうか？

テーブル test
id column
1 a
2 a
1 b

新規データ登録手順
1.トランザクション
2.select max(id)+1 from test where column = b for update
3.insert test into (id,column) values (selectで取得した値,b)
4.トランザクション終了

これで、column毎に登録されているIDの最大値+1で
重複せずにデータのINSERTが保障されるでしょうか？

回答No.3

nora1962 · ベストアンサー

一応動くと思いますが
・ロックの粒度が大きい。'b'についてMAX(ID)を取得して挿入する処理の間
　他のカラムの取得もロックされる。
　同時実行性に問題はないか。
　後、ロック時間が長くなるとMysqlのinnodb_lock_wait_timeoutによって
　処理がエラーになる場合がある。
点が気になります。
独自に( column, max_id )を持つ発番テーブルを持った方がいいのではな
いかと思います。columnが主キーです。

お礼

そうなんです。ロック行が多すぎるので心配しておりました。
なるほど。確かにおっしゃる通り、InnoDBの場合、
発番テーブルを持つ方が良さそうです。ありがとうございました。

回答No.4

chukenkenkou

テーブルは、InnoDBである必要がありますか？
MyISAMでもよいなら、複合（複数列）インデクスにして、2番目の構成列をauto_incrementにすることで、実装が容易になります。

create table `test`
(`id` int auto_increment,
`column` varchar(5),
primary key(`column`,`id`))

上記のような定義にすることで、`column`列の値毎に`id`列に通番が自動採番されます。

http://dev.mysql.com/doc/refman/4.1/ja/example-auto-increment.html

お礼

す、すごいっす。目がうろこです。
そんな裏技ができたとは・・

InnoDBである必要はないので、MyISAMでやってみますね。

回答No.2

yambejp

テーブルtestではidがダブっている時点で本来のidとしての機能を果たしていませんね
column管理用に別テーブルをつくり、idをプライマリにしてauto incrementを
つけて管理するのがよろしいのでは？

補足

すみません、説明が不足しておりませした。
プライマリーキーは、id,columnになります。

column a のIncrement値は2
column b のIncrement値は1

つまり次ぎに、aのユーザーが登録したら、3番を初番
bのユーザーが登録したら、2番を初番したいのです。
ちなみに、columnのパターンは無限にありテーブルをわけるわけにも
いきません。

回答No.1

Tasuke22

IDにUNIQUE属性を付けていればいいのではないですか？
この例では１が二つあるからエラーになりますが。