title: Avoiding concurrency problems with MAX+1 integer in SQL Server 2008... making own IDENTITY value
body: <p>I need to increment an integer in a SQL Server 2008 column.  </p>
  
  <p>Sounds like I should use an <code>IDENTITY</code> column, but I need to increment separate counters for each of my customers. Think of an e-commerce site where each customer gets their own incrementing order number, starting with 1. The values must be unique (per customer). </p>
  
  <p>For example,</p>
  
  <pre><code>Customer1  (Order #s 1,2,3,4,5...)
  Customer2  (Order #s 1,2,3,4,5...)
  </code></pre>
  
  <p>Essentially, I will need to manually do the work of SQL's <code>identity</code> function since the number of customers is unlimited and I need <code>order #</code> counters for each of them.   </p>
  
  <p>I am quite comfortable doing:</p>
  
  <pre><code>BEGIN TRANSACTION
    SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
    INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
  COMMIT TRANSACTION
  </code></pre>
  
  <p>My problem is locking and concurrency concerns and assuring a unique value. It seems we need to lock with <code>TABLOCKX</code>. But this is a high volume database and I can't just lock the whole <code>Orders</code> table every time I need to do a <code>SELECT MAX+1</code> process and insert a new order record. </p>
  
  <p>But, if I don't lock the whole table, then I might not get a unique value for that customer.  Because some of our order entry is done after-the-fact in batches by a multi-threaded Windows process, it is possible that 2 operations will be simultaneously wanting to insert a new order for the same customer.</p>
  
  <p>So what locking methodology or technique will avoid deadlocks and still let me maintain unique incrementing order numbers PER customer?</p>