I have cut this down to keep it simple but here goes...
I have a table with 4 columns - OrderID, InitialOrderAmount, RemainingOrderAmount, CompletedDate
Rule is when the remaining order amount is 0, set the completed date.
I have a stored proc that takes in the ID, and the an order amount filled so I want to substract the amount filled from the remaining amount and if its 0 set the completed date.
my code is something like this...
ok so what I believed would happen is that even though the update statement updates the RemainingOrderAmount first, the case statement has to test by subtracting the amount as the first part of the update hasnt been committed... so I need the current value and subtract the amount filled.
Is this correct.
The reason I ask, is that in a test run... 13000 records updated fine but 4 had the maths right in that the RemainingOrderAmount was set to 0 but the completed date didnt get set.
Any ideas... thanks Mick
It's all part of a single transaction, so the values are not being modified first, they're being modified at the same time. Any reads are gathered and you won't see the affects of writes until they're committed. You'd probably need to break this down into two statements within a transaction to make it work.
answered Oct 26, 2010 at 05:03 AM
Grant Fritchey ♦♦
Thinking about it some more, it might even be a valid place for a trigger, although, I'd still rather see it in two, controlled steps. I'm not a fan of triggers.
answered Oct 26, 2010 at 05:04 AM
Grant Fritchey ♦♦
I agree with the above answers, consider this example
If the values are not modified together then the result Dt Column should be updated with NULL.
answered Oct 26, 2010 at 05:11 AM
thanks lads. yes indeed I did think on a trigger but the actual implementation is a bit more complex and thats why I do it in the update instead of a trigger as I have some more business rules.
I just want to check was there some mad bug or something that I didnt know about. I have had 13000 records all get updated and set correctly except 4 of them and they are the same as the rest.
@Grant. Yes indeed Grant I did think there was a concurrency issue but when I checked the logs I keep, I'm only updating this one row at a time as I use the PK ID to do it and its only in my logs once. I also catch exceptions if there is a lock and then retry the operation and this looks clean to.
However Grant, is it not the same code if I break it out into two statements except that I have to do a round trip to the database. Thats why I built it into the case, instead of another update statement.
Worst thing is, that its obviously a fluky thing and trying to track this down will be fun.
@Cyborg - nice clean example cyborg... thanks for the confirmation also regards Mick
answered Oct 26, 2010 at 07:14 AM
ok, you asked for it lol..
here is the actual statement. It does basically what the other simple statement does. I get in a value which can be negative or positive but I just use the abs on it. I'm closing out volume and in this case I know its a close.
So I subtract the @VolumentAmount from the amount closing.. add it to the amount closed.. do some working out on pricing etc and then set the ClosingTime. The worst thing is that all the other values are all correct for the 4 values that didnt have their ClosingTime set when it should have.
I have this enclosed in a try catch also so I would hope any locks or concurrency issues would be caught. The reason I dont think its concurrency is that the other values are all correct and I get no errors. I also know that only one call was made to update this record.
So should I put in an error check and test if the date is not null and throw an exception. Just for the craic, I'm going to run the values through the database to test... might take an hour or so... thanks again... a very confussed Mick
answered Oct 26, 2010 at 07:34 AM