Locks in SQL Server insert operations

This blog post briefly describes the locks used in an INSERT statement in SQL Server.

Preparing Data

First we create a table table_1, which has two column IDs (bigint) and value (varchar), where the ID establishes the primary key.

CREATE TABLE [dbo].[table_2](    [Id] [bigint]  not NULL,    [Value] [nchar](Ten)NULL, CONSTRAINT [pk_table_2] PRIMARY KEY CLUSTERED (    [Id] ASC) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY])  on [PRIMARY]

View Code

Then insert two data.

Insert  into dbo.table_2 (ID, value) Values (1'1'), (2'2' );

Start testing

We know that a shared lock is released at the end of a query statement in transaction, while an exclusive lock is released at transaction commit. We can use it to execute an INSERT, do not commit transaction, and then go to the status of the lock. Note that the transaction isolation level of the Query window configuration in this article is the default value of Read COMMITTED.

First execute the following sql:

begin Tran T1 Insert  into dbo.table_2 (ID, value) Values (3'3');

Then look at the lock:

SELECT      Resource_type,    request_mode,    resource_description,    request_session_id,    request_status ,    resource_associated_entity_id,    db_name(resource_database_id) as resource _databasefrom    sys.dm_tran_locksWHERE<>      'DATABASE'ORDERby    request_session_id;

The results of the implementation are as follows:

• The first one is the intent exclusive lock. It means that there is an exclusive lock (or a third exclusive lock) under this data page, and we find that it has the same resource_associated_entity_id as a third lock. So, this data page is the primary key that holds the data for this row.
• The second one is also intent exclusive. Its resource_type is object, which can be a data table, view, stored procedure, extended stored procedure, or any object that has an object ID. Its resource_associated_entity_id this column is actually the OBJECT_ID, uses the function object_name (object_id) to look at the discovery result is table_2. Then the exclusive lock that exists underneath it refers to the third lock.
• The third one is an exclusive lock. Resou_description refers to the hash value of the primary key of the inserted data.

Supplement 1

At this point, we execute the following query statement in another command window without blocking:

SELECT *  from dbo. table_2WHERE id=1;

But the other one has a blocking effect:

SELECT *  from dbo. table_2WHERE id=3;

Take a look at the first SQL-generated lock. Since the shared lock is released immediately at the end of the query, we add a holdlock to let it release at the end of the transaction:

begin Tran T2 SELECT *  from  with (HOLDLOCK) WHERE id=1;

This is the case when the above statement lock is executed:

The second SQL is blocked, so you can directly query and then look at the lock situation:

We found that the resource_description and 3rd lines of line 9th are the same, which means that the primary key lock is just a certain value.

Supplement 2

This SQL will also be blocked by insert:

SELECT     valuefrom    dbo. table_2WHERE    value='1'

And looking at the current lock can be found that the key is locked in the value of the INSERT statement is the key value. There are two questions: 1. Why is it useless to the primary key column, but the primary key lock is generated. 2. Why the Insert data is not yet commit, this will generate the lock of the primary key in this row.

Answer: 1. We look at the query plan, we can see that this statement is using a clustered index scan, as for why not a table scan, see here. 2. Because the transaction isolation level is read Committed by default, a shared lock is added to the inserted but uncommitted data primary key.

Locks in SQL Server insert operations