November 29, 2012 at 6:24 pm
Looking for some outside advice on a problem we're having with a piece of code inside a stored procedure that is called many, many times an hour and is causing waits and locking issues.
The following is a somewhat simplified snippet of the problematic section. The actual SP is much longer, but is pretty straightforward. The SP is called from .NET code that is starting a transaction prior to this call and committing once it returns, if it returns with a valid return code. There is a fair amount of processing (mostly lookups) that occur AFTER the snippet below, which means it takes a bit for the entire SP to complete, which (if I understand correctly) means that the locks called for in the code below will not be released until the SP is complete and the calling .NET code has committed the transaction.
Here's the code in question:
declare @AccountBalance money
-- get the current balance on the account
select @AccountBalance = p.Balance
from PrepayAccount WITH (XLock, Rowlock)
WHERE PrepayAccountID = @AccountID -- @AccountID is a parameter to the SP
-- see if we have enough to process the transaction
if (@Amount > ISNULL(@PrepayBalance,0.00)) -- @Amount is a parameter to the SP also
BEGIN -- not enough!
RAISERROR (50044,16,1,'Prepaid')
return (-99)
END
-- enough, so go ahead and decrement
Update PrepayAccount
Set Balance = @PrepayBalance - @Amount,
UpdatedDate=getdate(),
UpdatedBy = @UpdatedBy
Where PrepayAccountID = @AccountID
I am wondering if it is (Safe / Good Practice / Faster / Crazy / Bizarre / Something else ) to consider a different approach that would require no locks (other than what SQL itself does in the course of an UPDATE). Something like the following:
-- do the update with a more sophisticated WHERE clause
Update PrepayAccount
Set Balance = Balance - @Amount,
UpdatedDate=getdate(),
UpdatedBy = @UpdatedBy
Where PrepayAccountID = @AccountID
AND (Balance >= @Amount) -- put the check for sufficient funds here
-- now, check if it failed (No rows affected)
if @@RowCount = 0
BEGIN -- not enough!
RAISERROR (50044,16,1,'Prepaid')
return (-99)
END
-- and we continue as before
I'm sure others have thought of this before. Does it work? Will this get around the locking/wait issues? Are there other, better alternatives? Are there any obvious Gotchas in my proposed solution?
Thanks in advance for any help!
Rob Schripsema
Propack, Inc.
November 29, 2012 at 6:49 pm
Besides the obvious update you show in your snippet above, are there other updates occuring in the stored procedure that are dependent on this update completing successfully?
It is really hard to give specific advice with only a partial understanding of the process.
November 30, 2012 at 2:54 pm
Yes, there are. That is, there are other inserts and updates that can only occur if there was sufficient funds in the account to cover the transaction.
However, is it not correct that the alternative code snippet I gave would still account for that? If there was insufficient funds, the IF (@@ROWCOUNT = 0) would cause the SP to exit (RETURN (-99) ). So those other actions would not take place, just as they don't take place now.
And if it PASSES the @@ROWCOUNT test, and one of the other actions failed (for some reason), then THOSE steps would exit the SP with an error value in the RETURN value and the calling code would rollback the transaction -- including the update shown in my code snippet. Or not?
Which begs the question: does an action like I've shown in the alternative code snippet (UPDATE WHERE BALANCE >= @AMOUNT) participate in a transaction such that it could be rolled back? And if so, does that mean it is still placing a lock on the updated record that won't get released until a COMMIT or ROLLBACK, in which case I'm not gaining anything by avoiding the explicit LOCK?
Rob Schripsema
Propack, Inc.
November 30, 2012 at 10:23 pm
I would go with the approach of having the check in the update statement.
Also, I would open the transaction in the stored procedure instead of the .net code and commit or rollback in the procedure so that you do not hold a transaction open waiting for the application server to commit it.
November 30, 2012 at 10:57 pm
Rob Schripsema (11/30/2012)
Yes, there are. That is, there are other inserts and updates that can only occur if there was sufficient funds in the account to cover the transaction.However, is it not correct that the alternative code snippet I gave would still account for that? If there was insufficient funds, the IF (@@ROWCOUNT = 0) would cause the SP to exit (RETURN (-99) ). So those other actions would not take place, just as they don't take place now.
And if it PASSES the @@ROWCOUNT test, and one of the other actions failed (for some reason), then THOSE steps would exit the SP with an error value in the RETURN value and the calling code would rollback the transaction -- including the update shown in my code snippet. Or not?
Which begs the question: does an action like I've shown in the alternative code snippet (UPDATE WHERE BALANCE >= @AMOUNT) participate in a transaction such that it could be rolled back? And if so, does that mean it is still placing a lock on the updated record that won't get released until a COMMIT or ROLLBACK, in which case I'm not gaining anything by avoiding the explicit LOCK?
First, I'd have a constraint on the column BALANCE where the value must be >= 0.00. I'd then use a TRY CATCH block to trap the constraint violation.
Next, I'd determine if the updates that followed, if they failed would also result in the main update rollong back as well. If so, they could be part of the same TRY CATCH block. Also, what failures could occur with the secondary updates? Can they be captured and dealt with internally with causing the main transaction from rolling back.
A rough (very rough) structure would look something like this:
BEGIN TRY
BEGIN TRANSACTION
Main Update
Secondary Update
Secondary Update
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK;
... Error code ...
END CATCH
Without really knowing what the procedure is doing, it is hard to give you a more specific answer.
I do agree with Michael that the transaction should start and stop inside the stored procedure.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply