title: Ensuring unique numbers from a sql server database
body: <p>I have an application that uses incident numbers (amongst other types of numbers).  These numbers are stored in a table called "Number_Setup", which contains the current value of the counter.  </p>
  
  <p>When the app generates a new incident, it number_setup table and gets the required number counter row (counters can be reset daily, weekly, etc and are stored as int's).  It then incremenets the counter and updates the row with the new value.</p>
  
  <p>The application is multiuser (approximately 100 users at any one time, as well as sql jobs that run and grab 100's of incident records and request incident numbers for each).  The incident table has some duplicate incident numbers where they should not be duplicate.  </p>
  
  <p>A stored proc is used to retrieve the next counter.</p>
  
  <pre>
  SELECT @Counter = counter, @ShareId=share_id, @Id=id
  FROM Number_Setup
  WHERE LinkTo_ID=@LinkToId
  AND Counter_Type='I'
  
  IF isnull(@ShareId,0) > 0
  BEGIN 
      -- use parent counter
      SELECT @Counter = counter, @ID=id
      FROM Number_Setup
      WHERE Id=@ShareID
  END
  
  SELECT @NewCounter = @Counter + 1
  
  UPDATE Number_Setup SET Counter = @NewCounter
  WHERE id=@Id
  </pre>
  
  <p>I've now surrounded that block with a transaction, but I'm not entirely sure it' will 100% fix the problem, as I think there's still shared locks, so the counter can be read anyway.  </p>
  
  <p>Perhaps I can check that the counter hasn't been updated, in the update statement</p>
  
  <pre>
  UPDATE Number_Setup SET Counter = @NewCounter
  WHERE Counter = @Counter
  IF @@ERROR = 0 AND @@ROWCOUNT > 0 
      COMMIT TRANSACTION
  ELSE
      ROLLBACK TRANSACTION
  </pre>
  
  <p>I'm sure this is a common problem with invoice numbers in financial apps etc.<br>
  I cannot put the logic in code either and use locking at that level.
  I've also locked at HOLDLOCK but I'm not sure of it's application.  Should it be put on the two SELECT statements?</p>
  
  <p>How can I ensure no duplicates are created?</p>