Database exists as updated concurrency processing-go

Objective

In this section we talk about the most common situation in concurrency is updated, if there is no row record in the concurrency inserted, at this time is not processed very easy to insert duplicate key situation, this article we describe the concurrency in the presence of the update row records seven scenarios and we have to comprehensively analyze the most appropriate solution.

To explore the existence of the update seven scenarios

Let's start by creating a test table

IF object_id (' Test ') is not NULL    DROP table testcreate table Test (    ID int,    Name nchar (+),    [Counter] int , primary key (ID),    Unique (Name)); GO

Solution one (open transaction)

We unify the creation of stored procedures to test concurrency by sqlquerystress, let's look at the first case.

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)     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
   

There is less chance of opening 100 threads and 200 threads to insert duplicate keys at the same time.

Solution two (reduce isolation level to the lowest isolation level uncommited)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)     SET TRANSACTION ISOLA tion 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 point 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 read to the row record is empty, if the current transaction is inserted and committed, then the previous transaction commits the problem of inserting duplicate keys)

Solution three (raise isolation level to the highest level serializable)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)     SET TRANSACTION ISOLA tion 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

Worse in this case, directly to lead to deadlock

Raising the isolation level to the highest isolation level resolves the insertion of a duplicate key, but does not commit for an update to get an exclusive lock, while another process queries for a shared lock will cause the process to block each other and cause a deadlock. So we know that the highest isolation level can sometimes solve concurrency problems but also lead to deadlock problems.

Solution IV (LIFT isolation level + good lock)

Now let's add the update lock on top of adding the highest isolation level, as follows:

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)     SET TRANSACTION ISOLA tion 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 was found running multiple times, by querying data using an update lock instead of a shared lock, so that the data can be read without blocking other transactions, and the data is not changed since the last time the data was read, which resolves the deadlock problem. It seems like such a solution is feasible, if it is high concurrency do not know whether it is feasible.

Solution Five (elevation isolation level for row versioning 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 (+) = CAST (@Id as NCHAR)     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 above solution also appears to insert a duplicate key issue is undesirable.

Solution VI (Elevation isolation level + table variable)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)    DECLARE @updated tabl E (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);    

After many authentication is also 0 error, seemingly through table variable form to achieve feasible.

Solution VII (LIFT isolation level +merge)

Through the merge key to implement the presence of the update is otherwise inserted, and we should be aware of setting the isolation level of SERIALIZABLE Otherwise insert duplicate key problem, the code is as follows:

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)    SET TRAN Isolation LE VEL SERIALIZABLE     BEGIN TRANSACTION    MERGE Test as [target]    USING        (SELECT    @Id as Id        ) as source< C9/>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

Multiple authentication no exception information is still on 100 threads or 200 threads concurrently.

Summarize

In this section, we discuss in detail how to deal with the solution of the problem of insertion in concurrency, which is the case, and now the above three scenarios are feasible.

Solution one (highest isolation level + update lock)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)     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

Solution two (highest isolation level + table variable)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)    DECLARE @updated tabl E (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 three (highest isolation level + Merge)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)    SET TRAN Isolation LE VEL SERIALIZABLE     BEGIN TRANSACTION    MERGE Test as [target]    USING        (SELECT    @Id as Id        ) as source< C9/>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 can only think of these three solutions, personal comparison recommendation one and plan three, what do you have in mind, please leave your comments if feasible, I will follow up.

2017-06-03 Update

This blog post is very exciting, but also for the side dishes I re-learn the existence of the update is the reverse insert solution. This article re-update has been two days, during which I have been looking at this aspect of something more in-depth understanding of some of the basic aspects of things or is too general and I am not very understanding of the cause, the food is not terrible, terrible is not deep learning from the thought of their own is right, you say.

First we have to understand what the role of Updlock and Holdlock locks is, Holdlock is similar to the Serializable isolation level, we can read for shared locks, but cannot be updated and deleted and inserted until the current concurrent transaction is complete. The explanation for the blog in Updlock is that it allows you to read the data (without blocking other transactions) and update the data at a later time, while ensuring that the data has not been changed since the last time the data was read. When we use it to read records, we can add update locks to the records we fetch, and the record of the locks cannot be changed in other threads until the end of the transaction of the thread. It is easy to understand that it does not block concurrent queries and insertions, but blocks updates or deletes data that is queried for the current transaction, and updates locks switch to exclusive locks when queried for the existence of the data. So for the end of the above three solutions, we will elaborate.

Solution One (HOLDLOCK)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)     BEGIN transaction;
   update  dbo. Test with (HOLDLOCK)    SET     [Counter] = [Counter] + 1    WHERE   Id = @Id;     IF (@ @ROWCOUNT = 0)        BEGIN            INSERT  dbo. Test                    (Id, Name, [Counter])            VALUES  (@Id, @Name, 1);        END     Commitgo

If we do not add the HOLDLOCK lock hint, although the update acquires an exclusive lock, the exclusive lock does not persist until the end of the transaction, which causes the problem of inserting duplicate keys, and when we add the HOLDLOCK lock hint The above also talks about the highest isolation level in pessimistic concurrency, The lock hint continues until the end of the transaction, and when there is a concurrent request, the update occurs when the query to the data exists, but the transaction has not yet been committed, and the other request will also find that the row record exists, but will be blocked by the current transaction update operation Lock, if the query to the data does not exist at this time.

Solution Two (UPDLOCK + HOLDLOCK)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)     BEGIN transaction
   
    if EXISTS (SELECT  1                from    dbo. Test with (UPDLOCK, HOLDLOCK)                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
   

For the above query comparison the first solution we added the Updlock update lock instead of the shared lock for Select, which is intended to not cause blocking when the row record queried by the passed variable ID does not exist, so that it does not block the insertion of other transactions and ensures that the row record has not been modified since the last time , for Holdlock to ensure that the lock is released to the transaction, thus achieving our expectations. Summing up a sentence, if the query period row record exists then the locked resource is queried for the existence of the row record, if the row record does not exist during the query, then the holdlock to get the range lock on the primary key to prevent the insertion of a duplicate key before the lock is released. So updlock in order to resolve the concurrency update does not block other transactional queries, holdlock prevents the concurrent insertion of duplicate keys.

Solution Three (SERIALIZABLE + Merge)

IF object_id (' Testpro ') is a not NULL    DROP PROCEDURE Testpro; GO CREATE PROCEDURE Testpro (@Id INT) as    DECLARE @Name NCHAR (+) = CAST (@Id as NCHAR)    BEGIN transaction
   
    merge Test with (SERIALIZABLE) 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
   

Database exists as updated concurrency processing-go