Adding version to row to ensure user updates the current row version

Question

Adding version to row to ensure user updates the current row version

amns

SSCrazy

Points: 2628
More actions
July 18, 2014 at 11:52 am

#293501

Hi!
I'm having the problem described in the following example:
Table Samples:
SampleID | Color
1 Blue
User A open sample 1, its color is Blue and he updates the color to Green.
User B open sample 1, its color is Blue and he updates the color to Red.
Both users did not receive an error, but at the end the color will be Red and user A has updated to Green color.
How do I ensure that User B receive an error when updating the color because the color of sample that he is
editing has already been updated by another user to a different color?
The probability of this happens is low, but it could happen.
I've read that I could had rowversion or a datetime (with last update date),
but I don't know which is the best option.
Can you give some guidance?
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You can add a rowversion column to the table and do a comparison of the rowversion in the table and the rowversion stored in your app and then tell the user that someone else had made a change. If I had to code it I'd choose the rowversion over the date, unless I wanted the update date for other purposes as well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ScottPletcher SSC Guru Points: 101118 More actions · Answer 2

It depends.

Do you want to check just the column(s) they are actually trying to UPDATE? If so, you can just re-read them and insure they haven't changed before you process the UPDATE.

If you want to warn them of a change to any data in the row, outside of what they actually deleting, then you will probably prefer a datetime stamp. Read and store that timestamp for each row as you display it to the user, and compare it when you UPDATE, returning the appropriate message, and either doing no UPDATEs or only the valid ones, whichever you prefer.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lowell SSC Guru Points: 323518 More actions · Answer 3

an example with rowversion, where the WHERE statement needs to take into consdieration teh last version;

that makes the update jsut a bt more complex.

IF OBJECT_ID('[dbo].[Samples]') IS NOT NULL

DROP TABLE [dbo].[Samples]

GO

CREATE TABLE [dbo].[Samples] (

[SampleID] INT NULL,

[Color] VARCHAR(30) NULL,

[vers] ROWVERSION NOT NULL)

INSERT INTO Samples(SampleID,Color) SELECT 1,'Blue'

SELECT * FROM Samples --vers=0x00000000000007D1

--USERA

UPDATE SAMPLES

SET Color = 'Green' WHERe SampleId = 1 AND vers=0x00000000000007D1

if @@ROWCOUNT = 0 RAISERROR('Concurrency violation. Data has been changed since Data Snapshot Take.',16,1)

--USERB

UPDATE SAMPLES

SET Color = 'Red' WHERe SampleId = 1 AND vers=0x00000000000007D1

if @@ROWCOUNT = 0 RAISERROR('Concurrency violation. Data has been changed since Data Snapshot Take.',16,1)

Msg 50000, Level 16, State 1, Line 3
Concurrency violation. Data has been changed since Data Snapshot Take.

you could do the same thing without the extra columnby comparing the binary_checksum(*) of the row as well, but you need to get the binary_cheksum as a additional column in your dataset somewhere, i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 4

Here is an alternative solution.

http://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20140718

amns SSCrazy Points: 2628 More actions · Answer 5

Thanks for all your opinions.

I think I really just need to check the value of the column to update before I update it.

Regarding rowversion:

If I add rowversion to a table and then update table data,

will all the versions be stored in tempdb database?

Thanks!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

You wouldn't use rowversion via tempdb, not really.

What you'd do is in your CRUD proc, you'd have an additional parameter of, say, @oldRV. You'd then open a two stage transaction. The first would pull the existing record as a read, and you'd compare the RowVersion on the row to the parameter passed. The next step, if they matched, would update the row. Otherwise, you'd send up an error message for the end user.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

amns SSCrazy Points: 2628 More actions · Answer 7

Hi!

I ended up using rowversion, since it was not enough to compare the old and the value in the table column before the column update.

Other columns values may have changed since the last database snapshop and this is important to track, so the rowversion was the best solution.

I have one question yet. I read about it, but it was not clear to me if there is a relation between adding the rowversion columns and rowversion isolation levels: READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION

Thanks!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Adding a rowversion column is not related to snapshot isolation levels. In snapshot isolation concurrency is increased by taking a snapshot of the data you need for your transaction and putting it in tempdb instead of locking that data from other users. Your transaction uses the snapshot stored in tempdb, if you make changes and try to commit them after someone else has changed the same data your transaction will fail. See this article, http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question