Handling SQL Server Concurrency Issues

Sometimes I need to acquire a unique ID and store it with a record, but I am unable to use an identity column. So instead I have a table which provides unique IDs using a label fie

Solution 1:

You could increment and fetch the ID in the update statement using output.

update UniqueIdTable
set ID = ID + 1
output deleted.ID
where label = @inputLabel

Solution 2:

I think you are correct that two threads can read the same value in step 2. I can think of two alternatives:

1. Add a predicate for id in the update statement so that it updates only if the value hasn't changed. If the update does not update any record (don't know how to check in SQL Server but must be possible) then retry the operation.

2. Execute the update statement first. Only one thread will be able to execute. Then select the updated value.

I have two other suggestions

1. Do this in a separate transaction so that a long running transaction does not block another

2. Reserve a thread-local block at the application layer. Increment by large value then 1 and use the ids from the thread-local block. This will reduce server roundtrips and updates to the table

Solution 3:

Create a custom table in some database where you can include an incremental ID field. Any app needing this number will create a record and utilize the returned value. Even if you take this value and don't apply it to the table where you need it, it will still be unique even if you apply it a year later.