Deadlocks on Clustered Index

  • Eric M Russell - Wednesday, April 19, 2017 2:16 PM

    DesNorton - Wednesday, April 19, 2017 1:45 PM

    The OUTPUT requires a table/temp table/table variable to inset the value into.

    DECLARE @Results TABLE (NextInvoice INT);

    UPDATE #SalBranch WITH (TABLOCK)
    SET NextInvoice = NextInvoice + 1
    OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
    WHERE Branch = 'AA';

    SELECT NextInvoice
    FROM @Results;

    Since this is a single update, the Quirky Update method will probably be a better solution.

    DECLARE @NextInvoice INT;

    UPDATE SalBranch WITH (XLOCK)
    SET @NextInvoice = NextInvoice = NextInvoice + 1
    WHERE Branch = 'AA';

    Thanks, DesNorton. I didn't know until now that inline variable assignment within an UPDATE statement like that was possible.

    It's an undocumented feature, so no guarantees that it will always work that way (and to ensure it works in current versions you need a bunch of conditions, scan in clustered index order and no parallelism at least)
    Edit: The quirky update is undocumented (it depends on an implicit ordering of rows). The variable = expression = expression shown here is fully documented and supported.

    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
  • Eric M Russell - Wednesday, April 19, 2017 2:16 PM

    DesNorton - Wednesday, April 19, 2017 1:45 PM

    The OUTPUT requires a table/temp table/table variable to inset the value into.

    DECLARE @Results TABLE (NextInvoice INT);

    UPDATE #SalBranch WITH (TABLOCK)
    SET NextInvoice = NextInvoice + 1
    OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
    WHERE Branch = 'AA';

    SELECT NextInvoice
    FROM @Results;

    Since this is a single update, the Quirky Update method will probably be a better solution.

    DECLARE @NextInvoice INT;

    UPDATE SalBranch WITH (XLOCK)
    SET @NextInvoice = NextInvoice = NextInvoice + 1
    WHERE Branch = 'AA';

    Thanks, DesNorton. I didn't know until now that inline variable assignment within an UPDATE statement like that was possible.

    That is what makes the Quirky Update possible as well as some other update processes where intermediate results need to be stored as well as a final result.

  • GilaMonster - Wednesday, April 19, 2017 2:32 PM

    Eric M Russell - Wednesday, April 19, 2017 2:16 PM

    DesNorton - Wednesday, April 19, 2017 1:45 PM

    The OUTPUT requires a table/temp table/table variable to inset the value into.

    DECLARE @Results TABLE (NextInvoice INT);

    UPDATE #SalBranch WITH (TABLOCK)
    SET NextInvoice = NextInvoice + 1
    OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
    WHERE Branch = 'AA';

    SELECT NextInvoice
    FROM @Results;

    Since this is a single update, the Quirky Update method will probably be a better solution.

    DECLARE @NextInvoice INT;

    UPDATE SalBranch WITH (XLOCK)
    SET @NextInvoice = NextInvoice = NextInvoice + 1
    WHERE Branch = 'AA';

    Thanks, DesNorton. I didn't know until now that inline variable assignment within an UPDATE statement like that was possible.

    It's an undocumented feature, so no guarantees that it will always work that way (and to ensure it works in current versions you need a bunch of conditions, scan in clustered index order and no parallelism at least)

    Looks documented to me: https://technet.microsoft.com/en-us/library/ms177523(v=sql.110).aspx

  • Lynn Pettis - Wednesday, April 19, 2017 2:44 PM

    The variable = expression = expression part is. The quirky update is not. The quirky update depends on undocumented behavior (ordering of rows when no order by is present)

    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
  • GilaMonster - Wednesday, April 19, 2017 2:58 PM

    Lynn Pettis - Wednesday, April 19, 2017 2:44 PM

    The variable = expression = expression part is. The quirky update is not. The quirky update depends on undocumented behavior (ordering of rows when no order by is present)

    All I said it is what allows the quirky update to work.  Without this, there would be no quirky update, documented or not.

  • Eric M Russell - Wednesday, April 19, 2017 2:16 PM

    DesNorton - Wednesday, April 19, 2017 1:45 PM

    The OUTPUT requires a table/temp table/table variable to inset the value into.

    DECLARE @Results TABLE (NextInvoice INT);

    UPDATE #SalBranch WITH (TABLOCK)
    SET NextInvoice = NextInvoice + 1
    OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
    WHERE Branch = 'AA';

    SELECT NextInvoice
    FROM @Results;

    Since this is a single update, the Quirky Update method will probably be a better solution.

    DECLARE @NextInvoice INT;

    UPDATE SalBranch WITH (XLOCK)
    SET @NextInvoice = NextInvoice = NextInvoice + 1
    WHERE Branch = 'AA';

    Thanks, DesNorton. I didn't know until now that inline variable assignment within an UPDATE statement like that was possible.

    What order do you want the incrementing "NextInvoice" to be in?  I ask because if it needs to be in order by invoice number, that particular bit of code is missing quite a few pieces including a safety check to cover the fact that it is an undocumented feature.

    Also, if that's ALL you want to do to the table, it would be much easier to just use ROW_NUMBER(), which CAN be done to an UPDATE if you do it through a CTE.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Lynn Pettis - Wednesday, April 19, 2017 3:07 PM

    All I said it is what allows the quirky update to work.  Without this, there would be no quirky update, documented or not.

    Do note that I wasn't replying to you (since I posted before you)

    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
  • Jeff Moden - Wednesday, April 19, 2017 3:32 PM

    Eric M Russell - Wednesday, April 19, 2017 2:16 PM

    DesNorton - Wednesday, April 19, 2017 1:45 PM

    The OUTPUT requires a table/temp table/table variable to inset the value into.

    DECLARE @Results TABLE (NextInvoice INT);

    UPDATE #SalBranch WITH (TABLOCK)
    SET NextInvoice = NextInvoice + 1
    OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
    WHERE Branch = 'AA';

    SELECT NextInvoice
    FROM @Results;

    Since this is a single update, the Quirky Update method will probably be a better solution.

    DECLARE @NextInvoice INT;

    UPDATE SalBranch WITH (XLOCK)
    SET @NextInvoice = NextInvoice = NextInvoice + 1
    WHERE Branch = 'AA';

    Thanks, DesNorton. I didn't know until now that inline variable assignment within an UPDATE statement like that was possible.

    What order do you want the incrementing "NextInvoice" to be in?  I ask because if it needs to be in order by invoice number, that particular bit of code is missing quite a few pieces including a safety check to cover the fact that it is an undocumented feature.

    Also, if that's ALL you want to do to the table, it would be much easier to just use ROW_NUMBER(), which CAN be done to an UPDATE if you do it through a CTE.

    This table has a single row in it.  A different numeric column is incremented depending on the next number that is required (eg: Invoivce, Credit Note, etc).

  • Jeff Moden - Wednesday, April 19, 2017 3:32 PM

    Eric M Russell - Wednesday, April 19, 2017 2:16 PM

    DesNorton - Wednesday, April 19, 2017 1:45 PM

    The OUTPUT requires a table/temp table/table variable to inset the value into.

    DECLARE @Results TABLE (NextInvoice INT);

    UPDATE #SalBranch WITH (TABLOCK)
    SET NextInvoice = NextInvoice + 1
    OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
    WHERE Branch = 'AA';

    SELECT NextInvoice
    FROM @Results;

    Since this is a single update, the Quirky Update method will probably be a better solution.

    DECLARE @NextInvoice INT;

    UPDATE SalBranch WITH (XLOCK)
    SET @NextInvoice = NextInvoice = NextInvoice + 1
    WHERE Branch = 'AA';

    Thanks, DesNorton. I didn't know until now that inline variable assignment within an UPDATE statement like that was possible.

    What order do you want the incrementing "NextInvoice" to be in?  I ask because if it needs to be in order by invoice number, that particular bit of code is missing quite a few pieces including a safety check to cover the fact that it is an undocumented feature.

    Also, if that's ALL you want to do to the table, it would be much easier to just use ROW_NUMBER(), which CAN be done to an UPDATE if you do it through a CTE.

    It was stated earlier that there is only one row in the table, but I'm still assuming it could potentially contain rows for additional branch offices at some point in the future. There is a primary key constraint on the [Branch] column, so the lookup on [Branch] should make this reliably selective. The potential for multiple rows would come into play only if there were a JOIN, which would indeed be quirky.

    WHERE Branch = 'AA';


    CONSTRAINT [SalBranchKey] PRIMARY KEY CLUSTERED 
    (
    [Branch] ASC
    )

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 9 posts - 31 through 39 (of 39 total)

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