Record Locking 1

[Guru2B]

Greetings,

Hopefully this will be an easy answer for you VB pros out there:

I am creating an app that will use a shared database to furnish many users with information. The user will be allowed to add and edit the information contained therein.

I am using DAO against Access 2000. I am also using a class to retrieve the record from the DB. I can pass the class a value (ClientID) and it 'knows' how to populate itself with the record. As soon as it's populated, the connection to the DB is closed.

Here's the problem: How do I ensure that the information written to the database by one user will not overwrite any changes made to the same data by another user?

Eagerly awaiting any suggestions,
Guru2B

 

[scorpio66]

Normally I would suggest using the databases own implementation of record locking. The first user to access the record in 'update' mode has exclusive rights to that record until the change is committed. All other users either a) wait for the lock to clear or b) get a message telling them that they can't edit the record now.

However, because you close the connection that is not possible.

My first question is why do you want to do it like this? I'm assuming its some form of data caching. The problem with that is a) noticing when the underlying data has changed and b) the locking problem described above. If you can, I would advise that you get the data out of the database every time, otherwise you are making a lot of work for yourself and YOU WILL GET IT WRONG. Time and time again. Believe me, I've been there.

Against my better judgement, however, I can think of two approaches to resolving this:

1) Implement locking yourself - add fields to the table so that the user can 'check out' the record, modify it and 'check it in' again. You will need to do this for all the data tables that can be modified by the application but changes will go unnoticed. You will also need to be darn sure that your locking process is atomic, i.e. cannot be interrupted, otherwise you may end up with 2 users locking the same record. Oh, and you need to decide when to unlock records, and how long they can be locked for, and what happens if you unlock a record which is still being modified and ... YOU WILL GET IT WRONG. Believe me, I've been there.

2) Route all changes through a central service. The service is responsible for modifying all the data, locking, etc. It can also raise events to all of the clients saying 'update this record internally' so that things keep in sync. The service needs to be completely reliable. It also may need to be completely aware of all of the data items and relationships in your application. It also needs to know about locking timeouts, etc, rollbacks... YOU WILL GET IT WRONG. Believe me, I've been there.

Neither of these is as good as proper database management using record locks, etc. You might want to consider using ADO, and if you intend to have lots of clients Microsoft SQL server, Oracle or Sybase SQL server might be better options than Access. Locking is an incredibly complex issue - let the database do it for you.

Chaz

 

[Guru2B]

Well, you certainly raise a number of valid points. I will have to look at this problem again.

Thanks

 

[gkrogers]

I can think of another way, although I'm in agreement with scorpio66 on this in that your best bet is to use the database's inbuilt locking mechanisms.

But, if that's just not an option...how about, when you come to write the data back to the table, checking that the data hasn't been modified since you read it and, if it has, throwing an error, which your application can handle? This will involve storing the read values, then doing another read just before you're about to do the write and comparing the values. You could even check just the values that have been changed by the user if you wanted to get clever. I'm sure one of Access's locking schemes uses this method - I think it's called optimistic locking (as in "Nah, it'll be alright - nobody will have changed the data between us reading it and wanting to write it again...but we'd better just check...&quot

But, as I say, I agree with scorpio66: use the database's inbuilt locking if it's at all possible - it's one of the major reasons for using DBMSs. Any effort and ingenuity you put into implementing your own locking scheme is almost certainly better spent thinking of ways of designing your application so that it can use the DBMS's inbuilt locking. gkrogers

 

[scorpio66]

No, do not do that. Checking the values of the table before writing is not atomic - there are no guarantees that the data will not be changed between reading the record to check and writing the data.

Actually, if the database supports read locks then it might work, but you're making a lot more work for yourself.

Chaz

 

[Guru2B]

Hi,

Okay. I have decided not to use an object to contain a 'disconnected' recordset and have put all my data access code behind the form.

Is there anything special I have to do to ensure that the DB will lock the record appropriately? How do I maintain a record lock while working with the data? Should I keep the recordset open?

I have read that Jet locks 2048 bytes at a time (possibly locking the record after the one I'm editing). Will this cause any further complications?

Thanks again for all your help,
Dave

 

[gkrogers]

Yes, good point - you'd have to lock the record on the read and not release it until you've done the write. gkrogers