September 30, 2015 at 6:03 pm
Okay, I am figuring some of this out but I have a question regarding the values in the waitresource attribute of the deadlock graph.
The following are the values in one deadlock graph:
"KEY: 6:72057595083554816 (fd30f7c9838a)"
"KEY: 6:72057595083554816 (291970001013)"
"KEY: 6:72057595083554816 (291970001013)"
I know that the 6 is the database id of the database. I know that 72057595083554816 is the partition id of the index involved. What is the value in the parenthesis? I am hoping it may help me get a better handle on the deadlocks we are working on at this time.
The example above is not the typical deadlock we are faced with as it is all related to a single index on a single table. The deadlocks that are baffling us ar the ones between two tables with no relationship between them, meaning no foreign key are involved.
September 30, 2015 at 9:14 pm
Okay, I found that it is the index key has value. What does that mean?
October 1, 2015 at 12:58 am
Lock resource hash value. SQL uses a hash function on the index keys for locking purposes.
It's not all that useful, unless you want to use the %%lockres%% function to track down the exact row being locked.
http://www.sqlskills.com/blogs/paul/investigating-locking-and-deadlocking-with-lockres/
Post the deadlock graph and involved queries? I can't recall having needed to dig into the lock hash values to debug deadlocks before.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2015 at 4:50 am
GilaMonster (10/1/2015)
Post the deadlock graph and involved queries? I can't recall having needed to dig into the lock hash values to debug deadlocks before.
Maybe Lynn just likes to torture himself. @=)
But I am curious myself, so yes, Lynn. Please post so we can dig in and see what fun stuff we can learn.
October 1, 2015 at 9:58 am
Attached is just one of the deadlock graphs in a zip file.
October 1, 2015 at 9:59 am
Anyone have any idea why Profiler wouldn't capture the deadlock graph even when it was the only thing being captured?
October 1, 2015 at 10:19 am
Note, the indexes involved in the deadlock are both nonclustered indexes. There are no foreign key references between the tables OperationsSIGACTDraftReport or Workflow.
The queries are embedded SQL in the ColdFusion code of the application.
October 1, 2015 at 10:23 am
You've either got a filter in the trace definition or the deadlock didn't occur.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2015 at 11:00 am
GilaMonster (10/1/2015)
You've either got a filter in the trace definition or the deadlock didn't occur.
Only filter used was to the database and user involved (all connections use the same user). Also, I could capture the deadlocks with the extended events.
October 1, 2015 at 11:11 am
You should get rid of the INSULL() in the WHERE clause, as always. Just NEVER use ISNULL() in a WHERE or JOIN. But it's esp. important if that column is a usable key in any covering index.
(Mode <> @P2 OR (Mode IS NULL AND @P2 <> ''))
IsNull(Mode,'') <> @P2
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 1, 2015 at 11:22 am
ScottPletcher (10/1/2015)
You should get rid of the INSULL() in the WHERE clause, as always. Just NEVER use ISNULL() in a WHERE or JOIN. But it's esp. important if that column is a usable key in any covering index.(Mode <> @P2 OR (Mode IS NULL AND @P2 <> ''))
IsNull(Mode,'') <> @P2
Been suggested and at this time we are not going to go through the agony needed to make that change. It needs government approval, all the necessary testing associated with it including passing an IPAT.
So please, keep your nevers to yourself.
October 1, 2015 at 11:26 am
Lynn Pettis (10/1/2015)
ScottPletcher (10/1/2015)
You should get rid of the INSULL() in the WHERE clause, as always. Just NEVER use ISNULL() in a WHERE or JOIN. But it's esp. important if that column is a usable key in any covering index.(Mode <> @P2 OR (Mode IS NULL AND @P2 <> ''))
IsNull(Mode,'') <> @P2
Been suggested and at this time we are not going to go through the agony needed to make that change. It needs government approval, all the necessary testing associated with it including passing an IPAT.
So please, keep your nevers to yourself.
I will, and you can keep your resulting deadlocks to yourself.
Edit: Yes, <> does not lend itself to good plans anyway. But ISNULL() is still very poor coding practice in WHERE/JOIN clauses.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 1, 2015 at 11:38 am
ScottPletcher (10/1/2015)
Lynn Pettis (10/1/2015)
ScottPletcher (10/1/2015)
You should get rid of the INSULL() in the WHERE clause, as always. Just NEVER use ISNULL() in a WHERE or JOIN. But it's esp. important if that column is a usable key in any covering index.(Mode <> @P2 OR (Mode IS NULL AND @P2 <> ''))
IsNull(Mode,'') <> @P2
Been suggested and at this time we are not going to go through the agony needed to make that change. It needs government approval, all the necessary testing associated with it including passing an IPAT.
So please, keep your nevers to yourself.
I will, and you can keep your resulting deadlocks to yourself.
Edit: Yes, <> does not lend itself to good plans anyway. But ISNULL() is still very poor coding practice in WHERE/JOIN clauses.
Know what, I have been working with SQL Server for nearly 20 years. I have had the benefit of working in stable environments where deadlocks (knock on wood) did not occur or if they did it was so infrequent we didn't dig into them as simply rerunning the transaction was good enough.
I didn't write this code either, developers did while working in a combat zone (Iraq to be exact) with enough knowledge of SQL Server to be dangerous. This product had already been around 7 or 8 years when I joined the company. Interesting thing is that these deadlocks didn't become an issue until just recently.
I don't need you telling me what I already know and am trying to change when I can get it changed.
The biggest problem with the deadlocks we are trying to figure out is why two queries on two separate tables are deadlocking each other when there is no apparent relationship between them. No foreign keys, no joins between the tables.
That is what I am working on, not fixing code that is not efficient.
October 1, 2015 at 11:39 am
The trancount for those sessions is 2, so it could be as mundane as locks hanging around from other statements in other transactions. In that case, it would appear to be the age-old issue of sessions accessing the same objects in different orders.
I'd try to figure out what other code the application is passing in those sessions.
Cheers!
October 1, 2015 at 11:49 am
Jacob Wilkins (10/1/2015)
The trancount for those sessions is 2, so it could be as mundane as locks hanging around from other statements in other transactions. In that case, it would appear to be the age-old issue of sessions accessing the same objects in different orders.I'd try to figure out what other code the application is passing in those sessions.
Cheers!
Ahead of you on that, but it isn't easy. Looking to see how the web server connects to the database server.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply