Replacing TSEQUAL function
-
Hi,
In the past we've used the TSEQUAL function to guarantee concurrency in our sprocs. However my company has concerns that since the TSEQUAL function is undocumented that it may be removed in the future by MS. Therefore I've been tasked with replacing it.
We've come up with the following. (In our case @timestamp is both an input and output parameter)
BEGIN TRANSACTION
SELECT @ts = timestamp
FROM example with (updlock)
where ID =@ID – primary key
--error checking block for select
if @ts<>@timestamp
--fail out with error message
--otherwise all ok
UPDATE example
SET value=@value
WHERE id=@id
SELECT @ErrorValue = @@ERROR, @RowsAffected = @@ROWCOUNT
IF @RowsAffected = 0 OR @ErrorValue <> 0
BEGIN
IF @@trancount <> 0
ROLLBACK TRANSACTION
RETURN @FailValue
END
SELECT @Timestamp=timestamp
FROM example
WHERE id=@id
--error checking block for select
COMMIT TRANSACTION
My question comes from the final select statement where we return the new timestamp value for the row. I'm thinking that because we have an updatelock on that row (from the first select) and its within the same transaction, that we're guaranteed in the final select statement that the original record won't have been updated since and thus getting the correct timestamp value. Am I right?
Cheers
-
h
D!shan
-
TSEQUAL is not totally undocumented.
Although BOL lacks an explanation of use to use this function, you can find it in the list of reserved keywords for SQL Server.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi all,
Got a further question in relation to this one. It's been suggested that I could use @@dbts to return the final timestamp. e.g change the final error block to:
SELECT @ErrorValue = @@ERROR, @RowsAffected = @@ROWCOUNT , @dbts= @timestamp
IF @RowsAffected = 0 OR @ErrorValue <> 0
BEGIN
IF @@trancount <> 0
ROLLBACK TRANSACTION
RETURN @FailValue
END
However according to BOL, this returns the current timestamp for the current database, not the last statement.
So if you do the above, then that means theres no guarantee that I'll get the right timestamp value?
What do you reckon?
Cheers
-
DO NOT use @@dbts as you would SCOPE_IDENTITY() !!
AFAIR @@dbts is NOT Transactionaly safe!!
ALL MS suggestions are based on RE-Reading the new TS column if you need to update again on a Second trip to the server!!
If your Enviromet is not too Highly transctional and the table you are accessing is not too big then you are on the right path, I would change the Update though to
Update ...
Where id = @id and ts = @timestamp
if you think Transactions are very intensive you could
use the reserved keys table trick to read only the keys that are not in use to allow updates!
HTH
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply