採番処理と排他制御

初歩的な話だと思います。ですが、ちょっとミスもあったりしたので、自分への戒めとして書き残しておこうと思います。

「日付毎に連番」というのがしたかった

最初は、単純に「とあるテーブルから現在の日付のMAX値+1を取得して、それを新しいレコードに割り当てる」という方法で対応しました。

ただ、排他制御をしていなかったので、同時に採番処理が実行された時に、同じ番号が取得されてしまい重複が発生しました。

SELECT (MAX(no) + 1) AS next_no FROM hoge WHERE create_datetime >= '2020-12-10 00:00:00' AND create_datetime <= '2020-12-10 23:59:59';

排他制御の方法

排他制御には「楽観ロック」と「悲観ロック」があります。（今回は悲観ロック）

重複を回避するため排他制御をする必要がありました。

なので、採番テーブルを作り、採番処理時に採番テーブルのレコードをロックするようにしました。

採番処理の流れ

1. トランザクション開始
2. 採番テーブルからレコードを取得してロックする。
3. 取得したレコードのcreate_dateをチェックして、現在の日付であるならnoを+1してレコードを更新する。
   日付が違う場合はnoを1に更新する。（create_dateも更新する。）
4. トランザクションをコミット（ロックが解除される）

採番テーブルの例

CREATE TABLE assign_no (
    id SERIAL NOT NULL,
    no INT NOT NULL,
    create_date DATE NOT NULL,
    PRIMARY KEY(id)
);

（2）の採番テーブルの取得とロック方法（PostgreSQLの場合）

PostgreSQLの場合は、SELECT分の最後に「FOR UPDATE」と書くことで、取得した行をロックすることができます。

他のトランザクションが下記SQLを実行するタイミングで、既にレコードがロックされている場合は待ち状態になります。

SELECT * FROM assign_no FOR UPDATE;

ロックを取得していたトランザクションがコミット（もしくはロールバック）されると、ロックが解除されて更新された番号が取得されます。

最後に

単純な連番であれば「シーケンス」や「オートインクリメント」などのデータベースの機能を使えば対応できます。

ですが、特殊なルールで採番する場合は、採番テーブルを用意して排他制御をする必要があります。

今後は忘れないようにしないと。