concurrency error not caught. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

concurrency error not caught.

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2013-10-24 : 15:52:55

I recently changed the way I handle my insert/updates after looking at some samples on this site regarding try/catch and transaction placement. I tested this and I realize that it does not pick up concurrency issues. Can someone tell me why this is happening?
I cannot figure it out. I tested for other errors like divide by zero and that is caught but concurrency is not. Instead, the stored procedure executes and returns zero.

Thanks.

PROCEDURE [dbo].[uspUpdate_tblCustomer]
( @CustomerID int ,
@IsActive bit ,
@CustomerCode varchar ( 25 ),
@DateEntered [datetime],
@DataEntryUser [varchar](50),
@DateLastModified [datetime] OUTPUT,
@DataModifiedUser [varchar](50),
@Sys_Ts TimeStamp,
@Sys_TsOut TimeStamp OUTPUT)

AS
declare @error int
set @error = 0
SET XACT_ABORT ON
SET ARITHABORT ON
SET NOCOUNT on
BEGIN TRANSACTION
BEGIN TRY
set @error = 1

Update [tblCustomer] set [IsActive] = @IsActive , [CustomerCode]= @CustomerCode ,
[DateEntered] = @DateEntered,
[DataEntryUser] = @DataEntryUser,
[DateLastModified] = CURRENT_TIMESTAMP,
[DataModifiedUser] = @DataModifiedUser

WHERE (CustomerID = @CustomerID
AND Tstamp = @Sys_Ts);

select @sys_TSOut = TStamp from tblCustomer where CustomerID = @CustomerID
select @DateLastModified = DateLastModified from tblCustomer where CustomerID = @CustomerID
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);

-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Build the message string that will contain original
-- error information.

SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();

insert dbo.Application_Error_Log (UserName, ErrorProcedure, ErrorTime,
errorNumber, errorSeverity, errorState, ErrorLine, errorMessage)
values (suser_sname(), @ErrorProcedure,CURRENT_TIMESTAMP, @ErrorNumber,
@ErrorSeverity, @ErrorState,@ErrorLine, 'At Statement # ' + rtrim(Convert(char(5), @Error)) + ': ' + ERROR_MESSAGE() )

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

IF @@TRANCOUNT > 0
COMMIT TRANSACTION
select @sys_TSOut Newsys_TS
select @DateLastModified NewDateLastModified

RETURN 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-24 : 16:03:55

What do you mean by "concurrency issues"?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-24 : 16:13:51

I don't know what a concurrency issue is in this case, but the Commit Transaction is in the wrong spot in my opinion. I think it should be at the end of the TRY block not outside of it.

smh
Yak Posting Veteran

94 Posts

Posted - 2013-10-24 : 17:04:26

If I am editing a value in one application and editing the same record and same field in another application, I should get an error. Or if I change the value in one application while the other is open and then change the same field (which has changed because it was changed in the other application while this record was open in this one), I should get an error because the timestamps don't match.

Sorry if I used the wrong words to explain this.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 17:32:32

I don't see anything in your code where you are comparing timestamps and rolling back or raising error in the normal program flow. Anything that you have in CATCH block (between BEGIN CATCH and END CATCH) will be invoked ONLY if there is an error generated within the TRY block. In your code, you are updating a table and doing two selects in the TRY block - none of which would be able to detect, much less raise an error for the sort of concurrency issue you are describing.

smh
Yak Posting Veteran

94 Posts

Posted - 2013-10-24 : 17:57:26

where would I put the commit transaction to insure all possible errors were caught and if I was running multiple updates/inserts that they would all be rolled back. I am afraid my technique is a work in progress over the years.

I put the try block within the transaction and I was not sure exactly what to do with the rollback and commits. I had done it this way because I read this was better than the transaction with in the try for rolling back when there are multiple inserts/updates, etc. in the same stored procedure and this has worked well for that. I also have output parameters and I was not sure where to put them, but this works ok. It is just that it does not cause an error if the timestamps in the record in the database is not the same as the one on the editing form where the query is called. So for some reason, it is not capturing this. I used to check for a value and return that if the timestamps did not match, but this method did not rollback all actions done in the same stored procedure and I think other things were wrong. Here is a sample of the way I used to do it.

PROCEDURE [dbo].[uspDelete_tblProductPriceList]
@CID int

AS

DECLARE @tran_status int

SET NOCOUNT OFF;
BEGIN TRY

BEGIN TRANSACTION
DELETE FROM [tblProductPriceList] WHERE [ProductPriceListCodeID] = @CID

COMMIT TRANSACTION

return(0)
END TRY

BEGIN CATCH
if (@@ROWCOUNT=0)
select @tran_status = -11111

if (@@error <> 0)
select @tran_status =@@error

if (XACT_STATE() = -1)
ROLLBACK TRANSACTION
else
if (XACT_STATE() = 1)
COMMIT TRANSACTION

/* Log the error
insert dbo.Application_Error_Log (UserName, tableName,
errorNumber, errorSeverity, errorState, errorMessage)
values (suser_sname(), @tableName, ERROR_NUMBER(),
ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE()) */
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);

-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.

RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);

return @tran_status
END CATCH

smh
Yak Posting Veteran

94 Posts

Posted - 2013-10-24 : 18:06:37

Hi James,

Why does this code not trigger an error: WHERE (CustomerID = @CustomerID
and Tstamp = @Sys_Ts).

As you can see in the post I just sent above, I use to use a value (-1111) with in the stored procedure to pass it there was this error. Then I changed that to a user error number which I passed: IF @@ROWCOUNT = 0
RAISERROR('ConcurrencyError ',16,1, -1111)

But this often was not consistent. I am afraid that when I figured out a way to deal with the other problems, I failed to test this adequately. Is there a way I can keep this as is but still test the timestamps.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-24 : 18:30:30

quote:
Originally posted by smh

Hi James,

Why does this code not trigger an error: WHERE (CustomerID = @CustomerID
and Tstamp = @Sys_Ts).

Assuming the syntax is right, that is not an error. That predicate simply will not be true so it would not match to any rows.

If you wanted to check for the existence of rows using an EXISTS clause or something, and, if no rows matched your predicate, then you raised an error. You could do something like that.

Guessing by what you have posted so far there is a slight disconnect on how TRY..CATCH blocks work or how they are used to control execution.

So is what you are really after is a way to prevent two applications from updating the same row or at least some mechanism to inform an application that another application has made a change and that updating the data may result in loosing previous changes?

If so, then that is Concurrency and there are many ways to handle it. Probably, the most popular/widely used method is Optimistic Concurrency.

Here is a link about that:
http://msdn.microsoft.com/en-us/library/bb404102.aspx

smh
Yak Posting Veteran

94 Posts

Posted - 2013-10-24 : 19:03:37

James, I see these samples and have used the Microsoft sql builder in the example. But the example does not show how to check for the error in the catch statement.)In my example, I only need to check the timestamp because that always changes when the row is updated and is adequate for what I need. I had used the checking of the rowcount (if (@@ROWCOUNT=0) select @tran_status = -11111) and then checked for that number in the .net code, but if I am doing multiple execution in the same stored procedure, this has to be checked with each one and I could not get the multiple rollback to work with it.

Thanks for your help on this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources