Deadlocks on Clustered Index

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 19, 2017 at 2:32 pm

  #1938962

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 19, 2017 at 2:32 pm

  #1938963

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 19, 2017 at 2:44 pm

  #1938966

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 19, 2017 at 2:58 pm

  #1938969

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

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

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 19, 2017 at 3:07 pm

  #1938972

  GilaMonster - Wednesday, April 19, 2017 2:58 PM

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

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

  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.

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  April 19, 2017 at 3:32 pm

  #1938975

  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.
  

  ---

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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 19, 2017 at 3:43 pm

  #1938977

  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

• DesNorton

  SSC-Insane

  Points: 24278

  More actions

  April 19, 2017 at 10:02 pm

  #1939005

  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).

  ---

  How to post data/code on a forum to get the best help.

• Eric M Russell

  SSC Guru

  Points: 125522

  More actions

  April 20, 2017 at 6:36 am

  #1939053

  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