SQL Server の同時処理が存在する場合の更新ソリューションに関するディスカッション_MsSql

この記事では主に SQL Server の 7 つのソリューションについて説明します 同時実行処理 興味のある方は参照してください

このセクションでは、次のように説明します。同時実行で最も一般的な状況は、行レコードが存在しない場合に更新することですが、これがうまく処理されない場合、重複キーを挿入するのは非常に簡単です。行レコードが同時に存在する場合に更新する方法を 7 つ紹介し、最適な解決策を包括的に分析してみましょう。

存在したらすぐに更新するための 7 つのオプションについて説明します

まず、テスト テーブルを作成します

IF OBJECT_ID('Test') IS NOT NULL
 DROP TABLE Test

CREATE TABLE Test
(
 Id int,
 Name nchar(100),
 [Counter] int,primary key (Id),
 unique (Name)
);
GO

解決策 1

(トランザクションを有効にする) SQLQueryStress を使用して、統合された ストアド プロシージャ

を作成してテストします最初のケースを見てみましょう。

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 
 BEGIN TRANSACTION
 IF EXISTS ( SELECT 1
    FROM Test
    WHERE Id = @Id )
  UPDATE Test
  SET  [Counter] = [Counter] + 1
  WHERE Id = @Id;
 ELSE
  INSERT Test
    ( Id, Name, [Counter] )
  VALUES ( @Id, @Name, 1 );
 COMMIT
GO

重複キーが挿入される可能性は比較的低いですが、100 個のスレッドと 200 個のスレッドが同時に開かれている場合には依然として存在します。

解決策 2

(分離レベルを最も低い分離レベル UNCOMMITED に下げる)

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 BEGIN TRANSACTION
 IF EXISTS ( SELECT 1
    FROM Test
    WHERE Id = @Id )
  UPDATE Test
  SET  [Counter] = [Counter] + 1
  WHERE Id = @Id;
 ELSE
  INSERT Test
    ( Id, Name, [Counter] )
  VALUES ( @Id, @name, 1 );
 COMMIT
GO

現時点では、問題は解決策と同じです (レベルを最も低い分離レベルに下げた場合、行レコードが空である場合、前のトランザクションが送信されていない場合、現在のトランザクションは行レコードが空であることを読み取ることもできます。現在のトランザクションが挿入されて送信されると、前のトランザクションが送信されると重複キーが挿入されるという問題が発生します。もう一度)

解決策 3

(分離レベルを最高レベル SERIALIZABLE に上げる)

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 BEGIN TRANSACTION
 IF EXISTS ( SELECT 1
    FROM dbo.Test
    WHERE Id = @Id )
  UPDATE dbo.Test
  SET  [Counter] = [Counter] + 1
  WHERE Id = @Id;
 ELSE
  INSERT dbo.Test
    ( Id, Name, [Counter] )
  VALUES ( @Id, @Name, 1 );
 COMMIT
GO

この場合はさらに悪化し、デッドロックに直結します

今回は、分離レベルを最高の分離レベルに上げることで問題は解決します。重複キーの挿入の問題は解決されますが、更新ではコミットせずに排他ロックを取得し、この時点で別のプロセスが共有ロックを取得するために クエリ

を実行します。プロセス間の相互ブロックを引き起こし、デッドロックを引き起こすため、今後は最高の分離レベルが同時実行の問題を解決できる場合もありますが、デッドロックの問題を引き起こす可能性があることがわかります。

解決策 4

(分離レベルを上げる + 適切なロック)この時点では、次のように、最も高い分離レベルの追加に基づいて更新ロックを追加します。データのクエリ時に共有ロックの代わりに更新ロックを使用すると、第 1 に、他のトランザクションをブロックすることなくデータを読み取ることができます。第 2 に、データが最後に変更されてから変更されていないことも保証されます。このようにしてデッドロックの問題を解決します。この解決策は実行可能であるように見えますが、同時実行性が高い場合に実行可能かどうかはわかりません。

解決策 5

(行バージョン管理スナップショットの分離レベルを上げる)

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 BEGIN TRANSACTION
 IF EXISTS ( SELECT 1
    FROM dbo.Test WITH(UPDLOCK)
    WHERE Id = @Id )
  UPDATE dbo.Test
  SET  [Counter] = [Counter] + 1
  WHERE Id = @Id;
 ELSE
  INSERT dbo.Test
    ( Id, Name, [Counter] )
  VALUES ( @Id, @Name, 1 );
 COMMIT
GO

上記の解決策は、重複キーの挿入の問題も引き起こすため、お勧めできません。

解決策 6

(分離レベル + テーブル変数を増やす)

ALTER DATABASE UpsertTestDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
 
ALTER DATABASE UpsertTestDatabase
SET READ_COMMITTED_SNAPSHOT ON
GO 

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 
 BEGIN TRANSACTION
 IF EXISTS ( SELECT 1
    FROM dbo.Test
    WHERE Id = @Id )
  UPDATE dbo.Test
  SET  [Counter] = [Counter] + 1
  WHERE Id = @Id;
 ELSE
  INSERT dbo.Test
    ( Id, Name, [Counter] )
  VALUES ( @Id, @Name, 1 );
 COMMIT
GO

多くの認証を経て、テーブル変数の形式で実装することが可能であると思われます。

解決策 7

(分離レベルを上げる + マージ)

存在を達成するか更新するには Merge キーを使用します。同時に、分離レベルを SERIALIZABLE に設定することに注意する必要があります。そうでない場合は、重複キー挿入の問題 コードは次のとおりです:

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 DECLARE @updated TABLE ( i INT );
 
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 BEGIN TRANSACTION
 UPDATE Test
 SET  [Counter] = [Counter] + 1
 OUTPUT DELETED.Id
   INTO @updated
 WHERE Id = @Id;
 
 IF NOT EXISTS ( SELECT i
     FROM @updated )
  INSERT INTO Test
    ( Id, Name, counter )
  VALUES ( @Id, @Name, 1 );
 COMMIT
GO

複数の認証の後、100 スレッドでも 200 スレッドでも、異常な情報はありません。

概要 このセクションでは、存在する場合は更新、そうでない場合は同時挿入の問題の解決策を詳細に説明しました。現在、上記の 3 つの解決策が実行可能です。

解決策 1

(最高の分離レベル + 更新ロック)

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 SET TRAN ISOLATION LEVEL SERIALIZABLE 
 BEGIN TRANSACTION
 MERGE Test AS [target]
 USING
  ( SELECT @Id AS Id
  ) AS source
 ON source.Id = [target].Id
 WHEN MATCHED THEN
  UPDATE SET
    [Counter] = [target].[Counter] + 1
 WHEN NOT MATCHED THEN
  INSERT ( Id, Name, [Counter] )
  VALUES ( @Id, @Name, 1 );
 COMMIT
GO

今のところ、私はこの 3 つの解決策しか思いつきません。個人的には解決策 1 と 3 をお勧めします。うまくいったらコメントを残してください。後で追加します。

解決策 2

(最高の分離レベル + テーブル変数)

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 DECLARE @updated TABLE ( i INT );
 
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 BEGIN TRANSACTION
 UPDATE Test
 SET  [Counter] = [Counter] + 1
 OUTPUT DELETED.Id
   INTO @updated
 WHERE Id = @Id;
 
 IF NOT EXISTS ( SELECT i
     FROM @updated )
  INSERT INTO Test
    ( Id, Name, counter )
  VALUES ( @Id, @Name, 1 );
 COMMIT
GO

解决方案三（最高隔离级别 + Merge）

IF OBJECT_ID('TestPro') IS NOT NULL
 DROP PROCEDURE TestPro;
GO
 
CREATE PROCEDURE TestPro ( @Id INT )
AS
 DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
 SET TRAN ISOLATION LEVEL SERIALIZABLE 
 BEGIN TRANSACTION
 MERGE Test AS [target]
 USING
  ( SELECT @Id AS Id
  ) AS source
 ON source.Id = [target].Id
 WHEN MATCHED THEN
  UPDATE SET
    [Counter] = [target].[Counter] + 1
 WHEN NOT MATCHED THEN
  INSERT ( Id, Name, [Counter] )
  VALUES ( @Id, @Name, 1 );
 COMMIT
GO

暂时只能想到这三种解决方案，个人比较推荐方案一和方案三， 请问您有何高见，请留下您的评论若可行，我将进行后续补充。

以上がSQL Server の同時処理が存在する場合の更新ソリューションに関するディスカッション_MsSqlの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。