Solved

Figure out XML key lock deadlock

Posted on 2014-02-27
13
383 Views
Last Modified: 2014-03-05
Hi, I am trying to figure out the deadlock info in the xml.
I know it is a keylock, it seems to indicate that it is looking to get a lock on the index I think.
Odd that some of the information I would of liked to see in here did not seem to be populated.  The XML is coming from the extend event deadlock.
I think it had done the inserts in each process in the table and now the lock issue on the index. This is what I think happened, not sure I am reading this right.
There is no clustered index on the table, only one index on the table, no foreign keys and no constraints.
Do I have it right? it is a lock issue on the index?
Thanks
Bob
<deadlock>
  <victim-list>
    <victimProcess id="processa5df948" />
  </victim-list>
  <process-list>
    <process id="processa5df948" taskpriority="0" logused="1072" waitresource="KEY: 1143:72057596377563136 (930d4b645bed)" waittime="4312" ownerId="2883028677" transactionname="INSERT" lasttranstarted="2014-02-26T05:32:50.560" XDES="0x102742b950" lockMode="X" schedulerid="24" kpid="8996" status="suspended" spid="2940" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-02-26T05:32:50.320" lastbatchcompleted="2014-02-26T05:32:50.320" clientapp=".Net SqlClient Data Provider" hostname="WEB11" hostpid="4880" loginname="em" isolationlevel="read committed (2)" xactid="2883028677" currentdb="1143" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="" line="57" stmtstart="3530" stmtend="4970" sqlhandle="0x03007704331eda65ac9cb6002ea200000100000000000000" />
      </executionStack>
      <inputbuf>
Proc [Database Id = 1143 Object Id = 1708793395]   </inputbuf>
    </process>
    <process id="process4f5abc8" taskpriority="0" logused="1076" waitresource="KEY: 1143:72057596377563136 (4bc9a4268952)" waittime="4312" ownerId="2883028646" transactionname="INSERT" lasttranstarted="2014-02-26T05:32:50.557" XDES="0x377967950" lockMode="X" schedulerid="9" kpid="8032" status="suspended" spid="5529" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-02-26T05:32:50.393" lastbatchcompleted="2014-02-26T05:32:50.393" clientapp=".Net SqlClient Data Provider" hostname="WEB2" hostpid="2620" loginname="em" isolationlevel="read committed (2)" xactid="2883028646" currentdb="1143" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="" line="57" stmtstart="3530" stmtend="4970" sqlhandle="0x03007704331eda65ac9cb6002ea200000100000000000000" />
      </executionStack>
      <inputbuf>
Proc [Database Id = 1143 Object Id = 1708793395]   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057596377563136" dbid="1143" objectname="" indexname="" id="lockb4d920600" mode="X" associatedObjectId="72057596377563136">
      <owner-list>
        <owner id="process4f5abc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="processa5df948" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057596377563136" dbid="1143" objectname="" indexname="" id="lock1080385380" mode="X" associatedObjectId="72057596377563136">
      <owner-list>
        <owner id="processa5df948" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process4f5abc8" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>
0
Comment
Question by:remenard
  • 7
  • 6
13 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Yes indeed - Victim as you can see was the "processa5df948" INSERT statement and you also have the dbid and objected in the details.
My guess is the table has a PK(CLUSTERED would be the worst case scenario for the insert/delete) and maybe FKey or referenced in a FK.
If there was an UPDATE in the deadlock my usual workaround is to add ROWLOCK (or UPDLOCK) hint and OPTION (MAXDOP 1)
If your INSERT is built from a SELECT where you may even use the table you INSERT into then make sure at least the WITH (NOLOCK) exists and also you can try the OPTION (MAXDOP 1) if parallelism is involved in the query plan.


Aside these - do you have any regular maintenance (RE-INDEX, UPDATE STATISTICS) running as this would definitely help as well.
0
 

Author Comment

by:remenard
Comment Utility
Thanks for the input.

No clustered index
No constraints
No foreign keys
One non clustered PK index, composite key of 3 fields, 2 are guids.

The table is very volatile, with many deletes and inserts.
Table maintenance is done once every 2 weeks.
For grins, on Monday we defragged the index at noon by 3 pm the index was at 74% frag.

The proc  has 3 steps
1. delete any update or old rows, based on the key
2. insert new rows
3. insert the update rows.

Each insert is the same different data source passed in.
I am guessing it is getting hung up on the index due to it says a key lock.
Trying to figure out what is the blocking item.
Thanks
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
"I am guessing it is getting hung up on the index due to it says a key lock."

Yes, and my guess is that it all runs on parallel processors - can you please check that in the query plan for the SP that has the 3steps as you described?

