Recovering from blocking | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Recovering from blocking

I have a problem with my application when blocking occurs on the db I am not the only application using the db which is (sql2000 sp2(?) on win2000 sp4) The problem is I wait for an indication to my application from a stored procedure to tell me whether it has updated. i.e. BEGIN TRANSACTION UPDATE Table1
SET
Column1 = ‘D’
WHERE
Column2 = @myVAR
IF @@ROWCOUNT >0
BEGIN
COMMIT WORK
if @@error <>0
begin
SET @OUTPUTFIELD = 0
RETURN end
else
begin
SET @OUTPUTFIELD = 1
RETURN end
end
ELSE
BEGIN
ROLLBACK TRANSACTION
SET @OUTPUTFIELD = 0 END I think this is performing how it should now so that when blocking does occur and the commit fails when query timeout 600 is reached then we will get a rolllback and the application will report a 0 back to the application. I have just implemented the if @@error portion of the code and prior to that i was simply returning 1 like below BEGIN TRANSACTION
UPDATE Table1
SET
Column1 = ‘D’
WHERE
Column2 = @myVAR
IF @@ROWCOUNT >0
BEGIN
COMMIT WORK SET @OUTPUTFIELD = 1
RETURN end ELSE
Begin
ROLLBACK TRANSACTION
SET @OUTPUTFIELD = 0 END in this example the PROCEDURE would output a @outputfield value of 1 regardless of committing which would cause the application to move on. Rollback have occurred but the application would be none the wiser. Hope this makes sense.
What i guess i am asking is what other techniques or better techniques would you suggest to
handle this problem.

I think next code is what you are looking for:
UPDATE Table1
SET
Column1 = ‘D’
WHERE
Column2 = @myVAR
select @OutputField = case (@@rowCount)
when 0 then 1 –some rows are updated
else 0 –nothing is updated
end,
@ReturnValue = case (@@Error)
when 0 then 0 –successful update
else -1 –update failed
end
return @returnValue
or
UPDATE Table1
SET
Column1 = ‘D’
WHERE
Column2 = @myVAR
select @OutputField = @@rowCount [email protected] value is number of rows updated
@ReturnValue = @@error –Eror code will be returned return @returnValue
There is no need for explicit transaction when only one data maintenance operation (in this case update) is done. Single data maintenance operation is executed as implicit transaction.
thanks I have implemented a "set lock_timeout 5000" for all statements and have been catching the
error in the calling VB when the timeout occurrs.
This has proved a more stable approach than waiting for the error code to return by which time a queue of transaction would build up on the spid and trouble wouldn’t be far behind.
]]>