SQL Server seems to behave in an async manner

Question

I know SQL statements don't execute asynchronously by default, but I have a situation that appears to behave that way.

Tables

• [#data]
• [tbl_Bucket]
• [tbl_IDPool]

Procedures

• [sp_InsertIntoBucket]
• [sp_GenerateID]
• [sp_UpdateIDPool]

Process

• An application calls [sp_InsertIntoBucket]
• [sp_InsertIntoBucket] calls [sp_GenerateID]
• [sp_GenerateID] queries [tbl_IDPool] and generates a value
• [sp_GenerateID] calls [sp_UpdateIDPool]
• [sp_UpdateIDPool] writes to [tbl_IDPool]
• [sp_GenerateID] returns its generated value to [sp_InsertIntoBucket]
• [sp_InsertIntoBucket] uses the value as a primary key for a new record in [tbl_Bucket]
• [sp_InsertIntoBucket] returns the generated value to the caller

Scenario

[#data] has information (1500 - 12000 records) destined for [tbl_Bucket]. Since [sp_InsertIntoBucket] can only handle one record at a time, the process is RBAR'ed - for each record in [#data] [sp_InsertIntoBucket] is called.

Problem

[sp_GenerateID] generates duplicate values. I've had anything from 13 to 130 duplicate generated values before the actual INSERT in [sp_InsertIntoBucket] occurs and the error is thrown.

The generated value is dependent on the data in [tbl_IDPool] and it is therefore also important that [sp_UpdateIDPool] be called for each [sp_GenerateID] call to ensure the next [sp_GenerateID] call generates a unique value.

I suspect it has something to do with [sp_GenerateID] being called a second time before [sp_UpdateIDPool] could finish writing to [tbl_IDPool]. But this doesn't make sense because the RBAR should wait for [sp_InsertIntoBucket] which should wait for [sp_GenerateID] which should wait for [sp_UpdateIDPool] before moving to the next [#data] entry, right?

What I've tried

• WAITFOR DELAY "00:00:00.003" - This works, but I'm looking for a better, more efficient, more elegant solution.
• WHILE vs. CURSOR - The only difference is that the CURSOR is slightly slower.
• With and without WITH (NOLOCK) in the [sp_GenerateID] query of [tbl_IDPool] hoping a write (first call) would lock a read (second call).

Answer 1

At the beginning of [sp_GenerateID] try specifying

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Then enclose the remaining contents in transaction block.

BEGIN TRANSACTION
...
COMMIT TRANSACTION