question

Doiremik avatar image
Doiremik asked

Point of update when using case in an Update statement

hi there, 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... UPDATE [OrderTable] SET RemainingOrderAmount = RemainingOrderAmount - ABS(@orderAmountFilled ), -- reduce the amount left to close ClosingTime = (CASE WHEN (RemainingOrderAmount - ABS(@orderAmountFilled)) = 0 THEN GETUTCDATE() ELSE NULL END) WHERE OrderID = @orderID 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
t-sqlupdatecase-statement
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder commented ·
looks like it should work, is there more to this that might be affecting the result?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
@Grant +1 - as you said, the values are both calculated at the same time and then updated. @Doiremik - This can be seen in the execution plan, there would be a table scan/clustered index scan/index seek or similar to find the OrderId, then a calculate scalar (with ALL values being calcualted at the same time) and then the update of the table (and any indexes that needed it too). Could you be running into concurrency issues?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
I agree with the above answers, consider this example

DECLARE @temp TABLE
(ID INT ,
 Amt INT,
 Dt DATE)
  INSERT INTO @temp
 SELECT 1,1000,GETDATE()

 UPDATE @temp
 SET Amt = Amt+1000,
 Dt = CASE WHEN Amt = 2000 THEN NULL ELSE GETDATE()END
  
SELECT * FROM @temp
--Result------------
ID	Amt	Dt
1	2000	2010-10-26
If the values are not modified together then the result Dt Column should be updated with NULL.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Doiremik avatar image
Doiremik answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Doiremik avatar image
Doiremik answered
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 -- Dont reduce the amount of VolumeOpened by the amount closed as it has already been reduced on the ScaleOutPosition. -- Position is closed when Opened = Closed UPDATE [Position] SET VolumeClosing = VolumeClosing - ABS(@VolumeAmount), -- reduce the amount left to close VolumeClosed = VolumeClosed + ABS(@VolumeAmount), -- add to the amount Closed FilledReturnValueOnClosing = FilledReturnValueOnClosing + (ABS(@VolumeAmount) * @ActualMarketExecutionPrice), QuotedMarketAskCostOnClosing = (CASE WHEN AccountBaseCurrency != SUBSTRING(Asset,5,3) -- if we are closing a long position and the Base currency doesnt match the Base Asset then we need to store the ask for conversions in RPL THEN (QuotedMarketAskCostOnClosing + (ABS(@VolumeAmount) * @QuotedMarketAskRate)) ELSE QuotedMarketAskCostOnClosing END), -- track the ask price. Used for RPL and only set on closing. Will be the same price as @ActualMarketExecutionPrice for long positions ClosingTime = (CASE WHEN (VolumeClosed + ABS(@VolumeAmount)) = TotalVolumeEverOpened THEN GETUTCDATE() ELSE NULL END) WHERE PositionID = @positionID
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
@Doiremik - is this happening consistently with just 4 records? If so, have you tried just selecting the values you show here instead of updating? I would be interested to see the result of that. Another question, why update one at a time, can't you do this in one? Maybe that could be a change enough to help out
0 Likes 0 ·
Doiremik avatar image Doiremik commented ·
@William - no william, this is intermittent from what I can see. If I select them, I would need to pump them to a file or something and then wait for the issue to arise again. As for updating one at a time, what actally happens is, I have a broker system that recieves fills for orders. They might be partially filled in which case only when there is nothing left to fill I set the Closed Time, if they get filled completely on the one go I update the closed Time on the order. So its handling orders as they stream into the system and I dont get a batch in the one go... if this makes sense... regards Mick
0 Likes 0 ·
Doiremik avatar image
Doiremik answered
OK... just want to throw this out there... is there any chance that a call to GETUTCDATE() could return NULL. I know its a bit mad to think but it would explain the issue alright. What I was going to do was clean down the database and try this again to see will it happen. It can take hours. If it does... then replace the GETUTCDATE with a harded coded value and then try again. Opinions very welcome... regards mIck
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
GETUTCDATE() cannot return NULL as far as I am aware. It just passes the time coming from the O/S - if that doesn't work, you're in a whole other world of pain!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.