Identity Column in SQL Server - Transaction - MultiUSer

• James Birch

  Grasshopper

  Points: 18

  More actions

  June 9, 2009 at 7:15 am

  #1006662

  Hi,

  Found a better way of doing this:

  SELECT MAX(DeleteMeID) FROM DeleteMe WITH (HOLDLOCK, UPDLOCK)

  This will prevent anyone else INSERTING data into the table until the end of the transaction, BUT crucially it doesn’t prevent SELECTs on the table or UPDATEs and DELETEs on anything except the last row.

  So the test code is now:

  --===== Identify the database to use (Tempdb is a nice safe place)

  USE TempDB

  WAITFOR TIME '13:39:00'

  BEGIN TRAN

  SELECT MAX(DeleteMeID) FROM DeleteMe WITH (HOLDLOCK, UPDLOCK)

  INSERT INTO DeleteMe

  (SomeStringData,Spid)

  SELECT TOP 100

  'xxxxxx' AS SomeStringData,

  @@SPID

  FROM master..syscolumns

  COMMIT

  Cheers,

  James