First of all, please, click the following link (and come back when you're done): BUG in Access 2007: locks
The question I'd like to ask: Does anyone know how does page locking in MS Access work? Does it lock 4kB of each table which is included in SQL query? I'd like as many details as possible, please.
Any help is appreciated. Thank you very much for your time in advance.
Hi Vladimir,I'm trying to involve some senior engineers into the issue. It takes some time. Your patience will be greatly appreciated.
Thanks for your understanding and have a nice day.
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
When a user edits a record in a shared database, you can prevent conflicts with other users by locking the data while it is being edited. When data is locked, any number of users can read it, but only one user can make changes to it.
In previous versions of the Jet database engine (version 3.5x and earlier), locking a record locks one page of data. For previous versions of the Jet database engine, a page is equal to 2K (2048 bytes) of data within the database file; for the current version, Jet 4.0, a page is 4K (4096 bytes) of data.
As a general rule, you are quite right, the locking system locks out the whole page.
And as your other question shows, then such a case can arise in which two different users attempting to edit records IN THE SAME page will encounter the issue you mention.
The basic solution is then to simply enable ROW locking in Access.
While I MUCH try to avoid this feature, it can in cases like yours solve this issue.
Keep in mind that behind the scenes this row lock feature should NOT be used until you VERY SURE need this feature.
The reason is simple:
The row locking feature is using a "simple" trick to achieve it's magic. All it does is EXPAND (pad with blanks) the record to ALWAYS result in the record being ONE full page size. Since the record ALWAYS takes up the space of a full page then you in a "trick" of a way now get real row locking. The result is one record per page!
The BIG downside of this feature is using can cause HUGE bloat.
I seen simple update routines in a 3 meg file case that database to grow to over 120 megs with this feature on.
With off, then virtually no bloat occcured.
So to fix this issue, than turn on row locking.
The option in 2010 is found by file->client settings
The check box is
[x] Open database by using record-level locking.
If you don't use above option, then access defaults to page locking. And keep in mind that you can "mix" clients that open the database using both approaches at the SAME time!
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada