Checksum not working

[ajamrozek]

I'm developing a stored procedure to run an update based on values
entered into a .Net web form. I want to capture the chceksum of the
row when it is displayed on the form then validate that when the update
is exec'd. Simple enough logic, eh? The problem is when I try to use
the checksum(*) function, SQL server yells at me and says that it isn't
recognized. I'm using SQL Server 7, so wtf? I am not the admin of the
server and I'm skirting around SQL Server Enterprise Manager and using
any free utils, MS Access, and Visual Studio to maintain this db.
Thanks
Alex Jamrozek

[David Portas]

The CHECKSUM function is specific to SQL Server 2000. It doesn't exist
in 7.0.

It seems like you are trying to implement optimistic locking on your
form. For that you can use a TIMESTAMP column in your table to
determine whether the data has changed.

--
David Portas
SQL Server MVP
--

[ajamrozek]

Thanks David.
I'm somewhat of a noob to SQL Server. I thought 7.0 supported that.
So let me see if I understand this TIMESTAMP logic. I would update the
row's TIMESTAMP column to GetDate() whenever the row was accessed, then
check that value again when the web form exec's its update. If it's not
the same as it was when the row was originally accessed, then the web
form's update should fail. Does this sound right?

[David Portas]

TIMESTAMP is just a binary value, not a date and time, and it can't be
updated directly, it just increments whenever the row is updated. What
you have to do is retrieve the TIMESTAMP when you read the row and then
compare to the current value in the table before you do the update. If
the two values are different then the row has been updated.

Be aware that a different TIMESTAMP doesn't necessarily mean the row's
*data* has changed - an update might have touched the row but just
replaced a value with the original value. Typically that isn't much of
a problem because the situation is fail-safe - you'll block an update
that might have been valid rather than allow an update against dirty
data. CHECKSUM in SQL Server 2000 is a different matter. It isn't a
reliable way to implement optimistic locking because it's perfectly
possible (in fact it's quite common) for two *different* sets of values
to give the *same* CHECKSUM value.

The only way to detect for certain if data has changed is to compare
every retrieved value with the columns in the table. You can do this in
a stored procedure by passing both the old and new values as
parameters. The following works assuming none of the columns is
nullable:

BEGIN TRAN

UPDATE YourTable
SET
col1 = @new_col1,
col2 = @new_col2,
col3 = @new_col3
WHERE
col1 = @old_col1
col2 = @old_col2
col3 = @old_col3

IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
RAISERROR('Concurrency error',16,1)
ELSE
COMMIT TRAN
END

Hope this helps.

--
David Portas
SQL Server MVP
--

[Hugo Kornelis]

On 31 Mar 2005 11:10:14 -0800, ajamrozek wrote:

Thanks David.
I'm somewhat of a noob to SQL Server. I thought 7.0 supported that.
So let me see if I understand this TIMESTAMP logic. I would update the
row's TIMESTAMP column to GetDate() whenever the row was accessed, then
check that value again when the web form exec's its update. If it's not
the same as it was when the row was originally accessed, then the web
form's update should fail. Does this sound right?

Hi Alex,

Actually, no. You never update the timestamp column. SQL Server will
change it's value whenever the row is inserted or updated. All you need
to do is to store the value from the timestamp column when you read the
data, then check if it is unchanged when you do the update. Somthing
like this:

SELECT @Var1 = Col1, @Var2 = Col2, ..., @TSvalue = TSColumn
FROM YourTable
WHERE KeyCol = @KeySelected

(Do things with the data - probably including showing it to the end user
and waiting for input)

UPDATE YourTable
SET Col1 = @Var1, @Col2 = @Var2, ...
WHERE KeyCol = @KeySelected
AND TSColumn = @TSValue
IF @@ROWCOUNT = 1
BEGIN
RAISERROR ('The row has been changed - update refused', 16, 1)
RETURN -1
END
(Note - the name timestamp is misleading. The values stored have no
relation at all with date and/or time of update. Since SQL Server 2000,
the datatype rowversion has been added as an alias to timestamp. I hope
that MS will decide to drop the old timestamp name in a future version).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[ajamrozek]

Thanks both to David and Hugo. You guys have educated me greatly.

[ajamrozek]

Ok so here's my stored procedure:
--------------------------------------
(
@NSIR_ID INT,
@PRODUCT_DESCRIPTION NVARCHAR(255),
@ACCESS_TIMESTAMP TIMESTAMP
)
AS
BEGIN TRAN

UPDATE ajamroz.IT_NSIR
SET PRODUCT_DESCRIPTION = @PRODUCT_DESCRIPTION
WHERE ID = @NSIR_ID AND ACCESS_TIMESTAMP = @ACCESS_TIMESTAMP

IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
SELECT 'NSIR: ' + CAST(@NSIR_ID AS NVARCHAR(5)) + ' has changed since
you opened it, so your changes could not be recorded. Please close
this window and select the record again to get the latest changes.'
END
ELSE
COMMIT TRAN
----------------------------------
All updates are still executing though. I tried to make this thing
throw the error and it didn't. It seems like @@ROWCOUNT is never 0.

[Hugo Kornelis]

On 31 Mar 2005 14:17:10 -0800, ajamrozek wrote:

Ok so here's my stored procedure:
--------------------------------------
(
@NSIR_ID INT,
@PRODUCT_DESCRIPTION NVARCHAR(255),
@ACCESS_TIMESTAMP TIMESTAMP
)
AS
BEGIN TRAN

UPDATE ajamroz.IT_NSIR
SET PRODUCT_DESCRIPTION = @PRODUCT_DESCRIPTION
WHERE ID = @NSIR_ID AND ACCESS_TIMESTAMP = @ACCESS_TIMESTAMP

IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
SELECT 'NSIR: ' + CAST(@NSIR_ID AS NVARCHAR(5)) + ' has changed since
you opened it, so your changes could not be recorded. Please close
this window and select the record again to get the latest changes.'
END
ELSE
COMMIT TRAN
----------------------------------
All updates are still executing though. I tried to make this thing
throw the error and it didn't. It seems like @@ROWCOUNT is never 0.

Hi Alex,

I tried it, but I had no problems. Here is the code I used.

First, the part to set up the table and procedure. Note that I simply
copied your stored procedure; the only change was to remove the owner
(ajamroz.) in fronn of the table name.

drop table IT_NSIR
go
create table IT_NSIR
(ID int NOT NULL,
PRIMARY KEY (ID),
PRODUCT_DESCRIPTION nvarchar(255) NOT NULL,
ACCESS_TIMESTAMP timestamp NOT NULL)
go
drop proc UpdateProc
go
create proc UpdateProc
(
@NSIR_ID INT,
@PRODUCT_DESCRIPTION NVARCHAR(255),
@ACCESS_TIMESTAMP TIMESTAMP
)
AS
BEGIN TRAN

-- UPDATE ajamroz.IT_NSIR
UPDATE IT_NSIR
SET PRODUCT_DESCRIPTION = @PRODUCT_DESCRIPTION
WHERE ID = @NSIR_ID AND ACCESS_TIMESTAMP = @ACCESS_TIMESTAMP

IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
SELECT 'NSIR: ' + CAST(@NSIR_ID AS NVARCHAR(5)) + ' has changed since
you opened it, so your changes could not be recorded. Please close
this window and select the record again to get the latest changes.'
END
ELSE
COMMIT TRAN
go
insert IT_NSIR (ID, PRODUCT_DESCRIPTION)
select 1, 'Test row'
go
With the table and stored procedure created, I executed this code from
Query Analyzer window #1:

-- Check starting information
select * from IT_NSIR
go
declare @NSIR_ID INT,
@PRODUCT_DESCRIPTION NVARCHAR(255),
@ACCESS_TIMESTAMP TIMESTAMP
-- Choose a row to operate on
set @NSIR_ID = 1
-- Simulate the read
select @PRODUCT_DESCRIPTION = PRODUCT_DESCRIPTION,
@ACCESS_TIMESTAMP = ACCESS_TIMESTAMP
from IT_NSIR
where ID = @NSIR_ID
-- Simulate "human activity"
waitfor delay '00:00:15' -- 15 seconds delay
set @PRODUCT_DESCRIPTION = 'Updated info'
-- Try the update
exec UpdateProc @NSIR_ID,
@PRODUCT_DESCRIPTION,
@ACCESS_TIMESTAMP
-- Review the result
select * from IT_NSIR
During the 15 seconds delay, I switched to Query Analyzer window #2 and
executed this code (to simulate an update by another user):

UPDATE IT_NSIR
SET PRODUCT_DESCRIPTION = 'Updated by someone else'
WHERE ID = 1
After the 15 seconds delay were finished, the output in window #1 was:

ID PRODUCT_DESCRIPTION ACCESS_TIMESTAMP
----------- ---------------------------- ------------------
1 Test row 0x000000000000721D
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
NSIR: 1 has changed since
you opened it, so your changes could not be recorded. Please close
this window and select the record again to get the latest changes.

ID PRODUCT_DESCRIPTION ACCESS_TIMESTAMP
----------- ---------------------------- ------------------
1 Updated by someone else 0x000000000000721E
I then re-executed the query in window #1, but this time I let it run
without executing any other query, The results were:

ID PRODUCT_DESCRIPTION ACCESS_TIMESTAMP
----------- ---------------------------- ------------------
1 Updated by someone else 0x000000000000721E

ID PRODUCT_DESCRIPTION ACCESS_TIMESTAMP
----------- ---------------------------- ------------------
1 Updated info 0x000000000000721F

(Note: I have edited the output above a little - I have removed most of
thhe white space of the product_description column to improve
readability)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)