0

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).
that0th3rGuy
  • 1,356
  • 1
  • 15
  • 19
  • 1
    You should avoid using `sp_` on the start of stored procedure names. There are warnings in the documentation about this being reserved for Microsoft's *system* procedures, and if there's a name clash (either now, or possibly if the system is updated later), the Microsoft one will "win". – Damien_The_Unbeliever Oct 02 '14 at 09:49
  • @Damien_The_Unbeliever That is interesting. Thanks for the heads-up. I was not aware of that and will do so in future - or should I say *not* do so. In this case, however, it was merely to distinguish between entity types. Your warning has been noted. – that0th3rGuy Oct 02 '14 at 10:00
  • You have concurrency between different transactions, right? Why do you think there is intra-transaction asynchrony? A race condition seems to explain this behavior well. – usr Oct 02 '14 at 10:22
  • Thanks for taking the time; I appreciate your input. I wasn't expecting SQL Server to behave in this way because the RBAR-loop is initiating the "call stack" to all the other procedures. It [the top-level, outer session] can't be concurrent to itself, can it? Maybe I'm not understanding you correctly. – that0th3rGuy Oct 02 '14 at 10:29
  • A single query to SQL Server never creates concurrency (this cna be annoying if you actually *want* parallelism). When you call a proc that call is synchronous and blocking. I'm nut sure whether you have said it or not, but most likely multiple transactions (in different sessions) are operating on the same data here. Try the SERIALIZABLE suggestion from the answer to test that theory. SERIALIZABLE provides the illusion of single-threaded execution. – usr Oct 02 '14 at 10:31
  • Precisely. I was expecting the call to be synchronous. But the first call to `[sp_GenerateID]` generates `ABC001` and the second call also generates `ABC001` while I am expecting it to generate `ABC002`. That tells me that the second call to `[sp_GenerateID]` was made before the first call to `[sp_GenerateID]` could call `[sp_UpdateIDPool]`. I tried the `ISOLATION LEVEL` suggestion; please see my comment on that answer. – that0th3rGuy Oct 02 '14 at 10:43
  • The procedure runs synchronously, period. There must be some bug in it. I did not understand from your comment what the result of your SERIALIZABLE test was. At this point there is not enough information to find the issue. Your best bet would be to create an executable repro by copying the database and deleting code until the issue is condensed to a few dozen lines. (Post any code in general would also help.) – usr Oct 02 '14 at 11:08
  • Show your are RBAR. And do you only have one active RBAR. That should synchronous. You need to have each sp write the sp_GenerateID, timestamp, and name of sp to a log table to prove that is happening. You have something else going on. – paparazzo Oct 02 '14 at 14:48
  • Why couldn't you use an identity field rather than calling a stored procedure to generate a unique id? – Kevin Cook Oct 03 '14 at 12:54

1 Answers1

0

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
Jim V.
  • 2,137
  • 16
  • 14
  • Thanks you for taking the time to respond. I tried the statement you suggested in a few different places but kept getting `Incorrect syntax near 'SERIALIZABLE'.` so I Googled it and got this: http://stackoverflow.com/questions/6342732/tsql-mutual-exclusive-access-in-a-stored-procedure – that0th3rGuy Oct 02 '14 at 10:14