Discussion on update solutions when SQL Server concurrent processing exists_MsSql

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

Solution one (Open transaction)

We create stored procedures Through 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

##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

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

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

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

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

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

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

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

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

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

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!