HELP!!! Intra-query parallelism caused DEAD LOCK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

HELP!!! Intra-query parallelism caused DEAD LOCK

Author

Topic

Tracey
Starting Member

40 Posts

Posted - 2004-06-21 : 22:11:50

Hi everyone,
When i tried to Execute this Query

select count(a.tfk)'Count', b.tpk, b.act
from lra a, lraa b
where a.tfk = b.tpk
and a.tfk not in (select tpk from lraas)
group by b.tpk, b.act

Server: Msg 8650, Level 13, State 1, Line 1
Intra-query parallelism caused your server command (process ID #100) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).

I tried to use option (MAXDOP 1), but it still does the same.which is:

Is there anyway to rewrite the query in better way?

Many thanks

Tracey

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-21 : 22:45:46

SELECT
COUNT(a.tfklraassm) AS 'Count',
b.tpklraacty,
b.actycode
FROM
lraasac a
INNER JOIN lraacty b ON a.tfklraacty = b.tpklraacty
LEFT OUTER JOIN lraassm c ON a.tfklraassm = c.tpklraassm
WHERE
c.tpklraassm IS NULL

This will work. And, don't use that syntax. :) It's not ANSI standard and should be avoided.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Tracey
Starting Member

40 Posts

Posted - 2004-06-21 : 23:38:33

Thanks Derrick,
I will run this and let you know the result!

many thanks
Tracey

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-22 : 05:37:19

Do you have sp3a or sp3? This was a problem that was fixed in some cases.

Looks like more were introduced as well.
Look at this for a few workarounds
http://support.microsoft.com/?kbid=837983
Don't see how you can get this when you set maxdop=1 though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Tracey
Starting Member

40 Posts

Posted - 2004-06-22 : 21:31:22

its SP3a.
I have used the Query given by Derrick.
It does the job!
thanks Guys

Tracey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources