Process deadlock

Question

Process deadlock

sarath.tata

SSCommitted

Points: 1640
More actions
March 6, 2015 at 3:13 pm

#398141

I get deadlock with the below query. I hope it is something with my sub-query here. I'm inserting into the same table which I use in sub-query
INSERT INTO Table1 (Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM #Temp t
LEFT JOIN Table2 sa (NOLOCK) ON t.Field1 = sa.Field1
LEFT JOIN Table3 s (NOLOCK) on S.Field2 = sa.Field2
WHERE s.Filed1 NOT IN (SELECT s.Field1 FROM Table 1asft (NOLOCK) WHERE asft.Field1 IS NULL AND asft.Field2 = @Field2Val)
From front-end, I execute a stored procedure containing this query using multi-threads so potentially multiple parallel SP instances execute in parallel.
If I retry it works fine!!
Can anyone advise any better alternatives?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Start by removing the nolock hints (they allow duplicate data and missing rows). Then turn traceflag 1222 on, see if you can get the deadlock to reoccur and post the deadlock graph (written to the error log) here.

There's no enough information in your post to begin to diagnose the problem.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sarath.tata SSCommitted Points: 1640 More actions · Answer 2

I enabled Trace and got into deadlock. Can you please advise where I can see the graph/log?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

GilaMonster (3/6/2015)
Then turn traceflag 1222 on, see if you can get the deadlock to reoccur and post the deadlock graph (written to the error log) here.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sarath.tata SSCommitted Points: 1640 More actions · Answer 4

This is the error log

2015-03-06 16:32:25.49 spid52 DBCC TRACEON 1222, server process ID (SPID) 52. This is an informational message only; no user action is required.
2015-03-06 16:33:03.78 spid19s deadlock-list
2015-03-06 16:33:03.78 spid19s deadlock victim=process4098bc8
2015-03-06 16:33:03.78 spid19s process-list
2015-03-06 16:33:03.78 spid19s process id=process4098bc8 taskpriority=0 logused=0 waitresource=KEY: 9:72057594042187776 (17e370f8a476) waittime=43 ownerId=2618751 transactionname=user_transaction lasttranstarted=2015-03-06T16:33:03.657 XDES=0xa02a63b0 lockMode=U schedulerid=2 kpid=2524 status=suspended spid=54 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-03-06T16:33:03.657 lastbatchcompleted=2015-03-06T16:33:03.657 clientapp=.Net SqlClient Data Provider hostname=test hostpid=34242 loginname=testisolationlevel=read committed (2) xactid=2618751 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2015-03-06 16:33:03.78 spid19s executionStack
2015-03-06 16:33:03.78 spid19s frame procname=ComputerAssetMgt.dbo.uspSaveTest line=56 stmtstart=4196 stmtend=4440 sqlhandle=0x03000900592685733b31100153a400000100000000000000
2015-03-06 16:33:03.78 spid19s UPDATE asset.tblAssetSoftware SET LastRemoved = @LastRemovedTimeStamp WHERE AssetID = @AssetID AND LastRemoved IS NULL
2015-03-06 16:33:03.78 spid19s inputbuf
2015-03-06 16:33:03.78 spid19s Proc [Database Id = 9 Object Id = 1938105945]
2015-03-06 16:33:03.78 spid19s process id=process40b2bc8 taskpriority=0 logused=9152 waitresource=PAGE: 9:1:5857 waittime=13 ownerId=2618749 transactionname=user_transaction lasttranstarted=2015-03-06T16:33:03.637 XDES=0x9fb8ee80 lockMode=U schedulerid=3 kpid=9068 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-03-06T16:33:03.653 lastbatchcompleted=2015-03-06T16:33:03.637 clientapp=.Net SqlClient Data Provider hostname=test hostpid=34242 loginname=test isolationlevel=read committed (2) xactid=2618749 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2015-03-06 16:33:03.78 spid19s executionStack
2015-03-06 16:33:03.78 spid19s frame procname=ComputerAssetMgt.dbo.uspSaveTest line=58 stmtstart=4442 stmtend=5138 sqlhandle=0x03000900592685733b31100153a400000100000000000000
2015-03-06 16:33:03.78 spid19s UPDATE asset.tblAssetSoftware SET LastRemoved = NULL
2015-03-06 16:33:03.78 spid19s FROM #Temp t
2015-03-06 16:33:03.78 spid19s INNER JOIN asset.tblSoftwareAlias sa ON t.Value = sa.Name
2015-03-06 16:33:03.78 spid19s INNER JOIN asset.tblSoftware s on S.SoftwareID = sa.SoftwareID
2015-03-06 16:33:03.78 spid19s INNER JOIN asset.tblAssetSoftware asft ON s.SoftwareID = asft.SoftwareID AND asft.AssetID = @AssetID AND asft.LastRemoved = @LastRemovedTimeStamp
2015-03-06 16:33:03.78 spid19s inputbuf
2015-03-06 16:33:03.78 spid19s Proc [Database Id = 9 Object Id = 1938105945]
2015-03-06 16:33:03.78 spid19s resource-list
2015-03-06 16:33:03.78 spid19s keylock hobtid=72057594042187776 dbid=9 objectname=ComputerAssetMgt.asset.tblAssetSoftware indexname=PK_AssetSoftware id=lock936e3b00 mode=X associatedObjectId=72057594042187776
2015-03-06 16:33:03.78 spid19s owner-list
2015-03-06 16:33:03.78 spid19s owner id=process40b2bc8 mode=X
2015-03-06 16:33:03.78 spid19s waiter-list
2015-03-06 16:33:03.78 spid19s waiter id=process4098bc8 mode=U requestType=wait
2015-03-06 16:33:03.78 spid19s pagelock fileid=1 pageid=5857 dbid=9 objectname=ComputerAssetMgt.asset.tblAssetSoftware id=lock936ddd00 mode=IX associatedObjectId=72057594042187776
2015-03-06 16:33:03.78 spid19s owner-list
2015-03-06 16:33:03.78 spid19s owner id=process4098bc8 mode=IU
2015-03-06 16:33:03.78 spid19s waiter-list
2015-03-06 16:33:03.78 spid19s waiter id=process40b2bc8 mode=U requestType=convert

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 5

It doesn't look like that particular deadlock has anything to do with the query you posted even with the understanding that you've probably sanitized the query a bit. That means 2 things... 1) this particular deadlock graph isn't going to help us with your query and 2) you've go deadlock problems with other queries, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sarath.tata SSCommitted Points: 1640 More actions · Answer 6

