How can I lock a row in a SQL Server database to simulate the IDENTITY property column? I want to guarantee that two rows don't contain the same number.
On your table, you can take an exclusive lock that confines the next incremental counter value to that table. Listing 2 shows sample code that takes such a lock. Be sure, however, not to call your number-allocation routine within a larger transaction because the c1 value will be locked until the outer transaction commits or aborts.
Obtaining a new number before starting the main transaction is typical. However, you might have to handle sequence gaps if the second transaction never commits because some business rules don't allow sequence gaps. If you have to accommodate sequence gaps, insert a dummy record into the table. You can replace the dummy record after the transaction is successfully committed.