How to prevent two user to access same data from sql server

Question

i really do not know what would be the best way to prevent two user to access same data from sql server. i have developed a win apps using c# where user click on record to modify it. when user click on records then a new form is shown with data from where user can modify the data. suppose my application is running on two pc and two user open the same record then who update the same data last that will be saved in db.

so what i did...i create a table and store record id in that table whenever any user open data record.if a record id is stored in table so next time another user can not access that data rather get message who is working on this records. this way my purpose is solved but i do not know whether it is right or best approach or not.

so guys tell me how to handle this situation with best approach. thanks

What happen in this support table if your first user closes your app without any confirm or rollback? (I.E. Power outgage or else) — Steve
– Steve, Commented Oct 30, 2013 at 8:33
try this link about locking: stackoverflow.com/questions/129329/… — Stefan
– Stefan, Commented Oct 30, 2013 at 8:34
I'd usually recommend optimistic concurrency instead - locks tend to lead to the need to allow overriding/forced unlocks, as well as the cleanup that Steve's alluding to. How often are two users likely to access the same record? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 30, 2013 at 8:37
i am not using ORM rather using ADO.DotNet data reader to fetch data from sql server 2005 db. — Thomas
– Thomas, Commented Oct 30, 2013 at 8:42

Mathese F · Accepted Answer · 2013-10-30 08:33:35Z

0

you have multiple solution.

You can create a rowlock if you only access one table for a short amount of time.

You can create a transaction between taking data and writing them. But you should always be aware of the syndrom called cup of coffee :p People retrieving data, going away and blocking the data.

Try perhaps to let the user modify, save and compare the previous result you had in you c# code and if it's different give an advise to the client. If he accepts open a transaction then save

answered Oct 30, 2013 at 8:33

Mathese F

5594 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Over a year ago

u said :- You can create a rowlock for short time. so guide me how to create row lock in details.

Thomas Over a year ago

please discuss this points in more details... u said :- Try perhaps to let the user modify, save and compare the previous result you had in you c# code and if it's different give an advise to the client. If he accepts open a transaction then save

sh1ng · Accepted Answer · 2013-10-30 09:16:49Z

0

Add three columns

version(timestamp)
editBy foreign key to user that edit a record
editAt(DateTime)

Work flow

Get all record
Allow edit all record with editBy is null or editBy == currentUser
On open form exec command update Record set @editBy = currentUser, @editAt=getdate() where @id=id and @version = currentVersion and check affected rows. If 1 record then we are in the edition mode, if not refresh record(s).
On save record change fields(editBy, editAt) back.
Additional server logic to handle edition timeout. For example no longer than 30 min.

edited Oct 30, 2013 at 9:16

answered Oct 30, 2013 at 8:38

sh1ng

2,9934 gold badges26 silver badges39 bronze badges

2 Comments

Thomas Over a year ago

if i do it by version then who try to save the record at last then i need to force that user to reload the data again and save. this way i do not want to achieve my goal. rather i like to lock a row when i will fetch data and that row will be unlock when data will be saved again. any idea ? thanks

sh1ng Over a year ago

Ok, add one more column(editMode). Update this flag on open form(with statement like update table set editMode = 1 where @id=id and @version=myCurrentVersion). Check number of affected row to be sure no other user is more fast :-) And another thing columns editedBy(user) and editedAt(DateTime) help handle the timeout and return user to edition.

Collectives™ on Stack Overflow

How to prevent two user to access same data from sql server

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related