10

Upon running sp_whoisactive on a server for the first time, I was greeted by something unexpected:

enter image description here

Two sessions had been running for 13 days but both of them seemed to be blocking each other. Taking a look at sys.dm_tran_locks:

enter image description here

The configuration value for the blocked process threshold setting is 10 seconds. Other deadlocks are being successfully resolved on the server via the deadlock monitor.

Information from the @get_locks parameter:

<Database name="DB1">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="TBL1" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>


<Database name="DB1">
    <Locks>
        <Lock request_mode="S" request_status="GRANT" request_count="1" />
    </Locks>
    <Objects>
        <Object name="TBL2" schema_name="dbo">
            <Locks>
                <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="2" />
            </Locks>
        </Object>
        <Object name="TBL1" schema_name="dbo">
            <Locks>
                <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="5" />
                <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="GRANT" request_count="33701" />
                <Lock resource_type="PAGE" page_type="*" index_name="PK__TBL1__3214EC27326C5B6A" request_mode="U" request_status="WAIT" request_count="1" />
            </Locks>
        </Object>
    </Objects>
</Database>

I've occasionally seen the term "undetected deadlock" but I don't have any direct experience with them. My questions are:

  1. Could this be an example of an undetected deadlock? I can't see how the situation gets resolved without intervention from the deadlock monitor thread, but for some reason that hasn't happened yet.
  2. Is there anything to do other than update to the latest CU and hope the issue doesn't occur again? The server is currently on 2017 CU10 which I know is a bit out of date.
0
2

From a comment left by David Browne - Microsoft

"Undetected deadlocks" usually means "undetectable deadlocks", eg where session is blocked by a SQL Server lock, and the other is blocked in the client application, or where a session calls xp_cmdshell which a runs a program that connects to the database and becomes blocked by the session waiting on the completion of xp_cmdshell. Having an ordinary deadlock not resolved automatically looks like a bug.

Your Answer

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

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