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

この記事では主に 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 サイトの他の関連記事を参照してください。