Causing an artificial infinite deadlock on MSSQL?



  • Is there a way of causing an artificial infinite deadlock on MSSQL? I need to cause a deadlock so I can test out some stored procedure to automatically remove the deadlock. Is there a way to do this?



  • Brendan McCaffrey is right that, under normal circumstances, SQL Server will identify and resolve deadlocks automatically.

    There are sometimes bugs that result in undetected deadlocks. The conventional advice on dealing with those would be to apply available updates to SQL Server, and if the problem persists to open a ticket with Microsoft about it.

    If you really have problems with undetected (infinite) deadlocks in your environment, and need a way to create the situation so you can test your programmatic approach to killing them, Joe Obbish has a good way of creating one here:

    https://www.erikdarlingdata.com/sql-server/unkillable-sessions-and-undetected-deadlocks/

    A summary of that approach is to run a normal query that will take and hold some locks, and then run an OPENROWSET query in the same session that requests locks on the same object(s). This results in a strange circular dependency that won't resolve on its own, and SQL Server doesn't detect as a deadlock.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2