Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Out Of Sequence OrderID Generated after Blocking Expand / Collapse
Author
Message
Posted Tuesday, March 17, 2009 3:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:16 AM
Points: 31, Visits: 107
Out Of Sequence OrderID generated after blocking.

I have a table called tableOrder with the following columns:

OrderID int identity(1,1)
FileID int,
OrdererID int,
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] (
@FileID int,
@OrdererID int
)
AS
SET NOCOUNT ON

Declare @NewOrderID int
Declare @Error int

-- tableOrder has an identity on OrderID, so the next OrderID gets generated that way.
BEGIN TRAN
Insert dbo.tableOrder (
FileID,
OrdererID
)
Values (
@FileID,
@OrdererID
)

Select @NewOrderID = SCOPE_IDENTITY(), @Error = @@ERROR
IF @ERROR <> 0
BEGIN ROLLBACK TRANSACTION
RAISERROR ('Error inserting new Order', 16, 1 )
RETURN -1
END

If ( (@NewOrderID is NOT NULL) AND (@NewOrderID > 0) )
Begin
-- 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
End
COMMIT TRAN
SELECT @NewOrderID AS OrderID
Return

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:

Begin tran
update tableOrder
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
locked.

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!









Post #677954
Posted Wednesday, March 18, 2009 6:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 10,340, Visits: 13,341
I don't think there is a way to force the inserts to happen in a certain order. If you have to have the data returned in a specific order you should specify that in your ORDER BY. So in the case you present your select should be:

select top 8
*
from
tableOrder (nolock)
order by
OrderDate DESC,
OrdererID





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #678354
Posted Wednesday, March 18, 2009 7:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
I do not know if Jack's suggestion is going to work for you because it does not look from the presented sample data like the pairs are tied up by date. It looks like only the top pair has the same datetime values.
I think your problem is somewhere in the design of the database and a wrong assumptions made about the order of execution. The question is why and how do you pair the orders. I think you may have to create an explicit pairing mechanism instead of implicitly relying on the order of records being stored.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #678413
Posted Wednesday, March 18, 2009 7:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:42 PM
Points: 10,340, Visits: 13,341
Ahh, thanks Jacek for catching my omission. I only looked at the first "pair" and they were tied by date so I assumed the rest were as well.

I also do not understand why the middle tier would care what order they are returned in. I mean how can you guarantee that Orderer 4 will always place orders before Orderer 8? In theory, Orderer 8 could be a more efficient worker and enter 4 orders in 5 minutes while Orderer 4 does 2. Then the order may be

8  10:15:10
4 10:15:11
8 10:16:29
4 10:17:01
8 10:18:01
8 10:19:30





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #678431
Posted Wednesday, March 18, 2009 7:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
Yep, I do not see the logic behind this as well so it is hard to give a valueable answer. I noticed that most issues people have with the SQL code are directly related the the incorrect database design. I am affraid this might be one of them

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #678445
Posted Wednesday, March 18, 2009 7:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 8:09 AM
Points: 51, Visits: 118
Since you have already tried to debug and provided so much information, I am just curious to know whether you tried running the procedure which you have run on machine 1 in machine2, and the procedure which you ran on machine2 on machine1. Did you get the same result?
Post #678449
Posted Wednesday, March 18, 2009 10:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:16 AM
Points: 31, Visits: 107

I think I need to clarify that I am able to reproduce in a round about way the problem that is happening when the application is running. I did this by just firing the stored procedure in the backend and creating a blocking of the table.

What is happening in the application is that on a certain page in the application a user has the choice to have one order created with Ordererid = 4, or 8 or two orders created by selecting both 4 and 8. Whwn they select only 4 or 8 the stored proc is called only once and so there is no problem. When they select the combination 4 and 8 the procedure is called twice once with Ordererid = 8 followed immediately with a call with Ordererid = 4. Now they expect to get the orderid's back in that sequence the one for 8 first and the one with 4 later. But when there is a little blocking, they get the results in the reverse order. One thing that I noticed was that whewnever this happens, the Orderdate on the two orders is identical, upto the millisecond. My first question is: why are the dates identical upto the millisecond? Can we do any thing with any setting so these two orders fired through the same login by the same person one afetr the other in the code return results in the right sequence.

Sorry for confusing you by making it look like two different users are involved, I had to do that to create a block and simulate the problem in the backend.

I am telling the developers that we should return the ordererid and the orderid from the proc and they should look for the ordererid as well as the orderid in the result being returned and then they would always associate them correctly regardless of the order in which they are returned. what they are doing is just looking at the orderid returned and assuming the first one is for 8 and the second for 4.
Do you think this is a good suggedtion. What do I tell them when they say : How come two Orders created have the exact same time of creation upto the milliseconds, when they were called one after the other in the code.

Thanks.



Post #678711
Posted Wednesday, March 18, 2009 10:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
What is the front end to this? A web application? What I do not understand is if the calls are made sequentially or not. If the calls are made sequentially then they do not block each other.
And yes, if the OrdererID is important have it returned and use it to identify the records.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #678719
Posted Wednesday, March 18, 2009 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:16 AM
Points: 31, Visits: 107

Yes it is a web application and the code calling the procedure is c#. Typically there are many users on the system doing this kind of order creation when this happens, it does not happen all the time. I am not a 100% sure that there is blocking when this problem occurs, but what I did was try to get two sequential orders get the same time of creation upto the millisecond and I was able to do that by creating a block with the process I had posted earlier. Also, because I have seen that this happens when different users are using this option about the same time, I assumed that the one having the problem must have had a little block from a previous user and then both his procedure calls land up completing at the same time. Do you think that may not be the reason, is it possible that even with no blocks on the table we could get the orderid's created at the exact same time and identity created OrderID returned out of sequence?

Thanks.



Post #678734
Posted Wednesday, March 18, 2009 11:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 8:09 AM
Points: 51, Visits: 118
"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."

Could you run the profiler and see which procedure is executed first? I don't see any reason why it should return the orderID in reverse sequence.
Post #678743
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse