Lock Access database? 3

[daveask]

Hi Experts,

I am using ASP+Access in my pages.
Sometimes there are about 50 people visit my pages at the "same time".
The data in database need to be edited from time to time (use ADO, Insert, Update, Delete...).
Do you think I need to lock the database when editing the data? If your answer is YES:
1. Can you tell me how to lock Access database?
2. If the database is locked and another user try to connect it at the same time, what will happen?

Thank you in advance.

Dave

 

[MoLaker]

Whether or not you need to "lock" the database would depend upon it's use and whether or not users have the ability to update it.

If it is read only for normal users then it is probably not necessary to lock it unless the data is so critical it might cause someone a problem if viewed during an incompleted update process. If the data is that critical, it might be better to update a copy of the database off-line (compact it while you are at it) then upload the entire DB file.

On the other hand, if users have the capability of updating the data, it might be desirable to lock it or in some way prevent access during your updating procedure.

How to lock it? My preference is to temporarily redirect pages that access it to a page which tells the user what is going on. This can be built into your website by having the access pages check for a condition (presence of a specific text file, for example, or a condition in a single record table) to determine if the DB may be accessed. Copy the text file to the appropriate location or set the condition in the table when you want to lock the DB and remove it when you want to unlock.

 

[daveask]

Thank you for your help. An example is in Global.asa something like:
application.lock
application("clickNumber")=application("clickNumber")+1
application.unlock
Now, I worry about Access database. If a few users edit the records at the same time, what will happen and what the Access deal with the edits.
So, you think there is no such thing: cnn.lock and cnn.unlock.

 

[MoLaker]

Application.lock/unlock is certainly an option. But, your initial posting indicated you were wanting to update your database "from time to time (use ADO, Insert, Update, Delete" which poses the possibility of a lengthy process. Locking the application during that time should effectively prevent other users from accessing your database, but could also prevent other ASP pages from being served while you have it locked - with no explanation to the user. That could frustrate and discourage users from visitng your site. But, you decide how best to handle your particular situation.

As for Access (any database for that matter) handling the possibility of multiple edits, it doesn't do that very well in an ASP application. There is no way to know if another user is editing the same record without taking specific measures. But, you are not very likely to run into update conflicts causing a database error. You are more likely to have one user overwrite another user's updates.

 

[Genimuse]

There is no such thing, correct.

MoLaker's got it, that's the best way. Set an application variable, like Application("DBLock"), to false in the global.asa. When you want to lock the database, use your own private page to set that variable to true. Then at the top of every page that uses the database, check to see if the variable is set to true, and if so, tell the user the db is undergoing maintenance and that the page should be available again in whatever time period.

If more than one user is updating the same record, then the last person to update is is the winner of version of a common programming problem called a "race condition." If you want to prevent a situation like User A getting data, User B getting the same data, User A saving changes, and then User B saving over User A's changes (without ever having been aware of them), you'll have to "manually" lock records.

One common method is to have a "LockTime" field for each row, a DateTime field that is either a set date in the past (no one has ever requested the record without having yet closed/updated it) or contains the date and time that someone requested it for editing. Whenever someone tries to open a record, you check to see if the date and time are more than timeout minutes old (you decide on the timeout). If it's not that old yet, you tell the new user that the record is unavailable, but if it is that old, you update the LockTime field to the current datetime and present the record to the new user.

Whenever you provide a record to a user, you put a hidden field in the form with the new LockTime info. Then whenever a user attempts to update or delete a record, you make certain that the hidden LockTime matches the stored LockTime. If it does then you permit the change and set the LockTime back to your "static" date in the past. If it doesn't match then you inform the user that the request has timed out and, if the LockTime is now set to a different expired datetime (possibly your old "static" date, possibly some other user's later now-expired lock), you ask them if they'd like to reload the current record for possible updating.

The important thing is that you set a timeout of some kind and make those comparisons, because if, say, a user requests a record and his browser crashes or he leaves for the day or what have you, the record can be locked for a very long time, possibly even indefinitely.

Make sense? It's a bit of work, but you can indeed keep people from accidentally messing with each others' work that way.

 

[Genimuse]

I just realized that I refer to that method as a "common" one. Actually I have no idea how common it is -- I came up with it years ago, and it seems logical, so I assume other people use it.

There may be a smoother way, I'm just not aware of it.

 

[MoLaker]

Actually, Genimuse, I had only thought of using a "Lock" field, not a "LockTime" field. So, I had some difficulty with removing the lock when a user went away w/o finishing the job. Yes, your method IS logical, just somehow escaped me. Thanks, have a star!

 

[daveask]

Thanks a lot for all your useful helps. I need to study your suggestions and then decide what I should do.