"One non clustered PK index, composite key of 3 fields, 2 are guids"

This one for sure does not help in particular the GUIDs and hopefully you don't update any columns part of the PK.
0
 

Author Comment

by:remenard
Comment Utility
Ok,
I have the proc and data.
 I ran it and have the execution plan, the only part I am unsure of is how to tell if they all executed in parrallel?
Thanks
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
"For grins, on Monday we defragged the index at noon by 3 pm the index was at 74% frag"

What is the FILLFACTOR on that non clustered PK? even though lots of inserts/deletes take place as you said it may help to have it at least 80 instead of 100%


Also if you run SQL Enterprise edition you could rebuild it with ONLINE ON instead of just defrag then update stats should help but do it more often than just once a week if possible.

You could run some command like below:

ALTER INDEX PK_KeyName ON dbo.TableName REBUILD WITH(FILLFACTOR = 80, ONLINE=ON)
0
 

Author Comment

by:remenard
Comment Utility
We are running with multiple processors.
Not sure how to tell if the insert and deletes are all running in parallel.

We are running Standard Edition, we wish we were running Enterprise, it would help a lot.

Fill factor for the index is 90%.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 39

Expert Comment

by:lcohan
Comment Utility
So you are in SSMS and if you right click the SPROC then Modify it will open it in a new window.

Remove the ALTER and anything else to leave just the commands to be executed and so you don't run the refresh(ALTER) by accident.

If you have a parameter passed in - declare a local variable instead and fill it in with value(s)

On the SSMS Query menu select the "Display Estimated Query Plan" and that will bring up the plan. if you see the yellow circle with horizontal arrows then that statement runs on parallel processors
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
"Not sure how to tell if the insert and deletes are all running in parallel."

Is not that the different statements run in parallel but that a single statement(which could be complex) will be split on multiple processors. I believe by default threshold cost for parallel execution in SQL is 5 which is low on busy OLTP so most of the queries will be split on multiple threads which SQL can't always handle. I believe is yellow circle with 2 or 3 parallel horizontal arrows and if you hover over it will show details.
0
 

Author Comment

by:remenard
Comment Utility
Ok, I know what your talking about in parallel, no both the estimate and actual execution plans do not have any thing being done in parallel.
From the properties for the delete and 2 inserts it had max degree of parallelism of 1.
 
There are no updates, it deletes any row it will up date and do an insert.
0 to 20 or so rows on average are passed in for each parameter.
Proc has  3 parms, all xml format.
Delete - passes in xml to parse
insert - passes in xml to parse and insert
update - passes in xml to parse, inserts

In proc
1. delete  removes both delete and update rows
2. insert - inserts the insert rows.
3. insert - inserts the update rows.

I wish the deadlock info had more detail.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
"
Delete - passes in xml to parse
insert - passes in xml to parse and insert
update - passes in xml to parse, inserts
"


So are there INSERT,UPDATE,DELETE in the same SPROC for 0-20 rows average?
If so, I think a no brainer no business logic impact would be to add the ROWLOCK hints to the deletes and updates and NOLOCK to any SELECT or JOIN in that SP. Also was there any missing index showing on the query plan?
Do you have any monitoring tools installed? I use and always recommend SQL own Performance Dashboard as it can help a lot debug without any impact issues on directly PROD boxes.
0
 

Author Comment

by:remenard
Comment Utility
The three input parms pass 0-20 rows of data on average

in side the proc, it does no update, the delete removes the row to update, also the delete rows.
Inside the proc only does delete and insert.
So inside the proc it parses the delete and update param to get all rows to delete.
Now it deletes all of the parsed rows found.
Next does insert of the new rows to insert parsing the that xml.
Last does insert of the update rows parsing that xml.

The only join is to the table it is deleting from.
The keys to delete with are in a temp variable table.

That is the order of processing in side the proc.

My idea is parse all 3 params into single temp var table.
Run the delete off it  first.
Then run just insert once for all data.
Get ride of running 2 inserts.
on the insert I was thinking of adding updlock and readpast.
These rows are tied only to the user with the keys, no other use should have that set.
Thought that would help remove some of the issues.

They would like me to nail down what is happening before changing it.
The way the proc runs now my way cuts down the cpu and elapsed time in half.
I just parse it all once to the table and then handle it.
It is repeatedly parsed in each step, figure that may add to the issue.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
It all sounds good to me and it should alleviate the issue and give lot less chance for the deadlock to occur. I would use a @table to get the all the inputs in one shot as you mentioned and if you need to put the keys in a table I would use #temp table where you can even add an index if # of rows are>100.
0
 

Author Closing Comment

by:remenard
Comment Utility
Thanks for the help
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now