Out Of Sequence OrderID generated after blocking.
I have a table called tableOrder with the following columns:
OrderID int identity(1,1)
orderdate datetime -- Has a default getdate() so we don't need to insert a date through the proc
Here is the procedure to add orders to the table.
CREATE PROCEDURE [dbo].[Add_Order] (
SET NOCOUNT ON
Declare @NewOrderID int
Declare @Error int
-- tableOrder has an identity on OrderID, so the next OrderID gets generated that way.
Insert dbo.tableOrder (
Select @NewOrderID = SCOPE_IDENTITY(), @Error = @@ERROR
IF @ERROR <> 0
BEGIN ROLLBACK TRANSACTION
RAISERROR ('Error inserting new Order', 16, 1 )
If ( (@NewOrderID is NOT NULL) AND (@NewOrderID > 0) )
-- Inserts a record to another table one of the columns being the @NewOrderID created above
-- Does error check and if a problem then Rollback as above
SELECT @NewOrderID AS OrderID
This table is being populated by executing procedure Add_Order. What is happening is that when many orders are being created by different users by calls to this procedure and the tableorder has some blocking on it, the orderIDs returned are out of sequence. I am able to reproduce this in the following way:
I go on one machine and connect to the database server and do the following:
set OrdererID = 555
Now the table tableOrder is locked, I have not rolled back or commited the tran.
I then Execute from two different client machines hitting the same database server that has the proc Add_Order.
I first execute it from machine 1:
Exec Add_Order @FileID=1, @OrdererID=8
Next,I execute it from machine 2:
Exec Add_Order @FileID=1, @OrdererID=4
Both the procedures in their result query show executing query and it has not completed in either as the table is
I rollback tran. Now both procedures complete and send back the OrderID. The PROBLEM is that the OrderIDs are now out of sequence.
I had executed Exec Add_Order @FileID=1, @OrdererID=8 first and Exec Add_Order @FileID=1, @OrdererID=4 second, I got the result from the second one first and then from the second.
The problem seems to occur when there is a little block and a previous transaction is holding the table for a liitle while. When the blocking is over the two procedures try to
complete, what is interesting is that every time I tried this, the procedure that I called later completed first and had the smaller OrderID, although when we call the same procedure twice we would expect to see results from the first call first. Have others experienced such behaviour after blocks as well.
When I tried to check the last 8 orders created, result below, there are two important things I see:
Under normal circumstances as was the case for Orders 45 to 50, all looks good and OrdererID 8 fires first and then OrdererID 4, please see the sequence in column OrdererID below.
But for OrderID 51 and 52 the one where I created the block, the sequence has switched. One thing to
note is that they have the exact same creation time. So it seems when they have the exact same
creation time then we may get OrderID 52 before getting OrderId 51
select top 8 * from tableOrder (nolock) order by OrderDate desc
Orderid OrderDate OrdererID
------ -------------- ---------
51 2009-03-10 19:17:18.073 8
52 2009-03-10 19:17:18.073 4
50 2009-03-10 19:16:39.013 4
49 2009-03-10 19:16:38.997 8
48 2009-03-10 19:16:09.183 4
47 2009-03-10 19:16:09.170 8
46 2009-03-10 19:15:36.823 4
45 2009-03-10 19:15:36.793 8
The problem is that the middle-tier expects the OrderIDs to come back in a way that Ordererid 8 comes back first and then Ordererid 4.
They come back in pairs, but what is happening for OrderIDs 51 and 52 is that 4 comes first and then 8.
Can something be done to the procedure to address this problem? is there possibly a setting at the Sql Server level to take care of this? Only thing I could do and that may in fact create more blocking and make the problem worse, is to put a waitfor delay of say 500 millisecs when Ordererid = 4. That way even if both OrdrererID 8 and 4 are firing at about the same time OrdererID 4 will wait a little. This
is not neat though.
Thanks for any suggesrions!