question

Leo avatar image
Leo asked

Deadlock in SQL 2008

Hi,

I got the following messages in SQL 2008 Server Profiler while I am running .Net application -

Parallel query worker thread was involved in a deadlock

But I am running simple Update Statement...Anybody know why?

then I got the following messages in SQL Logs.........

*10/29/2009 17:08:04,spid7s,Unknown,ResType:ExchangeId Stype:'AND' SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000002A5FA8510) Value:0xbadd6748 Cost:(0/10000)
10/29/2009 17:08:04,spid7s,Unknown,Port: 0x00000002BBE21850  Xid Slot: 0 Wait Slot: 1 Task: 0x00000000BADD6748 (Coordinator) Exchange Wait Type: e_waitPipeGetRow Merging: 0
10/29/2009 17:08:04,spid7s,Unknown,Node:4
10/29/2009 17:08:04,spid7s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
10/29/2009 17:08:04,spid7s,Unknown,ResType:ExchangeId Stype:'AND' SPID:58 BatchID:0 ECID:4 TaskProxy:(0x0000000429ADF770) Value:0x807ef708 Cost:(0/10000)
10/29/2009 17:08:04,spid7s,Unknown,Port: 0x000000036CA72CD0  Xid Slot: 0 Wait Slot: 3 Task: 0x00000000807EF708 (Consumer) Exchange Wait Type: e_waitPipeGetRow Merging: 0
10/29/2009 17:08:04,spid7s,Unknown,Node:3
10/29/2009 17:08:04,spid7s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
10/29/2009 17:08:04,spid7s,Unknown,ResType:ExchangeId Stype:'AND' SPID:58 BatchID:0 ECID:3 TaskProxy:(0x0000000429ADF710) Value:0xbadd7048 Cost:(20/0)
10/29/2009 17:08:04,spid7s,Unknown,Port: 0x00000002BBE7E5B0  Xid Slot: 2 Wait Slot: 0 Task: 0x00000000BADD7048 (Producer) Exchange Wait Type: e_waitPipeNewRow Merging: 1
10/29/2009 17:08:04,spid7s,Unknown,Node:2
10/29/2009 17:08:04,spid7s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
10/29/2009 17:08:04,spid7s,Unknown,ResType:ExchangeId Stype:'AND' SPID:58 BatchID:0 ECID:1 TaskProxy:(0x0000000429ADF650) Value:0xfefb88 Cost:(20/0)
10/29/2009 17:08:04,spid7s,Unknown,Port: 0x000000036CA73770  Xid Slot: 3 Wait Slot: 1 Task: 0x0000000000FEFB88 (Producer) Exchange Wait Type: e_waitPipeNewRow Merging: 1
10/29/2009 17:08:04,spid7s,Unknown,Node:1
10/29/2009 17:08:04,spid7s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
10/29/2009 17:08:04,spid7s,Unknown,Wait-for graph
10/29/2009 17:08:04,spid7s,Unknown,Deadlock encountered .... Printing deadlock information*

Thanks.

Leo

sql-server-2008profiler
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Laerte Junior avatar image
Laerte Junior answered

HUmm..Only a simple update ?

First - Look at yor indexes..This update using index ?

Second - Try to put ROWLOCK hint at update

UPDATE table WITH (ROWLOCK)

Third - try to put MADXOP hint at update or increase 'cost threshold for parallelism' with sp_configure

Update table.......option (MAXDOP 1)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 avatar image
Slick84 answered

Try using the system stored procedure sp_who2 while running the update query to see if there is any blocking going on.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.