0

I was analyzing a deadlock that I received when I noticed that one of the processes involved in the deadlock had the isolation level set to Read Uncommitted.

I search the web... and searched in my app for code/procedures that would set the solation level to read uncommitted or for queries in the procedures involved that had the with(nolock) hint... couldn't find anything...

The only thing I'm questionning is the isolation level when in an explicit transaction... Is it possible that the isolation level is automatically set to read uncommitted if selects, inserts and updates are done within a transaction before they are committed (when in an explicit transaction)?

If not what else can change the isolation level to read uncommitted?

Here's the details of the deadlock (I've masked the details)

Notice 2 things that don't make sence to me:

1- The isolation level Read uncommitted in the 1st process
2- The page lock and object lock in the ressource-list are on different tables
<deadlock>
  <victim-list>
    <victimProcess id="process21bbccd7c28" />
  </victim-list>
  <process-list>
    <process id="process21bbccd7c28" taskpriority="0" logused="0" waitresource="PAGE: 5:1:2115479 " waittime="3584" ownerId="326918835" transactionname="user_transaction" lasttranstarted="2019-06-28T05:10:45.263" XDES="0x21b68ca8040" lockMode="U" schedulerid="3" kpid="23468" status="suspended" spid="193" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-06-28T05:10:45.263" lastbatchcompleted="2019-06-28T05:10:45.263" lastattention="1900-01-01T00:00:00.263" clientapp="process1.exe" hostname="HOST" hostpid="39316" loginname="HOSTsa" isolationlevel="read uncommitted (1)" xactid="0" currentdb="5" currentdbname="DB1" lockTimeout="1200000" clientoption1="671088672" clientoption2="128568">
      <executionStack>
        <frame procname="unknown" queryhash="0x440f75bca6fdf0b5" queryplanhash="0x891bd85a341e0605" line="1" stmtstart="86" stmtend="320" sqlhandle="0x02000000e2435f0dc366de10f4d674434ad7779771138ace0000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="filtered" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
filtered    </frame>
        <frame procname="c442becc-1350-4cff-aa54-18e2b3469c64.dbo.PROCEDURE1" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="79" stmtstart="5764" stmtend="5876" sqlhandle="0x030005002bfdf279f26d7b0079aa000001000000000000000000000000000000000000000000000000000000">
Exec sp_executesql @sql, @PARAM1, @PARAM2,@PARAM3    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 2045967659]   </inputbuf>
    </process>
    <process id="process21bb7bc5088" taskpriority="-10" logused="400" waitresource="OBJECT: 5:679673469:0 " waittime="5133" ownerId="326920117" transactionname="user_transaction" lasttranstarted="2019-06-28T05:10:46.327" XDES="0x21bcae60428" lockMode="IX" schedulerid="1" kpid="51464" status="suspended" spid="162" sbid="0" ecid="0" priority="10" trancount="2" lastbatchstarted="2019-06-28T05:10:46.327" lastbatchcompleted="2019-06-28T05:10:46.327" lastattention="1900-01-01T00:00:00.327" clientapp="PROCESS2.exe" hostname="WEB" hostpid="2828" loginname="WEBSA" isolationlevel="read committed (2)" xactid="326920117" currentdb="5" currentdbname="DB1" lockTimeout="10000" clientoption1="671088672" clientoption2="128568">
      <executionStack>
        <frame procname="c442becc-1350-4cff-aa54-18e2b3469c64.dbo.PROCEDURE2" queryhash="0xdd161b43efe99ea0" queryplanhash="0x1a94ca740c8f6c50" line="212" stmtstart="14062" stmtend="14988" sqlhandle="0x0300050081d6ab7f176e7b0079aa000001000000000000000000000000000000000000000000000000000000">
INSERT INTO TABLE1 (FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FFIELD7,FFIELD8,FIELD9,FIELD10,FIELD11,FIELD12,FIELD13,FIELD14,FIELD15) SELECT
                    @FIELD1 AS FIELD1,
                    @FIELD2 AS FIELD2,
                    @FIELD3 AS FIELD3,
                    @FIELD4 AS FIELD4,
                    @FIELD5 AS FIELD5,
                    @FIELD6 AS FIELD6,
                    @FIELD7 AS FIELD7,
                    @FIELD8 AS FIELD8,
                    0 AS FIELD9,
                    0 AS FFIELD10,
                    GETDATE() AS FIELD11,
                    @FIELD12 AS FIELD12,
                    @FIELD13 AS FIELD13,
                    0 AS FIELD14,
                    NULL AS FIELD15    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 5 Object Id = 2141968001]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="2115479" dbid="5" subresource="FULL" objectname="c442becc-1350-4cff-aa54-18e2b3469c64.dbo.TABLE2" id="lock2194f7b6c80" mode="IX" associatedObjectId="72058845565091840">
      <owner-list>
        <owner id="process21bb7bc5088" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="process21bbccd7c28" mode="U" requestType="wait" />
      </waiter-list>
    </pagelock>
    <objectlock lockPartition="0" objid="679673469" subresource="FULL" dbid="5" objectname="c442becc-1350-4cff-aa54-18e2b3469c64.dbo.TABLE1" id="lock21abf691100" mode="X" associatedObjectId="679673469">
      <owner-list>
        <owner id="process21bbccd7c28" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process21bb7bc5088" mode="IX" requestType="wait" />
      </waiter-list>
    </objectlock>
  </resource-list>
</deadlock>
  • 1
    Unlikely. Maybe the connection settings have something set? – Randolph West Jul 4 at 18:52
  • Entity Framework (and I'm sure other ORM's) will sometimes do wonky things with their connection strings and transactions. The client must specifically request the transaction level. If you caught it once, I bet you can find out pretty quick by running an XE. – Jonathan Fite Jul 4 at 19:04
  • Why would a process using the read uncommitted isolation level, which should neither block on nor hold any locks, be involved in a deadlock? – mustaccio Jul 4 at 19:58
  • @mustaccio they still take schema stability locks. – Erik Darling Jul 4 at 20:37
  • @ErikDarling ...which should not cause deadlocks, should they. – mustaccio Jul 5 at 0:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.