“identity”なしで次のID番号を取得するための最良の方法

deadlock locking sql sql-server transactions
“identity”なしで次のID番号を取得するための最良の方法

私はいくつかのレコードをレガシーデータベースのテーブルに挿入しなければなりません、そしてそれは他の古いシステムによって使用されているので、テーブルを変更することは解決策ではありません。

問題は、ターゲット表にint主キーがありますが、ID指定がないことです。 だから私は次に利用可能なIDを見つけてそれを使う必要があります:

select @id=ISNULL(max(recid)+1,1) from subscriber

ただし、これを行っているときに他のアプリケーションがテーブルに挿入されないようにして、問題が発生しないようにします。 私はこれを試しました:

begin transaction
    declare @id as int
    select @id=ISNULL(max(recid)+1,1) from subscriber WITH (HOLDLOCK, TABLOCK)
    select @id
    WAITFOR DELAY '00:00:01'
    insert into subscriber (recid) values (@id)
commit transaction
select * from subscriber

SQL Management Studioの2つの異なるウィンドウで、1つのトランザクションが常にデッドロックの犠牲者として殺されます。

私も最初に `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ‘を試してみましたが、同じ結果になりました…

次のIDを確実に取得し、それを使用する方法について、他の誰か(または私)が危険を冒さずに使用できるようにするための良い提案がありますか。

これについては触れなかったことを申し訳ありませんが、これはSQL 2000サーバーなので、FOR UPDATEやOUTPUTのようなものは使用できません。

アップデート:これは私にとってうまくいった解決策です。

BEGIN TRANSACTION
    DECLARE @id int

    SELECT  @id=recid
    FROM    identities WITH (UPDLOCK, ROWLOCK)
    WHERE table_name = 'subscriber'

    waitfor delay '00:00:06'

    INSERT INTO subscriber (recid) values (@id)

    UPDATE identities SET recid=recid+1
    WHERE table_name = 'subscriber'

COMMIT transaction

select * from subscriber

WAITFORを使用すると、複数の接続を確立して同時実行を引き起こすためにクエリを数回開始できます。

答えをくれたQuassnoiと、貢献してくれた他の皆さんに感謝します! 驚くばかり!

  8  7


ベストアンサー

別のテーブルを作成します。

t_identity (id INT NOT NULL PRIMARY KEY CHECK (id = 1), value INT NOT NULL)

単一行では、この行をロックし、 IDENTITY`が必要になるたびに value`を1ずつ増やしてください。

単一のステートメントで新しい値をロック、増分、および戻すには、次のようにします。

UPDATE  t_identity
SET     value = value + 1
OUTPUT  INSERTED.value

更新したくない場合は、単にロックしてから発行してください。

SELECT  value
FROM    t_identity WITH (UPDLOCK, ROWLOCK)

これはトランザクションの終わりまでテーブルをロックします。

ancient_table`をめちゃくちゃにする前に、常に最初に t_identity`をロックすると、決してデッドロックにはなりません。

10


識別列を持つ別の表を追加し、この新しい表と列を使用して古い表の識別値を選択/生成します。

更新:INSERTSの頻度(および既存の行数* e )に応じて、 _ x = *に新しいIDENTITY値をシードできます。 これにより、従来の挿入との競合が回避されます。 悲しい解決策、確かに不完全な解決策、しかし何か考えるべきことはありますか?

4


*編集*これは基本的に@Quassnoiが意図した方法です。ループ内で実装するだけなので、同時に複数のウィンドウに対して実行して、うまく機能することを確認できます。

セットアップ:

ユーザーの既存のテーブルを作成します。

テーブルSubscriberを作成します(recid intはnull主キーではありません)

不足しているIDを追跡するために新しいテーブルを作成します。これが複数のテーブルで必要な場合は、テーブルを追跡するための列を追加することもできますが、この例では行っていません。

CREATE TABLE SubscriberID(SubscriberID int)をSubscriberIDに挿入するvalues(0) - 行が最初に存在しなければならない

テストスクリプトを作成し、これを複数のウィンドウに配置して同時に実行します。

@idtableテーブルを宣言する - 使用する次のIDを保持する(id int)@x <5000を宣言する@x intを宣言する@x <intを宣言する@ x <5000を設定する -  @ x = @ x 1トランザクションを開始する次に使用するIDを取得し、他のユーザーをロックアウトします。実際のテーブル内の次のIDをサブスクライバ値に挿入する(@y)

commit --print @x waitfor delay '00:00:00.005 'end  -  while
  • ————————————————- ————– * * EDIT *これが私の最初の試みです。これは、ループ内および複数のウィンドウで同時に実行されると、最終的にいくつかのデッドロックが発生します。 上記の方法は常に機能します。 私は(holdlock)を使ってトランザクションのすべての組み合わせを試し、トランザクション分離レベルをシリアライズ可能にするなどを設定しました。 しかし、上記の方法では実行できませんでした。

セットアップ:

テーブルサブスクライバを作成します(recid int null以外の主キー)。

IDをキャプチャするために使用されます。

@idtableテーブルを宣言する(id int)

インサート:

サブスクライバに挿入OUTPUT INSERTED.recid INTO @idtable SELECT ISNULL(MAX(recid)、0)

新しいIDをリストします。

@idtableから*を選択

すべてのIDをリストします。

加入者から*を選択

3


2番目は最初の完了を待つだけなので、ここでデッドロックになるべきではありません。 あなたの問題は、あなたがトランザクションを作成していて、そのトランザクション中に別のロックを追加しているということです。

また、IDを取得してから2つの別々のステートメントで使用していますが、1つの解決策ですべてを実行できます。

トランザクション分離レベルを設定可能直列化可能加入者へのトランザクション挿入の開始(再ID)

これはあなたがあなたのインサートに一貫性しかないことを保証するべきです。 しかし、レガシーアプリケーションもこのテーブルを使用していると指定した場合、新しいレコードを挿入したときにこれと競合しないことを確認できますか。

0


タイトルとURLをコピーしました