Lock Table during Insertion or Updation

[vellaima]

Hello,

 

Is there anyway of locking a table when updation or insertion is done. Meaning only one user at a time can update or insert data into the table. A sample code will be appreciated as i am new to VB.NET.

[Robby]

Which database are you using?

[vellaima]

I am using MS Access

[Nerseus]

What kind of locking do you mean? By default ALL databases will lock a row for a single update - you'll 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

[vellaima]

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]

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. There's no built-in way around this - you'd have to code something to prevent it, but I need the specifics of what you want before I can offer any advice.

 

-ner

[vellaima]

What i would like to know can we display a message in VB.NET to the second user that someone is updating the particular row and hence updation will not be done.

[wyrd]

As Nerseus said the update will be done and Access will make sure that two users aren't updating at the same time.

 

I'm taking it that's 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?

[vellaima]

Yes, i want the second user to be notified that the first user is making changes and hence updation cannot be done.

[Robby]

Let's 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 they're all handled the same way.

[Nerseus]

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 B's changes

3. Give User A a message that they can't update since someone's changed the data since they first looked at it

4. Notify User A immediately when User B saves.

 

Here's some ways to handle these cases:

1. Access doesn't suppor row level locking. SQL Server does, but you'll need to enable the row-level locking. You'll 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 don't 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 don't match, someone else has already updated your row. You'll 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 don't worry about it :)

 

-nerseus