ある条件の最大値+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 回答日時： 2008/05/05 14:16

一応動くと思いますが

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

この回答へのお礼

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

お礼日時：2008/05/05 19:37

No.4 回答者： chukenkenkou 回答日時： 2008/05/05 17:08

テーブルは、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-a …

この回答へのお礼

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

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

お礼日時：2008/05/05 19:34

No.2 回答者： yambejp 回答日時： 2008/05/05 01:21

テーブルtestではidがダブっている時点で本来のidとしての機能を果たしていませんね

column管理用に別テーブルをつくり、idをプライマリにしてauto incrementを
つけて管理するのがよろしいのでは？

この回答への補足

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

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

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

補足日時：2008/05/05 10:27

No.1 回答者： Tasuke22 回答日時： 2008/05/04 23:25

IDにUNIQUE属性を付けていればいいのではないですか？

この例では１が二つあるからエラーになりますが。