You are absolutely correct!! I realized later that my posted query has no impact but the one in the error log has created deadlock!! Any guesses how to tune that?

It is just updating a column!!

I believe, in my stored procedure, I have two update statements on the same table with a few joins and different criteria (as shown in the log) which are causing deadlocks.

is there any special mechanism that we need to adopt in such cases? I have a multi-threading application executing the SP!!

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 7

sarath.tata (3/6/2015)
You are absolutely correct!! I realized later that my posted query has no impact but the one in the error log has created deadlock!! Any guesses how to tune that?
It is just updating a column!!
I believe, in my stored procedure, I have two update statements on the same table with a few joins and different criteria (as shown in the log) which are causing deadlocks.
is there any special mechanism that we need to adopt in such cases? I have a multi-threading application executing the SP!!

First think to do is look at the stored procedures listed in that deadlock graph and look for long winded transactions. Second thing to do is to make the code run faster and use fewer resources. Look at the execution plan for both code snippets. It might only need the addition of an index or a change to an index. It might require other parts of the code to be sped up. I'd also recommend a change to the two code snippets where you use an alias for the table from the FROM clause in the update clause instead of the actual table name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sarath.tata SSCommitted Points: 1640 More actions · Answer 8

Thanks for your ideas. I'll look into them. By the way, do you mean this below on the usage of alias in UPDATE?

UPDATE asft SET LastRemoved = NULL

instead of

UPDATE asset.tblAssetSoftware SET LastRemoved = NULL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Can you post the complete definition of ComputerAssetMgt.dbo.uspSaveTest please? Also all definition and all indexes for the table ComputerAssetMgt.asset.tblAssetSoftware

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sarath.tata SSCommitted Points: 1640 More actions · Answer 10

It seems the weekend break worked for me to think fresh!!

I glanced through the query and found that I'm using joins on the columns that are not indexed and as a result, as the table grows bigger, the query execution takes time.

I added indices based on my joins and where clause conditions and everything looks good so far!

Thanks for your help