I have a situation where inserting new records into a SQL Server database table from a C# ASP.NET Web API causes deadlocks when hit with multiple threads. This is caused by an initial SELECT taking a shared range lock (RangeS-S) and then when the INSERT happens it is converted to a RangeI-N.
The abbreviated and anonymised deadlock XML is shown below.
<deadlock-list>
<deadlock victim="process19d91c28">
<process-list>
<process id="process19d91c28" taskpriority="0" logused="372" waitresource="KEY: 6:72057594044416000 (ffffffffffff)" waittime="3914" ownerId="1072531" transactionname="user_transaction" lasttranstarted="2015-03-27T15:41:26.670" XDES="0x5faad0d0" lockMode="RangeI-N" schedulerid="3" kpid="4300" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-03-27T15:41:26.693" lastbatchcompleted="2015-03-27T15:41:26.693" lastattention="1900-01-01T00:00:00.693" clientapp=".Net SqlClient Data Provider" hostname="MYHOST" hostpid="17300" loginname="MYDOMAIN\XYZ_DB_DEV" isolationlevel="serializable (4)" xactid="1072531" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="XYZ_Local.dbo.Article_Insert" line="20" stmtstart="1100" stmtend="2372" sqlhandle="0x0300060049c6306a7d37c200b0a3000001000000000000000000000000000000000000000000000000000000">
INSERT INTO Article (
-- ABREVIATED
) VALUES (
-- ABREVIATED
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 1781581385]
</inputbuf>
</process>
<process id="process3bd3f468" taskpriority="0" logused="372" waitresource="KEY: 6:72057594044416000 (ffffffffffff)" waittime="3916" ownerId="1072527" transactionname="user_transaction" lasttranstarted="2015-03-27T15:41:26.663" XDES="0x5faa73f0" lockMode="RangeI-N" schedulerid="2" kpid="3240" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-03-27T15:41:26.690" lastbatchcompleted="2015-03-27T15:41:26.690" lastattention="1900-01-01T00:00:00.690" clientapp=".Net SqlClient Data Provider" hostname="MYHOST" hostpid="17300" loginname="MYDOMAIN\XYZ_DB_DEV" isolationlevel="serializable (4)" xactid="1072527" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="XYZ_Local.dbo.Article_Insert" line="20" stmtstart="1100" stmtend="2372" sqlhandle="0x0300060049c6306a7d37c200b0a3000001000000000000000000000000000000000000000000000000000000">
INSERT INTO Article (
-- ABREVIATED
) VALUES (
-- ABREVIATED
</frame>
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 1781581385]
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594044416000" dbid="6" objectname="XYZ_Local.dbo.Article" indexname="IX_Article_THEINDEX" id="lock43227700" mode="RangeS-S" associatedObjectId="72057594044416000">
<owner-list>
<owner id="process3bd3f468" mode="RangeS-S"/>
<owner id="process3bd3f468" mode="RangeI-N" requestType="convert"/>
</owner-list>
<waiter-list>
<waiter id="process19d91c28" mode="RangeI-N" requestType="convert"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594044416000" dbid="6" objectname="XYZ_Local.dbo.Article" indexname="IX_Article_THEINDEX" id="lock43227700" mode="RangeS-S" associatedObjectId="72057594044416000">
<owner-list>
<owner id="process19d91c28" mode="RangeS-S"/>
<owner id="process19d91c28" mode="RangeI-N" requestType="convert"/>
</owner-list>
<waiter-list>
<waiter id="process3bd3f468" mode="RangeI-N" requestType="convert"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Adding WITH (UPDLOCK) to the initial SELECT 'solves' the deadlock but its not very pretty and effectively is the same as a lock() in the code as it blocks all other threads for the duration of the transaction.
Adding SET TRANSACTION ISOLATION LEVEL REPEATABLE READ to the Article_Insert SPROC instead also seems to solve the deadlock.
I see from this XML, that the isolation level is Serializable and I am confused as to how this is the case. When I run this code.
SELECT *
FROM [Ecs_Local].[dbo].[Article]
WHERE title ='jcp001'
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID
The response is ReadCommitted, so the default on the SQL Server instance is ReadCommitted.
When I build the transaction in the c# code I use a utility that does this
_transaction = _connection.BeginTransaction(IsolationLevel.Serializable);
I assume this sets the default isolation level for the entire transaction to Serializable.
I have looked at restructuring the code to do the initial select outside the transaction, but that is not straight forward and will not necessarily solve other areas of the code that may also suffer from this problem.
What I'd like to understand is what is the best way to solve this:
READ COMMITTED instead (This would then apply to every transaction in my app) and what the potential problems are with this (I don't understand the phantom reads issue)REPEATABLE READEDIT 1 As requested by Bogdan here is the anonomised execution plan
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementId="1" StatementText="Article_Insert" StatementType="EXECUTE PROC" RetrievedFromCache="false" />
</Statements>
<Statements>
<StmtSimple>
<StoredProc ProcName="Article_Insert">
<Statements>
<StmtSimple StatementCompId="2" StatementId="2" StatementText="CREATE PROCEDURE [dbo].[Article_Insert]
 @CurrentUser NVARCHAR(1000),
 @xyzJDocId BIGINT,
 @xyzZDocId BIGINT,
 @ZDocNumber NVARCHAR(255) = NULL,
 @InstutionType NVARCHAR(255),
 @InstutionName NVARCHAR(255) = NULL,
 @QWStatus NVARCHAR(255) = NULL,
 @Doi NVARCHAR(32) = NULL,
 @Title NVARCHAR(4000),
 @ArticleType NVARCHAR(255),
 @Active BIT,
 @Exempt BIT,
 @NewDocRequired BIT
AS
BEGIN
 -- ThirdPartyContent Abc -> xyz only
 -- ConflictOfInterest Abc -> xyz only

 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 " StatementType="SET TRANSACTION ISOLATION LEVEL" RetrievedFromCache="true" />
<StmtSimple StatementCompId="3" StatementEstRows="1" StatementId="3" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.0300044" StatementText="INSERT INTO Article (
 [CreatedDate], 
 [LastModifiedBy],
 [xyzJDocId], 
 [xyzZDocId],
 [ZDocNumber],
 [InstutionType],
 [InstutionName],
 [QWStatus],
 [Doi],
 [Title],
 [ArticleType],
 [Active],
 [Exempt],
 [NewDocRequired]
 ) VALUES (
 GETUTCDATE(),
 @CurrentUser,
 @xyzJDocId,
 @xyzZDocId,
 @ZDocNumber,
 @InstutionType,
 @InstutionName,
 @QWStatus,
 @Doi,
 @Title,
 @ArticleType,
 @Active,
 @Exempt,
 @NewDocRequired
 )" StatementType="INSERT" QueryHash="0x108916B56B42574E" QueryPlanHash="0x73B019D9C6F1A1C5" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="32" CompileTime="1" CompileCPU="1" CompileMemory="176">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="38400" EstimatedPagesCached="9600" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="9" EstimateCPU="3E-06" EstimateIO="0.03" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Insert" EstimatedTotalSubtreeCost="0.0300044">
<OutputList />
<Update DMLRequestSort="false">
<Object Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Index="[PK__tmp_ms_x__3214EC071788A4DB]" IndexKind="Clustered" Storage="RowStore" />
<Object Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Index="[IX_Article_THEINDEX]" IndexKind="NonClustered" Storage="RowStore" />
<Object Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Index="[IX_Article_OTHERINDEX]" IndexKind="NonClustered" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[XYZ_Local].[dbo].[Article].[CreatedDate] = RaiseIfNullInsert([Expr1003]),[XYZ_Local].[dbo].[Article].[LastModifiedBy] = RaiseIfNullInsert([@CurrentUser]),[XYZ_Local].[dbo].[Article].[xyzJDocId] = RaiseIfNullInsert([@xyzJDocId]),[XYZ_Local].[dbo].[Article].[xyzZDocId] = RaiseIfNullInsert([@xyzZDocId]),[XYZ_Local].[dbo].[Article].[ZDocNumber] = [@ZDocNumber],[XYZ_Local].[dbo].[Article].[InstutionType] = [@InstutionType],[XYZ_Local].[dbo].[Article].[InstutionName] = [@InstutionName],[XYZ_Local].[dbo].[Article].[QWStatus] = [@QWStatus],[XYZ_Local].[dbo].[Article].[Doi] = [@Doi],[XYZ_Local].[dbo].[Article].[Title] = RaiseIfNullInsert([@Title]),[XYZ_Local].[dbo].[Article].[ArticleType] = RaiseIfNullInsert([@ArticleType]),[XYZ_Local].[dbo].[Article].[Active] = RaiseIfNullInsert([@Active]),[XYZ_Local].[dbo].[Article].[Exempt] = RaiseIfNullInsert([@Exempt]),[XYZ_Local].[dbo].[Article].[NewDocRequired] = RaiseIfNullInsert([@NewDocRequired]),[XYZ_Local].[dbo].[Article].[Id] = [Expr1002],[XYZ_Local].[dbo].[Article].[ThirdPartyContent] = NULL,[XYZ_Local].[dbo].[Article].[ConflictOfInterest] = NULL">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="CreatedDate" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1003" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="LastModifiedBy" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@CurrentUser" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="xyzJDocId" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@xyzJDocId" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="xyzZDocId" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@xyzZDocId" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="ZDocNumber" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ZDocNumber" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="InstutionType" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="@InstutionType" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="InstutionName" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="@InstutionName" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="QWStatus" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="@QWStatus" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="Doi" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Doi" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="Title" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Title" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="ArticleType" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@ArticleType" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="Active" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Active" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="Exempt" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Exempt" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="NewDocRequired" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@NewDocRequired" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="Id" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1002" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="ThirdPartyContent" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[XYZ_Local]" Schema="[dbo]" Table="[Article]" Column="ConflictOfInterest" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="19" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.357E-06">
<OutputList>
<ColumnReference Column="Expr1002" />
<ColumnReference Column="Expr1003" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="getutcdate()">
<Identifier>
<ColumnReference Column="ConstExpr1004">
<ScalarOperator>
<Intrinsic FunctionName="getutcdate" />
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.257E-06">
<OutputList>
<ColumnReference Column="Expr1002" />
</OutputList>
<ComputeScalar ComputeSequence="true">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="getidentity((880722190),(6),NULL)">
<Intrinsic FunctionName="getidentity">
<ScalarOperator>
<Const ConstValue="(880722190)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(6)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="3" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<ConstantScan />
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@NewDocRequired" ParameterCompiledValue="(0)" />
<ColumnReference Column="@Exempt" ParameterCompiledValue="(0)" />
<ColumnReference Column="@Active" ParameterCompiledValue="(1)" />
<ColumnReference Column="@ArticleType" ParameterCompiledValue="N'Article'" />
<ColumnReference Column="@Title" ParameterCompiledValue="N'jcp001'" />
<ColumnReference Column="@Doi" ParameterCompiledValue="NULL" />
<ColumnReference Column="@QWStatus" ParameterCompiledValue="NULL" />
<ColumnReference Column="@InstutionName" ParameterCompiledValue="NULL" />
<ColumnReference Column="@InstutionType" ParameterCompiledValue="N'Cogent'" />
<ColumnReference Column="@ZDocNumber" ParameterCompiledValue="NULL" />
<ColumnReference Column="@xyzZDocId" ParameterCompiledValue="(2015032810025104)" />
<ColumnReference Column="@xyzJDocId" ParameterCompiledValue="(852)" />
<ColumnReference Column="@CurrentUser" ParameterCompiledValue="N'Abc\Api'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
<StmtSimple StatementCompId="4" StatementId="4" StatementText="

 SELECT SCOPE_IDENTITY() AS [ArticleId]" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="true" />
</Statements>
</StoredProc>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
The S-locks are taken by the select because of the serializable isolation level. Serializable means "as if serial execution" which, in SQL Server, requires all data read to be stabilized.
You don't seem to need that so you probably should downgrade the isolation level.
Snapshot isolation levels are often a great fit for many apps. You get perfect read consistency with zero locking of read data and no blocking on existing locks (speaking about row locks here - please take this statement as approximating information).
Number 3 (using repeatable read) does not help because you can't reduce the locks that DML takes. The isolation level has (pretty much) no effect.
Please let me know what you think. Answering questions such as this one is always hard because the "best" solution depends very much on the specific circumstances. I always recommend SI if applicable because it is a total solution to a broad range of issues.