March 18, 2024 at 6:03 am
Hi Experts,
I am observing strange deadlock behaviour where one deadlock graph have 2 isolated databases involved. Can any experts here shed some light on this?
Regards
March 18, 2024 at 8:12 am
Seems like the issue is due to parallelism, Any experts here can give more insight.
March 18, 2024 at 8:16 am
Sharing the deadlock graph / XML will help people to see what went on, otherwise people will be shooting blind without the actual information.
March 18, 2024 at 8:39 am
Can you save and post the deadlock graph ?
An .xdl file can directly be opened by e.g. Solarwinds Plan Explorer (free tool), which has a great replay feature, helping you understand what caused the issue.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 18, 2024 at 12:30 pm
This was removed by the editor as SPAM
March 18, 2024 at 12:52 pm
This was removed by the editor as SPAM
March 18, 2024 at 12:59 pm
This was removed by the editor as SPAM
March 18, 2024 at 2:17 pm
To add to what everyone else said, I would not be surprised if it isn't 2 isolated databases involved. What is MORE likely is that a view on database A is using a table on database B over a linked server and that is causing issues. Alternately, it could be nested stored procedures that call across databases (I think... not 100% certain on how that works with deadlocks, but I think it could cause deadlocks across databases).
IF you can't post the XML, then posting those 2 queries along with any relevant DDL would be helpful. IF the query is using a view it'll be hard to help without the XML or DDL as we will just be guessing.
Also, I removed the tags as those looked like the got put in by one or more of the spam replies.
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.
March 18, 2024 at 3:08 pm
Hi Experts,
I am observing strange deadlock behaviour where one deadlock graph have 2 isolated databases involved. Can any experts here shed some light on this?
Regards
Synonyms. Views. Joins that use more than the 3 part naming convention. Code may be stored procedure, managed code, ad hoc code, code from spreadsheets, code from things like Power Query, Power BI, Tableau, improper use of cross database SELECT/INTO, etc, etc, etc.
Without the deadlock graphs, there is no way for any of us to know.
You've also been around long enough to know that very well.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2024 at 11:39 am
Can you save and post the deadlock graph ? An .xdl file can directly be opened by e.g. Solarwinds Plan Explorer (free tool), which has a great replay feature, helping you understand what caused the issue.
Attached the file. THanks for the suggestion on Solarwinds. I used it but couldnt identify it.
March 25, 2024 at 11:40 am
VastSQL wrote:Hi Experts,
I am observing strange deadlock behaviour where one deadlock graph have 2 isolated databases involved. Can any experts here shed some light on this?
Regards
Synonyms. Views. Joins that use more than the 3 part naming convention. Code may be stored procedure, managed code, ad hoc code, code from spreadsheets, code from things like Power Query, Power BI, Tableau, improper use of cross database SELECT/INTO, etc, etc, etc.
Without the deadlock graphs, there is no way for any of us to know.
You've also been around long enough to know that very well.
Thanks Jeff, I have uploaded the graph in my last post.
March 25, 2024 at 12:15 pm
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 ! */
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 25, 2024 at 12:29 pm
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.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply