Set up optimistic concurrency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Set up optimistic concurrency

Author

Topic

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-08-19 : 14:28:58

Is it possible to write T-Sql code to check for optmistic concurrency? If yes could someone give me a very simple example please?

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 14:52:24

http://msdn.microsoft.com/en-us/library/aa0416cz(VS.71).aspx is a good place to start maybe?

If you don't have the passion to help people, you have no passion

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-08-19 : 15:13:05

So your saying i need to do this in .Net AND in my SQL syntax?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 15:18:35

sorry wrong link

try this one

http://sqlblogcasts.com/blogs/simons/archive/2008/06/18/SQL-Server-2008---MERGE-and-optimistic-concurrency.aspx

I wonder if you could add a column that has a binary_checksum of all fields that is updated at INSERT/UPDATE and possibly DELETE (by setting IsDeleted field) then compare with current changes submitted by user?

If you don't have the passion to help people, you have no passion

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-08-19 : 15:23:30

It seems pretty complicated. Is there a very simple example i could follow as this will help me understand?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-19 : 15:57:24

I just did a quick search and received 10's of thousands of results on this topic. Here is one that might help:
http://davidhayden.com/blog/dave/archive/2005/10/05/2503.aspx

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-08-19 : 16:26:31

I have already read that link. What im asking for is presumably a step by step guide to understand it better. As per that link it does state "Below shows the strategies from a view point of 30,000 ft high." So i guess im looking at strategy 2 but a bit more in depth.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-19 : 16:54:20

For Option #2:

1. Select the tuple that you want to edit.
2. Store all the Original Values. Presumably, in the calling application.
3. Make edits in the UI (or where ever)
4. Only allow the update if none of the origianl values have changed.

Assuming that the table has 5 colums Col1 through Col5 the update might look something like:

UPDATE
	MyTable
SET
	Col1 = @NewCol1,
	Col2 = @NewCol2,
	Col3 = @NewCol3,
	Col4 = @NewCol4,
	Col5 = @NewCol4
WHERE
	Col1 = @OldCol1
	AND Col2 = @OldCol2
	AND Col3 = @OldCol3
	AND Col4 = @OldCol4
	AND Col5 = @OldCol5

Obviously, if you are calling a stored procedure you need two sets of parameters: one for the old values and one for teh new.

If you need more clarification let us know!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources