March 17, 2009 at 3:52 pm
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
OrderidOrderDate OrdererID
-------------------- ---------
512009-03-10 19:17:18.0738
522009-03-10 19:17:18.0734
502009-03-10 19:16:39.0134
492009-03-10 19:16:38.9978
482009-03-10 19:16:09.1834
472009-03-10 19:16:09.1708
462009-03-10 19:15:36.8234
452009-03-10 19:15:36.7938
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!
March 18, 2009 at 6:36 am
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
Consultant - Straight Path Solutions
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
March 18, 2009 at 7:20 am
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.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 7:31 am
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
Consultant - Straight Path Solutions
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
March 18, 2009 at 7:36 am
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
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 7:39 am
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?
March 18, 2009 at 10:51 am
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.
March 18, 2009 at 10:58 am
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.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 11:10 am
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.
March 18, 2009 at 11:15 am
"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.
March 18, 2009 at 11:21 am
Yes, I have been wanting to do that but have not been able to thus far as it is a PROD enviroment and because this happens sporadically. I might get permission to do it soon though.
Thanks!
March 18, 2009 at 11:22 am
The Identity field is not going to be created out of sequence. But two records next to each other could have the same creation time. You have to realise one thing about the time in SQL Server. When using getdate() to get the current time the time returned by this function is not exactly precise to a milisecond. I think it is about 13 or 16 miliseconds.
When you call getdate() in a loop you will notice the time is not changing smootly but jumps every 13 or 16 miliseconds.
Another funny part is that depending on the client who was calling the SQL sometimes the later call had earlier time in the record. I do not think this is going to be case in your scenario though.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 18, 2009 at 11:56 am
I would also double check the C# code that the execution order is as expected.
Someone more knowledgeable than I might be able to answer this, but I wonder if this could be caused by parallelism?
Jack Corbett
Consultant - Straight Path Solutions
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
March 18, 2009 at 12:10 pm
Jack, that is what I was wondering too but I would not want to change the setting in the database.
If I look at the configuration related to parallelism on the server , this is what I have:
name: max degree of parallelism
minimum:0
maximun:64
config_value:0
run_value:0
name: cost threshold for parallelism
minimum:0
maximun:32767
config_value:5
run_value:5
Does Anybody think it needs changing?
Thanks!
March 19, 2009 at 1:41 am
Would it be possible to change the calling procedure to handle the double call by two parameter sets?
Like
CREATE PROCEDURE [dbo].[Add_Order] (
@4FileID int null,
@4OrdererID int null,
@8FileID int null,
@8OrdererID int null
)
and handle the insert with just one proc? If both order ID's refer to the same file ID you could use just that one.
From my perspective this would reflect the subject a little more, since it is the users choice to select 4, 8 or both.
The call of two separate procedures do not really reflect the business case from my point of view...
That wouldn't answer your question regarding the duplicate timestamp, but might help to solve the business case...
Another option could be using service broker to force the order of running the inserts.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply