Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Figure out XML key lock deadlock

Posted on 2014-02-27
13
Medium Priority
?
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39892839
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
ID: 39892907
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 40

Expert Comment

by:lcohan
ID: 39892937
"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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:remenard
ID: 39892976
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 40

Expert Comment

by:lcohan
ID: 39893032
"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
ID: 39893051
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
 
LVL 40

Expert Comment

by:lcohan
ID: 39893068
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 40

Expert Comment

by:lcohan
ID: 39893081
"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
ID: 39893187
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 40

Expert Comment

by:lcohan
ID: 39893222
"
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
ID: 39893338
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 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 39894964
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
ID: 39907220
Thanks for the help
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 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