Handling SQL Server concurrency issues

Sometimes I need to acquire a unique ID and store it with a record, but I am unable t开发者_StackOverflowo use an identity column. So instead I have a table which provides unique IDs using a label field and an integer. When a unique ID is needed, I call a stored procedure and pass in the label, and it spits out the next ID associated with it. Of course it's important for this to be reliable in an environment with concurrent transactions. That is, the stored procedure should never return the same value twice for a given label. My limited understanding of transaction isolation has led me to do the following:

1) Set transaction isolation level to serializable

2) SELECT id FROM UniqueIdTable WHERE label = @inputLabel

3) UPDATE UniqueIdTable SET id = id + 1 WHERE label = @inputLabel

4) Return the id retrieved in 2)

But is this actually safe? Isn't it still possible for two threads to concurrently execute up to step 2), even with serializable isolation? It's my understanding that the highest isolation level only guarantees that a single transaction will execute without experiencing phantom rows or changing data from other threads. If this is the case, two simultaneous calls to the GetID function could return the same value.

Am I misunderstanding something about the isolation levels? How can I guarantee this won't occur?

I have another problem I need to sort out. Suppose I have a table with a field in it which holds foreign keys for a second table. Initially records in the first table do not have a corresponding record in the second, so I store NULL in that field. Now at some point a user runs an operation which will generate a record in the second table and have the first table link to it. This is always a one-to-one relationship, so if two users simultaneously try to generate the record, a single record is created and linked to, and the other user receives a message saying the record already exists. How do I ensure that duplicates are not created in a concurrent environment?

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

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

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.