連番管理を専用テーブルで行う方法について、考えてみました。
■方針
連番管理テーブル N を作成し、連番用レコードを事前に挿入しておく。
| ID |
USED |
| A-00001 |
0 |
| A-00002 |
0 |
| A-00003 |
0 |
番号が必要になったら、USED = 0 の中から、最小 ID を取得し、USED = 1 に設定する
■要件
- 番号の取得時に、ロック待ちが発生しないこと
- 同時に取得する場合に、同じ番号を渡してしまわないこと
- 高速であること
- 番号を取得したトランザクションが失敗した場合、番号を返却できること
■テーブル作成クエリー
-- DROP TABLE N
-- テーブル N を作成
CREATE TABLE N(
ID char(7) NOT NULL,
USED bit NOT NULL
)
-- インデックス PK_ID を作成
CREATE INDEX PK_ID ON N (ID)
-- テストデータを挿入
DECLARE @NN int,
@N char(7)
BEGIN TRANSACTION
SET @NN = 1
WHILE @NN < 10000
BEGIN
SET @N = 'A-' +
RIGHT('00000' + CONVERT(varchar, @NN), 5) INSERT INTO N
VALUES (@N, 0)
SET @NN = @NN + 1
END
-- 'A-08000' 未満を USED に設定
UPDATE N
SET USED = 1
WHERE ID < 'A-08000'
COMMIT TRANSACTION
-- 結果表示
SELECT * FROM N
■番号取得クエリー
-- トランザクション開始
BEGIN TRANSACTION
-- 変数宣言
DECLARE @ID char(7)
-- 番号を取得し、USED = 1 に設定
UPDATE N
SET USED = 1, @ID = ID
WHERE ID = (
SELECT MIN(ID)
FROM N WITH (READPAST)
WHERE USED = 0
)
-- 番号を表示
SELECT @ID
-- 任意の処理
-- トランザクション終了
COMMIT TRANSACTION