x

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

more ▼

asked Oct 29 '09 at 02:11 PM in Default

Leo gravatar image

Leo
1.6k 51 56 58

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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) 
more ▼

answered Oct 29 '09 at 03:55 PM

Laerte Junior gravatar image

Laerte Junior
488 2

I agree, but I'd put "option (MAXDOP 1)" first as the message says about 'parallel worker threads'.
Oct 30 '09 at 08:58 AM AlexS
Yes could be. But I see much to say that if you are experiencing problems with parallelism, simply take it. I do not agree with that, I think if the sql server is choosing to parallelize is because he is doing what it can with the information he have. If no index, out of date stats etc. .. he is doing his best with it. And when I had problems with parallelism most of them because does not have correct indexes
Oct 30 '09 at 11:54 AM Laerte Junior
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 29 '09 at 03:38 PM

Slick84 gravatar image

Slick84
1.3k 75 102 142

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x34

asked: Oct 29 '09 at 02:11 PM

Seen: 3551 times

Last Updated: Oct 29 '09 at 02:18 PM