Stored proc, deadlocking after a minor change, but nothing has changed on the deadlocking T-SQL statement

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  November 11, 2011 at 12:33 pm

  #1406555

  dooh.register (11/11/2011)

  ---

  Try to use scope_identity() instead of @@IDENTITY. scope_identity() is session wide. @@IDENTITY is global and it may be source of your problems.

  @@Identity is not global. It's the last identity value for that session. From Books Online:

  After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement.

  @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

  @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

  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

• tfifield

  SSCrazy Eights

  Points: 9655

  More actions

  November 15, 2011 at 1:47 pm

  #1408161

  Just to further clarify what Gale posted, SCOPE_IDENTITY returns the last identity inserted in the current scope, which is not necessarily the same as the current session. This script demonstrates what will happen if you use @@identity after inserting into a table that has an audit trigger that inserts into an audit table that also has an identity:

  CREATE TABLE Test

  ( TestID INT IDENTITY (1, 1)

  , SomeText VARCHAR(50))

  GO

  CREATE TABLE TestAudit

  ( AuditID INT IDENTITY(1, 1)

  , TestID INT

  , SomeText VARCHAR(50)

  , ActionDone VARCHAR(50))

  GO

  CREATE TRIGGER TestInsert ON dbo.Test AFTER INSERT

  AS

  INSERT INTO TestAudit

  ( TestID, SomeText, ActionDone )

  SELECT

  TestID, SomeText, 'INSERT'

  FROM inserted

  GO

  -- Insert into both tables.

  INSERT INTO Test

  ( SomeText )

  SELECT 'Some Text'

  GO

  -- Re-set the identity on table Test

  TRUNCATE TABLE Test

  GO

  -- New insert

  INSERT INTO Test

  ( SomeText )

  SELECT 'More Text'

  SELECT

  @@IDENTITY AS AtAtIdentity -- The identity of TestAudit

  , SCOPE_IDENTITY() AS ScopeIdentity -- The identity of Test

  DROP TABLE Test

  DROP TABLE TestAudit

  It's always safer to go with SCOPE_IDENTITY

  Todd Fifield