Lock Table during Insertion or Updation
- Thread starter vellaima
- Start date
Robby
Moderator
Which database are you using?
Nerseus
Danner
What kind of locking do you mean? By default ALL databases will lock a row for a single update - youll never get column1 updated by column2 not updated.
Maybe you meant locked while your users have it to edit or maybe locking multiple tables/rows so that two or three different updates are all part of the same transaction...? More info please
-nerseus
Maybe you meant locked while your users have it to edit or maybe locking multiple tables/rows so that two or three different updates are all part of the same transaction...? More info please
-nerseus
I would like to lock the table so that no two users can update or insert values at the same time. If the first user is updating the table say "Sales Representative" then the second user should not be able to update the Sales Representative until the first users data is updated
Nerseus
Danner
Are you allowing more than one row to be updated at a time? If just one row at a time, are you saying you want to lock that row until the first user is done with it?
As I said, if you are only allowing one row to be modified at a time, the database will guarantee that each UPDATE will succeed. If they happen at the *exact* same time, Access will allow one update to occur, then process the next one - last one to update wins. Theres no built-in way around this - youd have to code something to prevent it, but I need the specifics of what you want before I can offer any advice.
-ner
As I said, if you are only allowing one row to be modified at a time, the database will guarantee that each UPDATE will succeed. If they happen at the *exact* same time, Access will allow one update to occur, then process the next one - last one to update wins. Theres no built-in way around this - youd have to code something to prevent it, but I need the specifics of what you want before I can offer any advice.
-ner
wyrd
Well-known member
As Nerseus said the update will be done and Access will make sure that two users arent updating at the same time.
Im taking it thats not what you ment, though.. do you mean if a user (say, user 1) updated a record you want to notify user 2 that the record was updated by another user and NOT save his changes to the database?
Im taking it thats not what you ment, though.. do you mean if a user (say, user 1) updated a record you want to notify user 2 that the record was updated by another user and NOT save his changes to the database?
Robby
Moderator
Lets say they update the same row 30 minutes apart, would you want to inform them? What about 1 minute, or 3 seconds.
What I getting at is that theyre all handled the same way.
What I getting at is that theyre all handled the same way.
Nerseus
Danner
Suppose you have a scenario where User A gets a row of data and begins editing. Then User B gets the same row, edits it, and saves it. Do you want to:
1. Not even allow User B to edit the row
2. Have User A (who is updating second) overwrite User Bs changes
3. Give User A a message that they cant update since someones changed the data since they first looked at it
4. Notify User A immediately when User B saves.
Heres some ways to handle these cases:
1. Access doesnt suppor row level locking. SQL Server does, but youll need to enable the row-level locking. Youll probably need some kind of custom row-locking: the basic idea is to set a flag on a row when editing begins. You must worry about cleanup if the user who holds the lock crashes and the lock is still held - this is never fun to implement, but sometimes necessary.
2. This is the most common scenario and easiest to implement since you dont have to do *anything*
3. This requires keeping a date or something other date/time stamp when you begin editing a row. When you go to update, check that the date in the database matches your date. If they dont match, someone else has already updated your row. Youll have to decide if the second user is out of luck or if you try to do some kind of merge (or use Number 2 and just overwrite anyway, after a message).
4. This is pretty much impossible so dont worry about it
-nerseus
1. Not even allow User B to edit the row
2. Have User A (who is updating second) overwrite User Bs changes
3. Give User A a message that they cant update since someones changed the data since they first looked at it
4. Notify User A immediately when User B saves.
Heres some ways to handle these cases:
1. Access doesnt suppor row level locking. SQL Server does, but youll need to enable the row-level locking. Youll probably need some kind of custom row-locking: the basic idea is to set a flag on a row when editing begins. You must worry about cleanup if the user who holds the lock crashes and the lock is still held - this is never fun to implement, but sometimes necessary.
2. This is the most common scenario and easiest to implement since you dont have to do *anything*
3. This requires keeping a date or something other date/time stamp when you begin editing a row. When you go to update, check that the date in the database matches your date. If they dont match, someone else has already updated your row. Youll have to decide if the second user is out of luck or if you try to do some kind of merge (or use Number 2 and just overwrite anyway, after a message).
4. This is pretty much impossible so dont worry about it
-nerseus