SQL Server – Deadlock – Range I-N to Range S-S — Convert Lock

Background

Thankfully, our Architect logs all Application Errors.  Occasionally, I will review the errors and see if there is a pattern.

Let us quickly go over one Range I-N requestType=convert.

 

Error Log

Screen Shots

Waiter and Owner

Screenshot

Waiter-Mode=RangeI-N RequestType=Convert

Explanation

  1. The waiter is mode=RangeI-N requestType=convert
  2. Owner mode=RangeS-S

 

Deadlock List

Screen Shot

DeadlockList

 

Explanation

  1. Process List
    • SPID 116
    • SPID 79

 

Process ID

Screen Shot

ProcessID

Textual


Date		1/19/2016 7:09:35 AM
Log		SQL Server (Current - 1/15/2016 11:03:00 AM)

Source		spid20s

Message
process id=process7309828 taskpriority=0 logused=696 waitresource=KEY: 7:72057594080329728 (3105a22072fc) waittime=2979 ownerId=4927195156 transactionname=user_transaction lasttranstarted=2016-01-19T07:09:32.660 XDES=0x11380a370 lockMode=RangeI-N schedulerid=9 kpid=8724 status=suspended spid=79 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2016-01-19T07:09:32.660 lastbatchcompleted=2016-01-19T07:09:32.660 clientapp=.Net SqlClient Data Provider hostname=mywebapp hostpid=4648 loginname=appUser isolationlevel=serializable (4) xactid=4927195156 currentdb=7 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056


Explanation

  1. Confirmed the Hostname
  2. And, the loginname
  3. The Isolation Level, Serializable, is revealing
  4. And, is the LockMode Range I-N

 

DB Objects

 

Stored Procedure


SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @ErrMsg nvarchar(4000)
DECLARE @ErrSeverity int

BEGIN TRY

	BEGIN TRANSACTION -- Start the transaction		
	insert into [Products].[ProductSurveyFields]
	(
  	    [userID]
	  , [refID]
	  , [uniqueID]
	  , [docID]
	  , [fieldID]
	  , [value]
	)
   	values
	(
		  @userID
		, @refID
		, @uniqueID
		, @docID
		, @fieldID
		, @Value
	)

	-- done with everything
	COMMIT TRANSACTION
		
				
END TRY
BEGIN CATCH			

	IF @@TRANCOUNT > 0
	begin
	  ROLLBACK
	end;

	/*
	 Raise an error with the details of the exception
	*/				
	SELECT
	        @ErrMsg = ERROR_MESSAGE()
	      , @ErrSeverity = ERROR_SEVERITY()

	RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

 

Table Indexes

Code

exec sp_helpindex '[Products].[ProductSurveyFields]'

 

Output:

Index

 

Consideration

From a quick study, we can tell the following

  1. Table Structure
    • Our table is a not a heap as it has a Clustered Index
    • We have a lone index and so we are not experiencing Index Contention
  2. Isolation Level
    • Our default Transaction Isolation level is Serializable
  3. Is Deadlock Intra Query
    • Our deadlock involves two distinct SQL Server Sessions as we have two SPIDs ( 116 and 79 )
  4. Is Query an UPSERT ( a query that involves a seek to determine whether Operation should be INSERT or UPDATE )
    • If so, work is more complicated

 

Revision

Isolation Level – Serializable

The only change we will make at this time is to relax the isolation level. Microsoft .Net’s default Isolation Level is Serializable.

Serializable prevents concurrent inserts as it demands that changes can not happen; this mandate applies to changes that does not even materially affect the change that is being made.

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Rules
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

 

Explanation

The problematic and unpredictable area is that other SQL Statements might be trying to add records that fall into the same key range.

Isolation Level – Read Committed

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Rules
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

Explanation

  1. We are not made aware of changes by by others
  2. We are able insertion new rows to our heart’s content

Stored Procedure

Here is our revision.


SET NOCOUNT ON;
SET XACT_ABORT ON;
--effect new Isolation Level
set transaction isolation level read committed;

DECLARE @ErrMsg nvarchar(4000)
DECLARE @ErrSeverity int

BEGIN TRY

	BEGIN TRANSACTION -- Start the transaction		
	insert into [Products].[ProductSurveyFields]
	(
  	    [userID]
	  , [refID]
	  , [uniqueID]
	  , [docID]
	  , [fieldID]
	  , [value]
	)
   	values
	(
		  @userID
		, @refID
		, @uniqueID
		, @docID
		, @fieldID
		, @Value
	)

	-- done with everything
	COMMIT TRANSACTION
		
				
END TRY
BEGIN CATCH			

	IF @@TRANCOUNT > 0
	begin
	  ROLLBACK
	end;

	/*
	 Raise an error with the details of the exception
	*/				
	SELECT
	        @ErrMsg = ERROR_MESSAGE()
	      , @ErrSeverity = ERROR_SEVERITY()

	RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

Lock Types

Range Locks

Range I-N & Range S-S

The dead giveaways here are that the Locks are prefixed with the Keyword Range.

Key Range Locking

Lock Type Lock Explanation
Range I-N The RangeI-N mode key-range lock is placed on the index entry corresponding to the value of the Key being inserted.

This request tests the the range. If the lock is granted, the data is inserted and an exclusive (X) lock is placed on the Key Range.

The RangeI-N mode key-range lock is necessary only to test the range and is not held for the duration of the transaction performing the insert operation. Other transactions can insert or delete values before or after the inserted value.

However, any transaction attempting to read, insert, or delete the value Key value will be locked until the inserting transaction either commits or rolls back.

Range S-S Shared range, shared resource lock; serializable range scan.

 

References

Range Locks

  1. Sunil Agarwal – SQL Server Database Engine Blog – Range Locks
  2. Vitaliy Popovych, Software Developer at Intapp – SQL Saturday 406 – Key Range Locking
  3. kalen Delaney & Jonathan Kehayias – SQLskills.com – The deadlock between RangeS-S and RangeI-N

UPSERT Statements

  1. Mark S. Rasmussen – Solving Deadlocks Through Locking Hints

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s