0

On a table that is heavily used with reads and writes, a lot of deadlocks are happening. Therefore I am experimenting with snapshot isolation. As I understood, reads shouldn't block writes and writes schouldn't block reads. Unfortunately, there are still deadlocks with those operations as shown below.

Am I wrong with my expectation, that those deadlocks should not happen with snapshot isolation?

<deadlock-list>
 <deadlock victim="processe587a9088">
  <process-list>
   <process id="processe587a9088" taskpriority="0" logused="0" waitresource="KEY: 11:72057594050904064 (d6a2e023f14e)" waittime="2359" ownerId="1218373" transactionname="SELECT" lasttranstarted="2022-11-18T13:58:58.453" XDES="0xe75e35c60" lockMode="RangeS-S" schedulerid="15" kpid="636" status="suspended" spid="65" sbid="0" ecid="13" priority="0" trancount="0" lastbatchstarted="2022-11-18T13:58:58.453" lastbatchcompleted="2022-11-18T13:58:58.453" lastattention="1900-01-01T00:00:00.453" clientapp="Core Microsoft SqlClient Data Provider" hostname="SUN" hostpid="40632" isolationlevel="serializable (4)" xactid="1218373" currentdb="11" currentdbname="-rocket-author" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="170" stmtend="1324" sqlhandle="0x0200000057d81707bbdc50a55ae52cfd91db4d2b8ab0279f0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@__result_TransactionId_0 uniqueidentifier,@__destinationInstanceKey_1 nvarchar(50))SELECT [c].[Id], [c].[EntityId], [c].[EntityType], [c].[Error], [c].[FailureCount], [c].[InstanceKey], [c].[ReplicationScheduleOptions], [c].[ReplicationType], [c].[ScheduledAt], [c].[ScheduledById], [c].[Status], [c].[TransactionId], [c].[Metadata_ModifiedAt], [c].[Metadata_ModifiedById], [c].[Metadata_PublishedAt], [c].[Metadata_PublishedById], [c].[Metadata_PublishedStage]
FROM [ReplicationSchedule] AS [c]
WHERE ([c].[TransactionId] = @__result_TransactionId_0) AND ([c].[InstanceKey] = @__destinationInstanceKey_1)    </inputbuf>
   </process>
   <process id="processe00051088" taskpriority="0" logused="1236" waitresource="KEY: 11:72057594050904064 (8a80a45c8553)" waittime="2336" ownerId="1216957" transactionname="user_transaction" lasttranstarted="2022-11-18T13:58:58.123" XDES="0xe00188428" lockMode="X" schedulerid="1" kpid="2044" status="suspended" spid="83" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-11-18T13:58:58.350" lastbatchcompleted="2022-11-18T13:58:58.347" lastattention="1900-01-01T00:00:00.347" clientapp="Core Microsoft SqlClient Data Provider" hostname="SUN" hostpid="40632" loginname="sa" isolationlevel="read committed (2)" xactid="1216957" currentdb="11" currentdbname="-rocket-author" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="6" stmtstart="938" stmtend="1240" sqlhandle="0x0200000045b2f113b74c9df2726146dd46cbeb9155dda3010000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p3 uniqueidentifier,@p0 int,@p1 datetimeoffset(7),@p2 uniqueidentifier,@p7 uniqueidentifier,@p4 int,@p5 datetimeoffset(7),@p6 uniqueidentifier,@p11 uniqueidentifier,@p8 datetimeoffset(7),@p9 uniqueidentifier,@p10 nvarchar(4000),@p13 uniqueidentifier,@p12 datetimeoffset(7))SET NOCOUNT ON;
UPDATE [ReplicationSchedule] SET [Status] = @p0, [Metadata_ModifiedAt] = @p1, [Metadata_ModifiedById] = @p2
WHERE [Id] = @p3;
SELECT @@ROWCOUNT;

UPDATE [ReplicationSchedule] SET [Status] = @p4, [Metadata_ModifiedAt] = @p5, [Metadata_ModifiedById] = @p6
WHERE [Id] = @p7;
SELECT @@ROWCOUNT;

UPDATE [GummyBearPack] SET [Metadata_PublishedAt] = @p8, [Metadata_PublishedById] = @p9, [Metadata_PublishedStage] = @p10
WHERE [Id] = @p11;
SELECT @@ROWCOUNT;

UPDATE [GummyBearProductionCharge] SET [Metadata_PublishedAt] = @p12
WHERE [Id] = @p13;
SELECT @@ROWCOUNT;

    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594050904064" dbid="11" objectname="-rocket-author.dbo.ReplicationSchedule" indexname="PK_ReplicationSchedule" id="locke61bad100" mode="X" associatedObjectId="72057594050904064">
    <owner-list>
     <owner id="processe00051088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processe587a9088" mode="RangeS-S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594050904064" dbid="11" objectname="-rocket-author.dbo.ReplicationSchedule" indexname="PK_ReplicationSchedule" id="locke106de100" mode="RangeS-U" associatedObjectId="72057594050904064">
    <owner-list>
     <owner id="processe587a9088" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processe00051088" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

0

1 Answer 1

3

The snapshot database options are not relevant to the deadlock in your question.

The SELECT query session is using the SERIALIZABLE transaction isolation level (as shown by isolationlevel="serializable (4) in the process xml element), which is especially prone to deadlocks because of the more restrictive locking. SERIALIZABLE does not use row versioning and is unaffected by the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options. Note that, as @DavidBrowne-Microsoft mentioned in comments, .NET applications that use TransactionScope sometimes use SERIALIZABLE inadvertently since that's the default unless another isolation level is specified.

The UPDATE query session is using READ COMMITTED so read-only queries on that session will use row versioning because of the READ_COMMITTED_SNAPSHOT ON database setting. However, data modifications still use locking for transactional integrity. Below is the excerpt from the documentation:

Important

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. Additionally, an update made at the READ_COMMITTED isolation level uses update locks on the data rows selected, whereas an update made at the SNAPSHOT isolation level uses row versions to select rows to update. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

This deadlock will be avoided if you change the SELECT query session to use READ COMMITTED or SNAPSHOT instead of SERIALIZABLE. With all sessions using an isolation level implemented with row versioning, readers will not block or deadlock with writers and visa-versa. Only writer/writer blocks/deadlocks can occur.

The ALLOW_SNAPSHOT_ISOLATION database option does not need to be set unless you plan to use the SNAPSHOT transaction isolation level.

1
  • @user2900970, I updated my answer for your first question. For existing applications I'm not intimately familiar with, my personal preference is to opt-in for SNAPSHOT isolation for read-only queries and leave READ_COMMITTED_SNAPSHOT OFF to preserve pessimistic locking behavior of read/write transactions.
    – Dan Guzman
    Nov 20, 2022 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.