JAVAのAPIからDBの新規レコードを採番する際に順次処理をする方法について

Question

###前提・実現したいこと
JAVAのAPIから発行するSELECT/INSERT文を順次処理することで、採番する登録番号の一意性を保ちたいです。

###発生している問題・エラーメッセージ
javaでサーバーサイドのAPIを作成しています。
insertクエリの中で、登録番号を現在DBにあるレコードの最大値+1で採番しているのですが
同時に1000件程度のリクエストが来ることがあり、同じ番号を採番してしまうケースが発生します。

###該当のソースコード
申し訳ございませんが、手元にソースがないので明日追加します。

###試したこと
・対象のメソッドにsynchronizeをつける
→インスタンス内部の値というよりはクエリで取得する値の問題なので解決できず
・threadを使う
→これが正攻法かと思われるが、、、使い方がよくわからないです（すいません）、そもそもスレッドの実行ってクラス内で行うものなのか？別クラスからインスタンス生成して実行するものなのか・・・

※既存テーブルへの登録のため、テーブル定義の変更は難しいです。。。

###補足情報(言語/FW/ツール等のバージョンなど)
language:JAVA
DB:postgres
O/R:mybatis
Framework:spring
※mybatisのconnectionpool機能を使用しております

Comments

[退会済みユーザー]

運用中のシステム？開発中のシステム？どっちだろう。

[oreyade]

運用中のシステムです。
既存のテーブルにレコードを登録するのですが、新規で作られた機能により同時リクエストの数が跳ね上がったため質問の課題が発生しました。

[A.Ichi]

同時に1000件程度のリクエストは全て特定のjava（スレッド？）にて処理がされ他のプログラムでからのテーブルへのINSERTは、発生しない前提でしょうか？

[oreyade]

コメントありがとうございます。
他のプログラムからのテーブルへのINSERTは発生する可能性があり得ます。
既存の登録するプログラムがあり、今回作成するのは大量データを一度に入れられるものだと思ってください。

・・・そして自分でも色々考えてたんですが、エラーが起きたらリトライする方法が最善な気がしてきました。

[A.Ichi]

度々の質問ですみませんが、空き番は発生させない仕様でしょうか？（番号は必ず連番で飛び番無し）

Answer 1

IDの採番をDB側に任せたらどうでしょうか？
PostgreSQLだとserial型です。

参考サイト

Comments

[oreyade]

回答ありがとうございます。
完全に考えてなかったため、情報を記載する事を忘れていました。。。
既存のテーブルに追加するため、テーブル定義の変更は難しい状況です。

[popobot]

後からでも自動採番にできると思いますよ
http://www.glamenv-septzen.net/view/725

※運用中だからソースコードは変更できるけど、DB構造は変えられないというのは、個人的にはよくわかりません。問題があり改善する必要があるなら、最も適切な方法を取ればいいだけだと思います。そうしないと、更にメンテしにくいシステムになっていきます。

[oreyade]

おっしゃる通りだと思います。
その上で、ですがserialにした場合、現在のMax値＋１のメソッドが他プログラムで使われているので、そこを全部修正することになります、調査する時間と現在の納期を考えるとかなり難しいのです。。。
でもでもばかりですいません。。。

Answer 2

ストアドを作成してみました 採番項目がユニーク制約が有る事を前提としています。参考まで

CREATE TABLE table_s (s1 INT PRIMARY KEY, s2 TEXT);


CREATE FUNCTION insert_max_no(data TEXT) RETURNS INT AS
$$
        DECLARE _rn INT;
BEGIN
    LOOP
        BEGIN
            INSERT INTO table_s(s1,s2) SELECT MAX(s1)+1, data FROM table_s RETURNING s1 INTO _rn;
            RETURN _rn;
        EXCEPTION WHEN unique_violation THEN CONTINUE;
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;