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
--xxxxxxxxxxxxxxxxxxxxxxxxxxx--
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name =
'ApplicationUsers')
BEGIN
PRINT 'Dropping Table ApplicationUsers'
DROP Table dbo.ApplicationUsers
END
GO
PRINT 'Creating Table ApplicationUsers'
GO
CREATE TABLE dbo.ApplicationUsers
(
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.ApplicationUsers
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 ConcurrencyHandler
--Set dataversion output parameter
Select
@dataVersion = DataVersion
From
dbo.ApplicationUsers
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
ConcurrencyHandler:
Rollback Tran LocalTran
If Exists (Select * From dbo.ApplicationUsers 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.ApplicationUsers
(LoginName, LoginPassword, LoginAttempts, EmailAddress)
Values
('blackmamba', 'Pwd1', default, 'b*@DIVAS.com')
Insert Into dbo.ApplicationUsers
(LoginName, LoginPassword, LoginAttempts, EmailAddress)
Values
('GoGo', 'Pwd2', default, 'g***@crazy88.com')
GO
/*
Call UpdateUser Stored Proc with current rowversion
*/
Declare @retVal int, @rowvrsn rowversion
--Get Current Row Version
select @rowvrsn = DataVersion from dbo.ApplicationUsers where LoginName
= 'blackmamba'
Exec @retVal = dbo.UpdateUser @loginName = 'blackmamba', @loginPassword
= 'UpdatedPwd', @loginAttempts = 0, @emailAddress = 'b*@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.ApplicationUsers where LoginName
= 'blackmamba'
--Simulate update by other process
Update dbo.ApplicationUsers Set LoginPassword = LoginPassword where
LoginName = 'blackmamba'
--Update User with out of date Rowversion
Exec @retVal = dbo.UpdateUser @loginName = 'blackmamba', @loginPassword
= 'UpdatedPwdVersion2', @loginAttempts = 0, @emailAddress =
'b*@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.ApplicationUsers where LoginName
= 'blackmamba'
--Simulate delete by other process
Delete From dbo.ApplicationUsers where LoginName = 'blackmamba'
--Update User with out of date Rowversion
Exec @retVal = dbo.UpdateUser @loginName = 'blackmamba', @loginPassword
= 'UpdatedPwdVersion2', @loginAttempts = 0, @emailAddress =
'b*@DIVAS.com', @dataVersion = @rowvrsn output
Print @retVal --Should be 66666 for row deleted by other process
