Strange Deadlock Graph showing multiple DB but isolated

Question

Strange Deadlock Graph showing multiple DB but isolated

Viewing post 16 (of 16 total)

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

Mr. Brian Gale SSC-Insane Points: 24968 More actions · Answer 1

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.