SQL Server concurrent processing, update solution discussion, SQL Server

SQL Server concurrent processing, update solution discussion, SQL Server

Preface

In this section, we will talk about the most common situation of concurrency, that is, update. If no row record exists in concurrency, insert it. At this time, it is very easy to insert duplicate keys, this article describes the seven solutions for updating Row Records in concurrency and the most suitable solutions for comprehensive analysis.

Seven solutions are updated if they exist

First, create a test table.

IF OBJECT_ID('Test') IS NOT NULL DROP TABLE TestCREATE TABLE Test( Id int, Name nchar(100), [Counter] int,primary key (Id), unique (Name));GO

Solution 1(Start transaction)

We use SQLQueryStress to test the concurrency of the stored procedure. Let's look at the first scenario.

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 ); COMMITGO

When 100 and 200 threads are enabled at the same time, the chance of inserting duplicate keys is relatively small.

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 ); COMMITGO

At this time, the problem remains the same as the solution (if the lower level is the lowest isolation level, if the row record is empty, if the previous transaction is not committed, the current transaction can also read the row record as null, if the current transaction is inserted and committed, and the previous transaction is committed again, the insertion of duplicate keys will occur)

Solution 3(Improve 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 ); COMMITGO

In this case, it is worse, and the deadlock is caused directly.

Increasing the isolation level to the highest isolation level will solve the problem of inserting duplicate keys, but it is not submitted for updating to obtain the exclusive lock, at this time, another process queries and obtains the shared lock, which will cause mutual congestion between processes and lead to deadlocks. Therefore, the highest isolation level can sometimes solve the concurrency problem but also lead to deadlocks.

Solution 4(Improve isolation level + Good lock)

In this case, we will add an update Lock Based on the highest isolation level, as shown below:

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 ); COMMITGO

No exception is found during multiple operations. Update locks instead of shared locks are used to query data. In this way, data can be read but other transactions are not blocked, second, it also ensures that the data has not been changed since the last read, which solves the deadlock problem. It seems that this solution is feasible. If it is high concurrency, I wonder if it is feasible.

Solution 5(Upgrade the isolation level to line version control SNAPSHOT)

ALTER DATABASE UpsertTestDatabaseSET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE UpsertTestDatabaseSET READ_COMMITTED_SNAPSHOT ONGO 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 ); COMMITGO

The preceding solution also causes the insertion of duplicate keys.

Solution 6(Improve 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 ); COMMITGO

It is also a zero error after multiple authentication, and it seems feasible to implement it through the form of table variables.

Solution 7(Enhanced isolation + Merge)

The Merge key is used to implement the existence, that is, update, or insert. At the same time, we should pay attention to setting the isolation level to SERIALIZABLE. Otherwise, the duplicate key insertion problem may occur. 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 ); COMMITGO

No exception information is displayed for multiple authentication tasks, including 100 concurrent threads and 200 concurrent threads.

Summary

This section describes in detail how to handle the problem of existence, update, or insert in concurrency. Currently, the above three solutions are feasible.

Solution 1(Maximum 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  COMMITGO

For the time being, I can only think of these three solutions. I personally recommend solution 1 and solution 3. What are your comments? If your comments are feasible, I will add them later.

Solution 2(Maximum 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 ); COMMITGO

Solution 3(Maximum isolation level + 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 ); COMMITGO

For the time being, I can only think of these three solutions. I personally recommend solution 1 and solution 3. What are your comments? If your comments are feasible, I will add them later.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.