how to avoid redundancy (concurrency) in our application ?

Posted by Nav234 under Sql Server on 7/5/2010 | Views : 3513 | Status : [Member] | Replies : 4
Hi all,

While multiple users of our application uses same database,it is very obvious that
any user can try to access same data from database,which leads to
concurrency.

For such problems ,i heard we can use SQL Locks ?
but if this is only solution ? then how to achieve it ?

and if any other easy way or any other way ?and how ?

Thanks in advance

S.Naveen...


Responses

Posted by: Muhilan on: 7/5/2010 [Member] Starter

Up
0
Down
we can use TRansaction scope also. go through the below article to know more about

http://msdn.microsoft.com/en-us/library/cc917674.aspx

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 7/6/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
It Depends on the Type of Application you are developing.

I once worked on a Project, where i came across Concurrency problem. but it was easy for me to resolve it, Because they requirement was that if a record has been retrieved by User1 then User2 Should not be able to retrieve the Report. So it was easy for me to overcome that, but another thing that you need to understand is when does Concurrency happen and what is Concurrency.

What is Concurrency ?

When User1 retrieve a Set of Data and User2 Retrieve the same Records and User2 makes changes on the Data and Commit the changes . Then this means User1 will carrying old stale data. and when User1 makes a change of the record and try to save this records, the User1 will get a Concurrency error.



Now to Resolve this , you can use Optimistic Concurrency handling method or you can choose to handle this your self.

Allow all your users to retrieve data , and before a User Commit the data, check if the data did not change since they user has retrieve the data, if the data has changed, Refresh the data and keep the changes that you wanted to make on the cache and do the same check again and update the data.

I am doing this in one of my old articles in the following link
http://www.codeproject.com/KB/cs/N-Tier22.aspx

Thank you for Posting at Dotnetfunda

Vuyiswa Maseko


Thank you for posting at Dotnetfunda
[Administrator]

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 7/6/2010 [Member] [MVP] Silver

Up
0
Down
Hi

You can solve this issue usign TimeStamp column in the table. Because, whenever you modify the record, the Timestamp column will get updated automatically.

Scenario :
Create Table Tb_Sample1

(
ID Int Identity(1,1),
Name Varchar(50),
Gender Varchar(1),
RowID Timestamp
)
Go
Insert Tb_Sample1(Name,Gender) Values('Aequea','M') 

Insert Tb_Sample1(Name,Gender) Values('Salino','F')
Insert Tb_Sample1(Name,Gender) Values('Calcalino','M')
Insert Tb_Sample1(Name,Gender) Values('Setaceo','F')
Insert Tb_Sample1(Name,Gender) Values('Socaprio','M')
Insert Tb_Sample1(Name,Gender) Values('Alumino','F')
Insert Tb_Sample1(Name,Gender) Values('Vitriolic','F')
Go


Actual Records from the table:
ID	Name	 Gender	RowID

1 Aequea M 0x000000000005BCC1
2 Salino F 0x000000000005BCC2
3 Calcalino M 0x000000000005BCC3
4 Setaceo F 0x000000000005BCC4
5 Socaprio M 0x000000000005BCC5
6 Alumino F 0x000000000005BCC6
7 Vitriolic F 0x000000000005BCC7

Step: 1
User1:
User1 popolates 7 records , Each record will have Timestamp value generated on RowID column.

User2:
User2 also popolates 7 records , Each record will have same Timestamp value generated on RowID column.

Step: 2
User1:
User1 Modifies some record.
Update Tb_Sample1 Set Gender = 'M' Where Name='Vitriolic'
User1 popolates 7 records again (Refresh the page), Each record will have Timestamp value generated on RowID column and The modified row will have some different Timestamp value as given below.

Record modified :
ID	Name	 Gender	RowID

1 Aequea M 0x000000000005BCC1
2 Salino F 0x000000000005BCC2
3 Calcalino M 0x000000000005BCC3
4 Setaceo F 0x000000000005BCC4
5 Socaprio M 0x000000000005BCC5
6 Alumino F 0x000000000005BCC6
7 Vitriolic M 0x000000000005BCC8


User2:
User2 Tries to modify the same row (Name='Vitriolic'), We should not modify the row/data directly. You have to do the following validations.

1. You have to compare the RowID(Timestamp) of each row, Which row you going to modified with the Actual RowID from the Table.
So, Your RowID which you had on your page cache is 0x000000000005BCC7 , But the actual RowID from the Table will be 0x000000000005BCC8 .
Because whenever you modify a row the respective RowID(Timestamp) will also be modified automatically.

2. So, Your current RowID which you already got in your page will differ from the currently you got the RowID from table.

3. If the Both RowID is different then, You should not allow "User2" to modify the data, You have to raise some warning like "The same data modified by some other resource, So you have to Refresh the page and Try".

4. Once the "User2" page refreshed, then the modified information will be fetched.

5. Now the "User2" can try to modify the data.

Conclusion :
Whenever you try to modify the existing Data on multi-user environment application, Validating the Timestamp data of each row (Which row you trying to modify) is better solution.

If you have anything better solution appart from this, Please reply and share with others.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ermahesh2009 on: 9/12/2012 [Member] Starter | Points: 25

Up
0
Down
Dear
using time stamp your performance goes slow instead you use record modified count column(RMC).
like user 1 and user 2 fetch data when RMC is 10 if user 1 edit and modify in database RMC GOES to 11
now in case user 2 gone modified then it got error message RMC changes means it not that data that he has been fetched


Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response