Lock your records: sqlserver locks a row of records in the database

I have never understood the lock. Today I found an interesting post connecting to bbs. csdn. nettopics390797757 is similar to my doubts about the lock, that is, how to lock a record and prevent concurrency from saying that two identical records are inserted in the stored procedure. The script of the stored procedure is as follows: ALTERPROC [dbo]. [Insert] @

About lock this one has not understood, just today found a more interesting post, connection for http://bbs.csdn.net/topics/390797757 with my doubts about the lock is almost, is, how to lock a record, to prevent concurrency, the stored procedure inserts two identical records. The Stored Procedure script is as follows: alter proc [dbo]. [Insert] @

About lock this one has not understood, just today found a more interesting post, connection for http://bbs.csdn.net/topics/390797757

It is similar to my doubts about the lock, that is, how to lock a record to prevent concurrency

Two identical records are inserted in the stored procedure,

The script of the stored procedure is as follows:

ALTER PROC [dbo].[Insert]    @Tid IntASBEGIN     IF NOT EXISTS(SELECT 1 FROM Table WHERE TId = @Tid)    BEGIN        INSERT INTO Table (INSERTDATE,TID     ) VALUES (GETDATE(),  @Tid);         ENDEND

I checked his stored procedure and made a judgment on whether it exists. However, this judgment is far from enough in concurrent execution, because multiple sessions may judge that a record does not exist and insert the record at the same time, it is not surprising that the problem described in the post occurs. This test is also very simple. I am familiar with the sqlquerystress tool, enable multiple threads and insert them cyclically multiple times.

First, create a table. Similar to this stored procedure, create a non-unique index on the table.

If exists (select 1 from sys. objects where type = 'U' and name = 'testlock1') drop table testlock1 -- create table testlock1 (id int, Createdate datetime ,) -- create index index_1 on testlock1 (id) -- create proc ups_TestLock @ I intasbeginbegin trybegin tranif not exists (select 1 from t where id = @ I) begin insert into testlock1 values (@ I, GETDATE (); endcommitend trybegin catchrollbackend catchend

For concurrency testing, we use the sqlquerystress tool. The test script is as follows:

Declare @ I intset @ I = cast (rand () * 100000 as int) -- generate a random number less than 100000 exec test_p @ I

In the sqlquerystress tool, 30 threads are enabled, and 2000 pieces of data are inserted in each ready-made loop.

For example

Okay, record insertion is complete (this article is not a performance test and you don't need to pay too much attention to the time indicator). Is there any duplicate data?

Directly, there are images with truth, and there are a lot of Repeated Records.

Why? As mentioned above, because there may be multiple calls to determine that a record does not exist and is inserted at the same time, this will cause the insertion of duplicate data.

So how can we make judgments to prevent problems caused by similar concurrency?

So I thought about the lock. In fact, when I thought about the lock, I was not very familiar with it. I never understood the explicit lock prompts. it would be okay if there were any problems. So let's test it.

So I changed the stored procedure to this

Alter proc ups_TestLock @ I intasbeginbegin trybegin tranif not exists (select 1 from t with (xlock, rowlock) where id = @ I) -- Note that xlock, rowlock, row-level exclusive lock begin insert into testlock1 values (@ I, GETDATE (); endcommitend trybegin catchrollbackend catchend

Use truncate table testlock1 to clear the test table and continue the test.

What is puzzling is that there are repeated records this time. Although there are fewer records than at the beginning, the locking problem is still not solved.

I want to think about it. I don't know where the problem is. When I use sp_lock @ spid to view the session lock information, there is a key-level exclusive lock, but why is there no lock record?

Later, I checked it online. Some people said that a unique index was required to lock a row of records. After I changed the index to a unique index

The following script

drop index index_1 on testlock1

create unique index index_1 on testlock1(id)

During the test, I found that there were no Repeated Records. Is it a coincidence?

In addition, the secondary test proves that, even if there are no duplicates, after creating a unique index on the query condition and adding xlock and rowlock, the record is actually "locked" and the concurrency problem is solved.

It's not over yet. Why? When I got off work, I was still thinking about this problem on the bus ......

Later, I thought that the non-unique index could not "Lock" the record and duplicate the record. The unique index solved the concurrency problem,

The problem is certainly the concurrency, because it is multi-thread parallel insertion, will it be inserted at the same time by different threads,

That is to say, two threads A and B insert A data with the id of 12345 at the same time. When they determine before the insertion, there is indeed no data with the id of 12345 in the database at that time.

So we inserted them at the same time. Based on the reasoning here, duplicate data must be inserted in different calls,

It's so exciting to think about it. Test it.

So I changed the table structure to the following and added an insert session ID column.

If exists (select 1 from sys. objects where type = 'U' and name = 'testlock1') drop table testlock1 -- create table testlock1 (id int, Createdate datetime, SessionID varchar (50 )) -- create index index_1 on testlock1 (id) alter proc ups_TestLock @ I intasbeginbegin trybegin tranif not exists (select 1 from testlock1 with (xlock, rowlock) where id = @ I) begin insert into testlock1 values (@ I, GETDATE (), @ spid); -- insert a column of reply IDendcommitend trybegin catchrollbackend catchend

Continue to test with sqlquerystress. There are still 30 threads, and each thread is inserted 2000 times in a loop.

Use this script again to query

select COUNT(1),id,Createdate from testlock1group by id,Createdatehaving(COUNT(1))>1

There are two duplicates, so let's take a look at the session IDs of the two duplicates.

As expected !!!

Different session inserts, which explains why the row-level exclusive lock is applied during the judgment, but the record cannot be locked.

During Concurrent Insertion, because each session obtains the detection records in the database and inserts the records if the database does not exist, the possible duplicate values of each session are ignored.

If it is a unique index, sessions also need to wait to ensure the uniqueness of the index.

This explains the reason why we recommend that you use the row-level exclusive locks to lock a row of records.