SQL Server – Locking – Isolation Levels in Upsert Statements

Background

We are experiencing quite a bit of deadlocks and blocking and so took to the Net to find pointers on how to deal with these perplexing problem.

 

Supporting Objects

sqlServerInternals.vw_TransactionType

 


if schema_id('sqlServerInternals') is null
begin

	exec('create schema [sqlServerInternals] authorization [dbo] ')

end
go

if object_id('[sqlServerInternals].[vw_TransactionType]') is null
begin

	exec
		( 'create view [sqlServerInternals].[vw_TransactionType]
			 as	
				select 1/0 as [shell]
		  '
		)
end
go



alter view [sqlServerInternals].[vw_TransactionType]
as
		select 1 as [id], 'Read/write transaction' as [literal]
		union
		select 2 as [id], 'Read-only transaction' as [literal]
		union
		select 3 as [id], 'System transaction' as [literal]
		union
		select 4 as [id], ' Distributed transaction' as [literal]


go

grant select on [sqlServerInternals].[vw_TransactionType] to [public]
go

 

sqlServerInternals.vw_TransactionState

 



if schema_id('sqlServerInternals') is null
begin

	exec('create schema [sqlServerInternals] authorization [dbo] ')

end
go

if object_id('[sqlServerInternals].[vw_TransactionState]') is null
begin

	exec
		( 'create view [sqlServerInternals].[vw_TransactionState]
			 as	
				select 1/0 as [shell]
		  '
		)
end
go

/*
	https://msdn.microsoft.com/en-us/library/ms174302.aspx
*/

alter view [sqlServerInternals].[vw_TransactionState]
as
		select 0 as [id], 'The transaction has not been completely initialized yet.' as [literal]
		union
		select 1 as [id], 'The transaction has been initialized but has not started'
		union
		select 2 as [id], 'The transaction is active'
		union
		select 3 as [id], 'The transaction has ended. This is used for read-only transactions' 
		union
		select 4 as [id], 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
		union
		select 5 as [id], 'The transaction is in a prepared state and waiting resolution' 
		union
		select 6 as [id], 'The transaction has been committed' 
		union
		select 7 as [id], 'The transaction is being rolled back.' 
		union
		select 8 as [id], 'The transaction has been rolled back'

go

grant select on [sqlServerInternals].[vw_TransactionState] to [public]
go

 

sqlServerInternals.vw_TransactionIsolationLevel

 

if schema_id('sqlServerInternals') is null
begin

    exec('create schema [sqlServerInternals] authorization [dbo] ')

end
go

if object_id('[sqlServerInternals].[vw_TransactionIsolationLevel]') is null
begin

	exec
		( 'create view [sqlServerInternals].[vw_TransactionIsolationLevel]
			 as	
				select 1/0 as [shell]
		  '
		)
end
go

alter view [sqlServerInternals].[vw_TransactionIsolationLevel]
as
    select 0 as [id], 'Unspecified' as [literal]
    union
    select 1 as [id], 'ReadUncommitted'
    union
    select 2 as [id], 'ReadCommitted'
    union
    select 3 as [id], 'Repeatable' 
    union
    select 4 as [id], 'Serializable'
    union
    select 5 as [id], 'Snapshot' 

go

grant select on [sqlServerInternals].[vw_TransactionIsolationLevel] to [public]
go

Scenario

 


   if exists
        (
            select 1
            from   dbo.tblStudent tblS
            where  tblS.studentID = @studentID
       )
   begin
       update tblS
       set    firstname = @firstname
       from   dbo.tblStudent tblS
       where  tblS.[studentID] = @studentID

   end 
   else
   begin

     insert into dbo.tblStudent
     ( studentID, firstname)
     values
     ( @studentID, @firstName)


   end

Instrumentation

Here is a detection query



set nocount on;

	
select 
         [src] = 'sys.dm_tran_locks'
	
       , [sessionID]
		=tblSDTL.REQUEST_SESSION_ID

	, vwTIL.[literal]

        , [database] 
		= db_name(tblSDTL.resource_database_id)	

	, [schemaName]
	     = object_schema_name(
				     tblSP.object_id
				   , tblSDTL.resource_database_id
				 )

	, objectName
   	     = object_name(
			      tblSP.object_id
			    , tblSDTL.resource_database_id
			   )

	   ,  tblDTAT.name

	   , [indexID]
			= tblSI.index_id

		, [index]
			= tblSI.name

	   , tblSDTL.resource_type

	   , tblSDTL.request_mode

	   , tblSDTL.request_type


	   , [sqlStatement]
		 =
		  max(SUBSTRING
		  (
		  	   tblDESTR.[text]
			, (tblDESRR.[statement_start_offset] /2)+1
			,
			(
			  (
			   CASE tblDESRR.[statement_end_offset]               
			     WHEN -1 THEN DATALENGTH(tblDESTR.[text])        
			     ELSE (tblDESRR.[statement_end_offset] - tblDESRR.[statement_start_offset])/2           
			   END
			  )
			 ) + 1
			)
                     )

	   , [count]
			= count(*)

