Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    Salvador - BA - Brasil
    Posts
    12

    Unanswered: Query with union

    Hello Group!

    I submmit a SP with a update query that use multiple UNION.
    This SP fire multiple sub-threads for the same SPID and fail with deadlock condition.
    There is a way for suppress that sub-threads ?

    TIA,

    Aldair.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Need more info on your sp. I'd be leary about running update queries against Union joins. You might be better off using your union query to create a temporary table or table variable that holds the index keys of the values you want to update, and then update your values in a second step.

    blindman

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426

    Re: Query with union

    The threads shoukd all enlist in the same transaction and not cause a deadlock on it's own.

    If you think it is causing a problem the try it with maxdop=1

  4. #4
    Join Date
    May 2003
    Location
    Salvador - BA - Brasil
    Posts
    12

    Thumbs up

    Thanks for all.

    My update comand that has a where condition with several UNIONs fail when run at the production server (more than one CPU) but it work fine at test server (single CPU). This look like a bug!

    When I use the OPTION clause MAXDOP=1, it work at the production box.

    Thank you for help.

    Aldair.

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Do you have the latest service pack?
    There have been a number of bugs fixed to do with this.

  6. #6
    Join Date
    May 2003
    Location
    Salvador - BA - Brasil
    Posts
    12
    Yes, I do.

    Microsoft SQL Server 2000 - 8.00.760

    This is the SP 3, isn´t it ?

    We use the SQL Profiler to see the procedure´s steps and during the update the SQL Server build a multi-thread plan (we can see it using SP_WHO: SPID= n, ECID= 0,1,2,3,4), and at this point the procedure failed with error 1205 (Dead Lock victim).

    Thank you for your help.

    Aldair

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •