0

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

Thomas
  • 33,544
  • 126
  • 357
  • 626
  • Which ORM are u using? – Stefan Oct 30 '13 at 08:33
  • 1
    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 Oct 30 '13 at 08:33
  • try this link about locking: http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking – Stefan Oct 30 '13 at 08: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 Oct 30 '13 at 08:37
  • i am not using ORM rather using ADO.DotNet data reader to fetch data from sql server 2005 db. – Thomas Oct 30 '13 at 08:42

2 Answers2

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

Mathese F
  • 559
  • 4
  • 9
  • u said :- You can create a rowlock for short time. so guide me how to create row lock in details. – Thomas Oct 30 '13 at 08:49
  • 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 – Thomas Oct 30 '13 at 08:50
0

Add three columns

  1. version(timestamp)
  2. editBy foreign key to user that edit a record
  3. editAt(DateTime)

Work flow

  1. Get all record
  2. Allow edit all record with editBy is null or editBy == currentUser
  3. 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).
  4. On save record change fields(editBy, editAt) back.
  5. Additional server logic to handle edition timeout. For example no longer than 30 min.
sh1ng
  • 2,808
  • 4
  • 24
  • 38
  • 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 – Thomas Oct 30 '13 at 08:48
  • 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. – sh1ng Oct 30 '13 at 08:53