Record Locking Concerns

I am looking for feed back on this topic. Any of your thoughts or comments are appreciated. I am constructing a multi user database and I am concerned about record looking as most of the databases I have worked on before had a small number of users. I have a table that a dozen people will be adding records to all day. What they are doing is not that involved, but each time they do it they will add a record to a table. Since all 12 people are going to be adding records to the end of this small 4 column tabel all day, I am concerned about them having record writing conflicts.

One area I could use your feed back in is the "page" of 2 kb and how it plays into the following scenario as it relates to optimistic locking (no locks) or edit records locking.

Say we have two users, user01 and user02. Say user01 and user02 are both adding a record to this table. User01 finishes first and saves their changes. Then a few seconds later user02 saves their changes.

1) Edit records locking

If I am understanding the page idea correctly, edit records locking wouldn't allow for this scenario with my small table that's probably less than 2kb per record. Only one user could have been adding a record. If user01 had started first, user02 couldn't have started adding anything because user01 has locked the bottom of the table. Thus productivity would be very poor because only one person could add records at a time.

- Is this correct?

2)Optimistic Locking

This would allow for both user01 and user02 two be adding records at the same time. But will user02 be prompted about user01's addition, user02 getting an over write last persons update message?

Again I really appreciate you help here as it is most needed. Thank you for your time and happy holidays.
Who is Participating?
brewdogConnect With a Mentor Commented:
Good questions, Steve. Generally, I use optimistic locking for my databases (no locks) and then just build in some retry routines if I get the standard record-locked messages, etc. Answers to your specific questions are:

1. If you are using Access 2, then yes, you are correct that Access will lock the end of the table if one user is adding records. If you are using 7 or 97, they fixed that problem. The 2k "page size" is a bit odd, too: sometimes it seems to lock starting at the current record and going forward to other records, other times it starts before and finishes on the current record, and other times it splits locked records on both sides of the current record. (I don't know what Microsoft would say about it, but that has been my experience.)

2. This happens pretty rarely. The only situation where I think this might be a major concern is if you are using an AutoNumber in your table: Access may grab an AutoNumber for User01 and then, almost simultaneously assign it to User02. Or, sometimes it skips numbers (I don't know why). If you're using an AutoNumber, you may want to consider changing it to a Number field and writing a piece of code that finds the last number used and then increments it.

Are you using bound forms (tied to a table or query) or unbound? It's easier to do this stuff with unbound forms, but . . . Either way, you could consider using batch processing or temporary tables with append queries if it's a big issue.

Let me know if you have other questions, or if this doesn't quite address your problems.

TheRoyalFalconAuthor Commented:
I would like to thank you for taking the time to respond to this question. As I mentioned I am a little new at the frequent updates to a table area. It sound like even though there will be frequent updates, the odds of a person being prompted for an overwrite of someone else's work is not likely using optomistic locking?

I think I now see why I was worried and why I don't need to be. With Optimistic Locking, the lock is only for brief moments. It happens in my case there would never be a time when two of my users would be editing the same record, they would just be adding new records at the same time. Because they are in their own record, even if one of them locked the bottom of the table for a moment, no one would be prompted to overwrite another's work because they aren't hitting the same record, even if they are records next to each other. So they could lock the bottom of the table for a very brief moment but nothing more. I, for some reason, confused the 2kb lock with the records themselves, thinking records close by would get intertwined in the saving process. When in essence they are just records that are close by. They may have to wait a moment but never be prompted for an overwrite.

Unless I am way off course here, I feel pretty good about the whole thing now. Thank for your feed back it has proven to be quite helpful and reassuring.

Do you know of anyway I can tell how many bytes a record on a table makes up? Also is there a place where I could look at retry routines. I might want them to be able to copy just their record to the clipboard and try saving it as a new record should they run into the problem. Thanks and Happy Holidays

For figuring out how big your records are:

Text fields take up as much space as the text entered in the field. (Even if the field size is 50, if a user has only entered the name "Joe" in the field, for that record the field memory size is 3.)
The help system can tell you how much space different Number fields take up.
Yes/No fields take up 1 byte.
AutoNumber takes up 8 bytes, I believe.
OLE fields depend on the object itself.

Hmm. . . that's all I can think of for that off-hand.

I don't know where to point you for retry routines -- I have a couple of my own, usually using either a simple integer that increments (up to 10, for instance) every time the "I can't write now" error occurs or a timing routine using the TimeGetTime function (returns milliseconds since midnight). If you want to try TimeGetTime, you need to declare it in a general declaration somewhere and then store the value (in a Long variable) at the start of the save routine. If it hits the error, then compare the current TimeGetTime to the value you stored in the variable. If it's more than 10 seconds, or however long you want to try, then writing is locked and tell the user to try again in a second.

Hope those provide some leads for you. Glad I could be of service.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.