Strange Deadlock Graph showing multiple DB but isolated

Question

Strange Deadlock Graph showing multiple DB but isolated

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

and I have to query the design of that process - why do you have 2 different VM's trying to delete older rows from dbo.Logs_Epass without any other criteria than timestamp column - this type of deletes would either need a further filtering by process/user or should be done on a central process.

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 2

Johan Bijnens wrote:

Start with avoinding functions in where clauses ! ( needs full ( index ) conversion before actual use )
Declare @RefTs datetime2(0) = dateadd(MM, datediff(MM, 0, GETDATE()) - 3, 0 ) /* match to [TimeStamp] column in table */DELETE TOP (1000) 
FROM dbo.Logs_Epass 
WHERE TimeStamp < @RefTs ;
( parameter folding isn't always working as you may expect it to be )
According to the graph, spid 594 and spid 666 have no relation. I have no explanation for those graphs to be together in the xdl.
-added-
Same goes for the declaration of @P12 with the update statement
/* @p12 nvarchar(450), .... */

UPDATE [dbo].[CREATE_JOB_LOG]
  SET
      [COG_TEMPLATE_DRAFT_PAYLOAD] = @p0
    , [CREATEDBY] = @p1
    , [CREATED_DATE] = @p2
    , [DRAFT_ID] = @p3
    , [DRAFT_PAYLOAD] = @p4
    , [JOB_ID] = @p5
    , [PAYLOAD] = @p6
    , [REQ_NUM] = @p7
    , [RESPONSE] = @p8
    , [Template_ID] = @p9
    , [UPDATEDBY] = @p10
    , [UPDATED_DATE] = @p11
WHERE  [ID] = @p12;       /* is [ID] actually declared nvarchar(450) ??? - Avoid implicite conversions ! */

Thanks Johan, team is working on removing the function.

That is the strange part two different queries from entirely different databases with no relations are showing in the same deadlock graph.

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 3

Jonathan AC Roberts wrote:

From ChatGPT:
Based on the provided XML deadlock graph, there are two victim processes (process4d93468 and process4cfdc28) involved in the deadlock. The deadlock occurred between two DELETE operations on the dbo.Logs_Epass table and an UPDATE operation on the dbo.CREATE_JOB_LOG table.
Here's a breakdown of the deadlock scenario:
1. process4d93468 and process6669468 are attempting to delete rows from the dbo.Logs_Epass table, waiting for locks on pages PAGE: 38:7:7451064 and PAGE: 38:7:7451064 respectively.
2. process4cfdc28 and process1ff90084e8 are trying to update rows in the dbo.CREATE_JOB_LOG table, waiting for locks on page PAGE: 37:3:6847335.
3. process4d93468 holds an exclusive (U) lock on page PAGE: 38:7:7451064 and is waiting for a lock on page PAGE: 37:3:6847335.
4. process4cfdc28 holds an exclusive (U) lock on page PAGE: 37:3:6847335 and is waiting for a lock on page PAGE: 38:7:7451064.
This deadlock scenario arises due to the different order of acquisition of locks by the processes involved. While one set of processes is holding locks on pages required by another set, they are also waiting for locks held by the other set, resulting in a cyclic dependency and deadlock.
To resolve this deadlock, you can consider the following approaches:
1. Optimize Transaction Logic: Review the transaction logic to minimize the duration of transactions and reduce the possibility of deadlocks. Ensure that transactions acquire locks in a consistent order across different processes.
2. Lock Hints: Use appropriate lock hints (such as ROWLOCK, PAGLOCK, TABLOCK, or UPDLOCK) in the SQL statements to control the granularity and mode of locks acquired by transactions.
3. Transaction Isolation Level: Adjust the transaction isolation level to reduce the concurrency issues. Consider using a lower isolation level like READ COMMITTED if it's acceptable for your application's requirements.
4. Batch Processing: Instead of processing large batches of data in a single transaction, consider breaking down the operations into smaller batches. This reduces the likelihood of conflicts and deadlocks.
5. Retry Mechanism: Implement a retry mechanism in the application code to automatically retry transactions that encounter deadlock errors. This can help alleviate the impact of occasional deadlocks.
6. Database Schema Optimization: Analyze the database schema, indexing strategy, and query execution plans to optimize the performance of queries and reduce the likelihood of deadlocks.
By addressing these aspects, you can mitigate deadlock issues and improve the overall stability and performance of the SQL Server database.

Thank you Jonathan but still the mystery of two different databases with no relation is shown in one deadlock graph.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

Just a thought here from reading the XML, but any chance you have a trigger on CREATE_JOB_LOG that writes to the second DB (Logs_Epass)? or I may be reading the XML backwards and we should be looking for triggers on the Logs_Epass.

I see a lot of advice on how to improve the query, but I'm just trying to figure out the cause of the deadlock across 2 DB's.

What I suspect is happening is that when you update CREATE_JOB_LOG, a trigger is firing that is sending something back to Logs_Epass hence how/why you are getting multi-database deadlock.

I would also be a bit cautious of the ChatGPT "resolution". Lock hints I tend to use VERY sparingly as they can cause odd behavior if you are not too familiar with them and in general, SQL Engine usually picks the correct hints in the back end. Adjusting the transaction isolation level changes the behavior of your query too, so I'd be cautious about changing that. Retry mechanism MAY not be what you want, so if you do decide to implement that, make sure it is what you want/need. The other tips are not a bad idea but optimizing the transaction logic is the advice everyone is talking about. Also, when it says that the lock is "exclusive", that's not quite true. a U lock mode is an "Update" lock. Update (U) locks allow shared (S) lock requests while Exclusive (X) locks do not.

My last point - if the deadlock is common, I would try to fix it. If it happens infrequently/rarely, I would not worry about it too much UNLESS it is causing issues for you (bad data and you need to manually go in and fix it at a later date for example).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.