Discussion on update solutions when SQL Server concurrent processing exists

This article mainly discusses SQL ServerConcurrency processing7 solutions that are updated as soon as they exist. It has certain reference value. Interested friends can refer to it

Preface

In this section we will talk about the most common situation in concurrency, that is, update when it exists. In concurrency, if a row record does not exist, it will be inserted. If this is not handled well, it is very easy to happen. In the case of inserting duplicate keys, in this article we will introduce seven solutions for updating row records when they exist in concurrency and we will comprehensively analyze the most appropriate solution.

Discuss the seven options of updating if it exists

First we create a test table

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

Copy after login

Solution one(Open transaction)

We createstored proceduresThrough SQLQueryStress to test the concurrency situation, let's look at the first situation.

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

Copy after login

##Comparison of the probability of inserting duplicate keys when 100 threads and 200 threads are opened at the same time Less is still there.

Solution 2(Reduce the isolation level to the lowest isolation level 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

Copy after login

At this time, the problem is still the same as the solution (if Lower the level to the lowest isolation level. If the row record is empty and the previous transaction has not been committed, the current transaction can also read that the row record is empty. If the current transaction is inserted and submitted, the previous transaction will be performed again. At this time, the problem of inserting duplicate keys will appear)

Solution three(raise the isolation level to the highest level 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

Copy after login

In this case it is even worse, it will directly lead to deadlock

At this time, raising the isolation level to the highest isolation level will solve the problem of repeated insertion Key problem, but for updates to obtain exclusive locks without committing, and at this time another process performs

queryobtaining shared locks, which will cause mutual blocking between processes and cause a deadlock, so the highest isolation is known from now on Levels can sometimes solve concurrency problems but can also cause deadlock problems.

Solution 4(Increase isolation level + good lock)

At this time we will add updates based on adding the highest isolation level Lock, as follows:

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

Copy after login

No exceptions were found after running multiple times. Update locks are used instead of shared locks when querying data. , so that on the one hand, the data can be read without blocking other transactions, and on the other hand, it also ensures that the data has not been changed since the last time the data was read, thus solving the deadlock problem. It seems that this solution is feasible, but I don't know if it is feasible if the concurrency is high.

Solution 5(Raise the isolation level to row version control SNAPSHOT)

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

Copy after login

The above solution will also cause the problem of inserting duplicate keys and is not advisable .

Solution 6(Increase isolation level + table variable)

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

Copy after login

After multiple authentications, there are zero errors. It seems feasible to implement it in the form of table variables.

Solution 7(Increase isolation level +Merge)

Use the Merge key to achieve existence, update otherwise, and insert. At the same time, we should pay attention to Set the isolation level to SERIALIZABLE, otherwise there will be a problem of inserting duplicate keys. The code is as follows:

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

Copy after login

Multiple authentications, whether it is 100 concurrent threads or 200 concurrent threads, there is still no exception information.

Summary

In this section we have discussed in detail how to deal with the problem of updating if it exists, otherwise inserting in concurrency. At present, the above three solutions are feasible. .

Solution one(Highest isolation level + update lock)

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; UPDATE dbo.Test WITH ( UPDLOCK, HOLDLOCK ) SET [Counter] = [Counter] + 1 WHERE Id = @Id; IF ( @@ROWCOUNT = 0 ) BEGIN INSERT dbo.Test ( Id, Name, [Counter] ) VALUES ( @Id, @Name, 1 ); END COMMIT GO

Copy after login

I can only think of these three solutions for the time being. I personally recommend solution one and three. Please ask. If you have any suggestions, please leave your comments and I will add them later.

Solution 2(Highest isolation level + table variable)

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

Copy after login

解决方案三（最高隔离级别 + 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

Copy after login

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

The above is the detailed content of Discussion on update solutions when SQL Server concurrent processing exists_MsSql. For more information, please follow other related articles on the PHP Chinese website!