Concurrency Handling In Stored Procedures

[xAvailx]

I have a requirement that requires detection of rows deleted/updated by
other processes. My business objects call stored procedures to create,
read, update, delete data in a SQL Server 2000 data store. I've done
a fair amount of research on concurrency handling in newsgroups and
other resources. Below is what I've come up as a standard for
handling concurrency thru stored procedures. I am sharing with everyone
so I can get some comments (pro/con) regarding this approach and see if
anyone can find any holes for this solution.

Below is the DDL, DML and a Stored Proc demonstrating the approach. I
am using a rowversion column for concurrency checking. Another approach
that is less intrusive (doesn't require having a rowversion column in
all tables) is using checksum. I may eventually use checksum but the
process flow should be almost identical. Looking forward to anyone's
comments.

Thx, BZ

--xxxxxxxxxxxxxxx xxxxxxxxxxxx--

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name =
'ApplicationUse rs')
BEGIN
PRINT 'Dropping Table ApplicationUser s'
DROP Table dbo.Application Users
END
GO

PRINT 'Creating Table ApplicationUser s'
GO

CREATE TABLE dbo.Application Users
(
LoginName varchar (20) NOT NULL Primary Key,
LoginPassword varchar (50) NOT NULL,
LoginAttempts int NOT NULL default(0),
EmailAddress varchar(25) NOT NULL Unique,
DataVersion rowversion NOT NULL
)

GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name =
'UpdateUser')
BEGIN
PRINT 'Dropping Procedure UpdateUser'
DROP Procedure dbo.UpdateUser
END

GO

PRINT 'Creating Procedure UpdateUser'
GO

Create Procedure dbo.UpdateUser
@loginName Varchar (20),
@loginPassword Varchar (50),
@loginAttempts Int,
@emailAddress Varchar(25),
@dataVersion Rowversion Output
As


/*************** *************** *************** *************** *************** ***
** Name: dbo.UpdateUser
** Desc: Updates an Application User instance
**
** Parameters:
** Input
** @loginName
** @loginPassword
** @loginAttempts
** @emailAddress
** @dataVersion. row version used for concurrency control.
**
** Output
** @dataVersion. updated row version used for concurrency
control.
**
** Return
** 0 for Success. Error code if any are encountered
** 66661 if current row version doesn't match provided version
** 66666 if expected row is not found
**

*************** *************** *************** *************** *************** ****/

Set NoCount On

Declare
@err Int,
@rowCount Int,
@tranCount Int

--Transaction Handling
Select @tranCount = @@TRANCOUNT
If (@tranCount=0)
Begin Tran LocalTran
Else
Save Tran LocalTran

Update
dbo.Application Users
Set
LoginPassword = @loginPassword,
LoginAttempts = @loginAttempts,
EmailAddress = @emailAddress
Where
LoginName = @loginName
And
DataVersion = @dataVersion

--Check for errors and rowCount (Should have updated 1 row)
Select @err = @@ERROR, @rowCount = @@ROWCOUNT
If (@err != 0) GOTO ErrHandler
If (@rowCount != 1) GOTO ConcurrencyHand ler

--Set dataversion output parameter
Select
@dataVersion = DataVersion
From
dbo.Application Users
Where
LoginName = @loginName

/*
If we got this far then there were no errors
If this proc started transaction then commit it,
otherwise return and let caller handle transaction
*/
If (@TranCount = 0)
Commit Tran LocalTran

Return 0

/*
Rollback local transaction if an error was encountered.
Return code is used to communicate error number.
*/

--Handle Concurrency Errors
ConcurrencyHand ler:
Rollback Tran LocalTran

If Exists (Select * From dbo.Application Users where LoginName =
@loginName)
Return 66661 --Row version doesn't match provided version
Else
Return 66666 --Row not found

--Handle Other Errors
ErrHandler:
Rollback Tran LocalTran

Return @err --Return Err Number

GO

PRINT 'Inserting Test Data...'

--Add Test Data
Insert Into dbo.Application Users
(LoginName, LoginPassword, LoginAttempts, EmailAddress)
Values
('blackmamba', 'Pwd1', default, 'bm@DIVAS.com')

Insert Into dbo.Application Users
(LoginName, LoginPassword, LoginAttempts, EmailAddress)
Values
('GoGo', 'Pwd2', default, 'gogo@crazy88.c om')

GO

/*

Call UpdateUser Stored Proc with current rowversion

*/
Declare @retVal int, @rowvrsn rowversion

--Get Current Row Version
select @rowvrsn = DataVersion from dbo.Application Users where LoginName
= 'blackmamba'

Exec @retVal = dbo.UpdateUser @loginName = 'blackmamba', @loginPassword
= 'UpdatedPwd', @loginAttempts = 0, @emailAddress = 'bm@DIVAS.com',
@dataVersion = @rowvrsn output

Print @retVal --Should be 0 for success

GO

/*

Call UpdateUser Stored Proc with out of date rowversion (simulate
update by other process)

*/
Declare @retVal int, @rowvrsn rowversion

--Get Current Row Version
select @rowvrsn = DataVersion from dbo.Application Users where LoginName
= 'blackmamba'

--Simulate update by other process
Update dbo.Application Users Set LoginPassword = LoginPassword where
LoginName = 'blackmamba'

--Update User with out of date Rowversion
Exec @retVal = dbo.UpdateUser @loginName = 'blackmamba', @loginPassword
= 'UpdatedPwdVers ion2', @loginAttempts = 0, @emailAddress =
'bm@DIVAS.com', @dataVersion = @rowvrsn output

Print @retVal --Should be 66661 for rowversion mismatch

GO

/*

Call UpdateUser Stored Proc with out of date rowversion (simulate
delete by other process)

*/

Declare @retVal int, @rowvrsn rowversion

--Get Current Row Version
select @rowvrsn = DataVersion from dbo.Application Users where LoginName
= 'blackmamba'

--Simulate delete by other process
Delete From dbo.Application Users where LoginName = 'blackmamba'

--Update User with out of date Rowversion
Exec @retVal = dbo.UpdateUser @loginName = 'blackmamba', @loginPassword
= 'UpdatedPwdVers ion2', @loginAttempts = 0, @emailAddress =
'bm@DIVAS.com', @dataVersion = @rowvrsn output

Print @retVal --Should be 66666 for row deleted by other process

[Erland Sommarskog]

Re: Concurrency Handling In Stored Procedures

xAvailx (bjzamora@hotma il.com) writes:[color=blue]
> Below is the DDL, DML and a Stored Proc demonstrating the approach. I
> am using a rowversion column for concurrency checking. Another approach
> that is less intrusive (doesn't require having a rowversion column in
> all tables) is using checksum. I may eventually use checksum but the
> process flow should be almost identical.[/color]

I would advice against using checksum. The checksum functions in SQL Server
are simple XOR, and the probability that two different rows are not
detected is not negligible.

rowversion is fine.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[xAvailx]

Re: Concurrency Handling In Stored Procedures

Thanks for the checksum vs rowversion feedback, Erland.

Do you see any issues/gotchas with the implementation?

Thx, BZ