Remove Data Concurrency From SQL SERVER

First what mean by Data Concurrency.

If our appliction is used by many users. More than one user has opened up a record and simaltenously all user are trying to update that record.

So by whom saved is done in database. If in noraml condition that only detect concurrency when both will save at same time otherwise last save is made to database.

But the solution is that once first user saved record to database then other will not be able to save and inform him that record is changed by some one so please reload that changes.

In this case we have one solution is to use timestamp data field in database table.

timestamp filed will be modified each time when we are changing record. 

Lets have a look on some sample

I have a table Person in my database with fileds

• ID 
• Name
• Notes
• EditStamp  
  

Here EditStamp is timestamp field.

When you will going to add record you simple need to write

INSERT INTO Person (Name,Notes) VALUES ('AAAA','First Person')

as we have kept ID as autoincrement and EditStamp will be added by SQL server you will not to worried about its.

Now when you try to update record. then you will also pass EditStamp of that record

 @EditStamp timestamp

 @ID int

SELECT @EditStamp = EditStamp FROM Person WHERE ID = @ID

UPDATE PERSON 

SET Name ='BBBB'

WHERE  ID = @ID AND EditStamp = @EditStamp

UPDATE PERSON 

SET Name ='CCCC'

WHERE  ID = @ID AND EditStamp = @EditStamp

When you will execute above statement you will surprised that only Name is update with 'BBBB'

It will not update with 'CCCC'

Because after first update value of EditStamp will be changed. 

So with this we can overcome data concurrency problem we can also raise some error and detecting it.