from   sys.dm_tran_locks tblSDTL

		INNER JOIN SYS.PARTITIONS tblSP

			ON tblSDTL.RESOURCE_ASSOCIATED_ENTITY_ID = tblSP.HOBT_ID

		INNER JOIN SYS.INDEXES tblSI

			ON  tblSP.Object_id = tblSI.Object_id
			AND tblSP.Index_id = tblSI.Index_id

		LEFT OUTER JOIN SYS.DM_EXEC_SESSIONS tblDESR 

			ON tblSDTL.REQUEST_SESSION_ID = tblDESR.SESSION_ID

		LEFT OUTER JOIN sys.dm_exec_requests tblDESRR

			on tblDESR.SESSION_ID = tblDESRR.SESSION_ID

		LEFT OUTER JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS tblDTST

			ON tblDESR.SESSION_ID = tblDTST.SESSION_ID

		LEFT OUTER JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS tblDTAT

			ON tblDTST.TRANSACTION_ID = tblDTAT.TRANSACTION_ID

		LEFT OUTER JOIN [sqlServerInternals].[vw_TransactionIsolationLevel] vwTIL

			on tblDESR.[transaction_isolation_level] = vwTIL.[id]


		OUTER APPLY sys.dm_exec_sql_text(tblDESRR.sql_handle) tblDESTR

group by
	    
		  tblSDTL.REQUEST_SESSION_ID

		, vwTIL.[literal]

	   , db_name(tblSDTL.resource_database_id)	

	   , object_schema_name(
				  tblSP.object_id
				, tblSDTL.resource_database_id
			       )

	   , object_name(
			     tblSP.object_id
			   , tblSDTL.resource_database_id
			 )
	   ,  tblDTAT.name

	   , tblSI.index_id
	   , tblSI.name

	   , tblSDTL.resource_type

	   , tblSDTL.request_mode

	   , tblSDTL.request_type


order by

		  tblSDTL.REQUEST_SESSION_ID

		, db_name(tblSDTL.resource_database_id)	

		, object_schema_name(
			  	        tblSP.object_id
				      , tblSDTL.resource_database_id
				   )

	   , 
		 object_name(
			          tblSP.object_id
				, tblSDTL.resource_database_id
			   )

	   , tblSI.index_id

	   , tblSI.name

	   , 
		case
			when tblSDTL.request_mode like 'RangeX-X' then 1000
			when tblSDTL.request_mode like 'RangeI-N' then 999
			when tblSDTL.request_mode like 'RangeS-U' then 998
			when tblSDTL.request_mode like 'RangeS-S' then 997
			when tblSDTL.request_mode in ('IX', 'X') then 90
			else 0
		end desc

	   , 
		case
			when tblSDTL.resource_type = 'PAGE' then 10
			when tblSDTL.resource_type = 'KEY' then 20
			else 0
		end desc



Observation

Read Committed

Inserts

ReadCommitted_Inserts_v3

 

Updates

ReadCommitted_Updates_v2

Findings

Serializable

Inserts

Serializable-Inserts_v2

 

Updates

Serializable-Updates-v2

 

Interpretation

  1. Inserts
    • For both Read Committed and Serializable, Inserts take out more locks than updates
  2. Updates
    • Same type of locks for Read Committed and Serializable
  3. Serializable
    • Inserts
      • Serializable take out Range locks; i.e. RangeX-X and RangeS-S on the Key
      • Range Locks are taken out in-place of record level locks has there no current records to lock
        • Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement. The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction. A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.
        • Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.
          A key-range lock is placed on an index, specifying a beginning and ending key value. This lock blocks any attempt to insert, update, or delete any row with a key value that falls in the range because those operations would first have to acquire a lock on the index. For example, a serializable transaction could issue a SELECT statement that reads all rows whose key values are between ‘AAA’ and ‘CZZ’. A key-range lock on the key values in the range from ‘AAA’ to ‘CZZ’ prevents other transactions from inserting rows with key values anywhere in that range, such as ‘ADG’, ‘BBD’, or ‘CAL’.
  4. Indexes
    • PAGE and Key locks taken out on indexes
    • For Inserts
      • All Indexes
    • For Updates
      • Impacted indexes – Columns referenced/updated in the query

Locking Types

Key-Range Locking

Mode Range Row Description
Range
RangeS-S RangeS S Shared range, shared resource lock; serializable range scan.
RangeS-SU RangeS Shared range, update resource lock; serializable update scan.
RangeI-N RangeI Null Insert range, null resource lock; used to test ranges before inserting a new key into an index.
RangeX-X RangeX X Exclusive range, exclusive resource lock; used when updating a key in a range.

 

 

References

Database Systems

 

Locking Type

 

Blogs

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