-1

I am running into a problem, when a deadlock occurs, SQL Server 2022 does not choose a victim.

I am using a previous post for the code (How to simulate DEADLOCK on SQL Server?) to create the deadlock condition, but SQL Server 2022 does not choose a victim.

I am using SQL Server 2022: VERSION Microsoft SQL Server 2022 (RTM-CU6) (KB5027505) - 16.0.4055.4 (X64).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John Titus
  • 1
  • Please update your question to explain what you mean by “choose a victim”? – NickW Aug 04 '23 at 19:02
  • If theres no victim how is there a deadlock? Are you sure you haven't just got blocking. – Stu Aug 04 '23 at 19:08
  • Sorry if I am not being clear in what I am trying to portray. – John Titus Aug 04 '23 at 20:57
  • According to Microsoft (https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver16) In a deadlock condition, Session A and Session B are now mutually blocking one another. Neither transaction can proceed, as they each need a resource that is locked by the other. – John Titus Aug 04 '23 at 21:10
  • After a few seconds, the deadlock monitor will identify that the transactions in Session A and Session B are mutually blocking one another, and that neither can make progress. You should see a deadlock occur, with Session A chosen as the deadlock victim. Session B will complete successfully. An error message will appear in Session A with text similar to the following: Msg 1205, Level 13, State 51, Line 7 Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. – John Titus Aug 04 '23 at 21:11
  • Using the Activity Monitor, I can see Session 2 is being blocked by Session 1, so I see a deadlock occur, but there is no chosen deadlock victim, and I don't see an error message being displayed. I am wondering if others have seen similar behavior with SQL Server 2022. – John Titus Aug 04 '23 at 21:28
  • Session 2 being blocked by Session 1 is not a deadlock. That is just blocking. It only becomes a deadlock if Session 1 also is blocking Session 2. Then it becomes a circular wait requiring the system to kill one of them – Martin Smith Aug 05 '23 at 12:29
  • @MartinSmith Thank you for the clarification between blocking and a deadlock condition, but referring to a previous post in stack overflow (https://stackoverflow.com/questions/22825147/how-to-simulate-deadlock-on-sql-server) the accepted answer states "You can create a deadlock by using the steps shown below. First, create the global temp tables with sample data."... and shows an example. When I use that example, in SQL Server 2022, I don't see a transaction being aborted and/or error message 1205 sent to the client. The block just continues until Session 1 is manually rolledback. – John Titus Aug 07 '23 at 13:32
  • @Stu and MartinSmith You were both correct what I was doing was just getting a block not a deadlock. Once I understood the concept better I was able to receive the following message using SQL Server 2022: Msg 1205, Level 13, State 45, Line 10 Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Thank you everyone for all of your input. SQL Server 2022 when a deadlock occurs does choose a victim. – John Titus Aug 11 '23 at 20:45
  • @Stu please post "If there's no victim how is there a deadlock? Are you sure you haven't just got blocking." as an answer so I can accept it as the answer. Thank you! – John Titus Aug 11 '23 at 21:17

0 Answers0