2004.01.02 07:41 AM

Access Corruption Mystery Solved

[Update: Fixed links to Access corruption info on http://www.granite.ab.ca/access/.]

In October, I wrote about a rash of Access database corruptions I experienced with two systems converted to Access 2002 running on a load-balanced farm of Citrix Metaframe servers. The corruptions occurred regularly during the first two weeks of each month, multiple times per week per database.

The systems ran for years in a non-Citrix environment with no corruptions, so we initially focused on potential problems with server configuration and the Metaframe environment. We reviewed the usual list of culprits, including opportunistic locking, bad network cards or cables, and incorrect Access setup. We also verified that all 30+ users were running the latest Citrix Metaframe client.

Our efforts did uncover a few hardware and config problems on some of the Metaframe servers. One server was intermittently dropping its network connection, and a couple of servers had incorrect or missing DAO libraries. We also temporarily limited the systems to running on just two of the eight available servers in order to better control the environment and to turn on some logging to try and correlate corruptions with hardware/software events. In the end, though, none of this made a difference.

However, it did point out something I previously overlooked. In a normal desktop (i.e., non-Citrix Metaframe) environment, users run Access from their own machines and these local copies of Access all share a basic configuration established (and enforced) by my client. Unfortunately, the folks at my client who create and enforce desktop standards are not the same folks who install and configure Access on the Metaframe servers. Needless to say, the configurations are different. In fact, they aren't even the same on all the Metaframe servers, so users often get a different Access configuration each time they launch the system (load-balancing makes it impossible to predict which server a user will get when launching the system).

One Access setting we count on always being off is "Open databases using record-level locking", which is found on the Advanced tab of the Tools | Options dialog. It's stored in the Registry at "HKCU\Software\Microsoft\Office\10.0\Access\Settings\Use Row Level Locking" as a DWORD value of 1 or 0, and it can be programmatically changed using the Application object's SetOption method with the option name "Use Row Level Locking" and a value of True or False.

This setting is used by Access to instruct the JET database engine to use record-level locking vs. page-level locking. While no one has definitively linked JET's record-level locking with database corruptions, though some have tried, my experience has been that it definitely increases the chances of corruption. As compared to page-level locking, record-level locking is more granular, more complex, and results in more network activity, things that all increase the potential for corruption.

A check of the user-level Registry entries on many of the Metaframe servers showed that, in fact, users were all using record-level locking. This needed to change, but how? I considered asking my client's Metaframe server admins to change it, both for existing user profiles and with a policy for future users, but my recent experience with these folks didn't fill me with confidence. So, I decided to implement a programmatic solution instead.

You might think it would be as simple as having the Access system execute the following code on start-up:

Call Application.SetOption("Use Row Level Locking", False)

And it sort of is. This code does immediately update the Registry, which is good. But it alone doesn't solve the problem, because Access opens the database using the setting in effect at the time of open. Changing the setting after the database is open only affects the next open. And to make matters worse, the locking mode in effect is determined by the first user to open the database. If a user with record-level locking on opens the database, then all users who subsequently open the database will also use record-level locking, regardless of their own setting. With enough time, that one line of code would work, but it would be impossible to tell exactly when all users on all servers had been covered.

In order for this to work immediately and with certainty, it was necessary to make sure no one opened the database with the record-level locking setting on, or at least not keep it open in that mode. So the code was changed to this:

If Application.GetOption("Use Row Level Locking") Then
  Call Application.SetOption("Use Row Level Locking", False)
  '
  ' Show a form that beeps with 5-second timer informing user of situation and 
  ' asking them to try again.  (Form open call not shown.)
  '
  Call Application.Quit(acQuitSaveNone)
End If

I use a custom form with an auto-closing 5-second timer to avoid allowing first-open users with an inappropriate record-level locking setting from sitting on a MsgBox with the database open, causing all subsequent users to open the database with record-level locking enabled.

In a perfect world, the code would first check to see whether the current database was actually opened with record-level locking on prior to terminating the user's Access session, as the possibility exists another user with record-level locking off could have opened it first, causing the current user's inappropriate setting to be ignored. However, DAO doesn't expose this JET database connection attribute anywhere, and these systems use DAO.

Interestingly, it is available when using JET ADO via the Connection object's provider-specific OLEDB:Database Locking Mode property (see the DBPROP_JETOLEDB_DATABASELOCKMODE init property, which enables record-level locking, curiously code named "Alcatraz"). Also, when using JET ADO, you can establish the locking mode for individual SQL DML statements via the Recordset object's provider-specific OLEDB:Locking Granularity property (see the DBPROP_JETOLEDB_LOCKGRANULARITY rowset property).

One unfortunate side-effect of this approach is that it's possible for a user to be bumped out of Access as many times as there are Metaframe servers (in this case eight times). Luckily, in practice, the load balancer rarely hands users more than one or two servers, and it very often gives users the same server when quickly restarting.

To test this, I implemented it in just one of the two Access systems. The systems don't share any users, so I didn't have to worry about the setting change leaking over and skewing the outcome. The results were very positive. In December, the first full month the change was implemented, the modified system experienced no corruptions, while the unmodified system experienced the same number of corruptions. There were no complaints from users regarding the auto-termination of their Access sessions (they're a hardy bunch). I'm implementing the change in the other system today.

Mystery solved.


Comments

This is a wonderful paper. I am looking at a similar issue todo with the record locking option but in this case I was interested in why our databases keep exapnding so fast. Now it seems as though record level locking sets aside a 4 k page for each record. This has the effect of making each new and ammended record take up 4k of space in the database. This is resolved when the database is compacted but by then all the Ado code in our database has blown the database out by 10 megs

Garry Robinson
Editor of vb123.com and
author of a pragmatic Access security books

Garry Robinson | 2004.02.12 04:28 PM

Thought I would add to this contribution by showing you the ado code that stops database bloat with the single record locking turned on.

Change the granularity to 2 to see how the database bloats on a large number of single updates.


strOpen = "Select * from tblBox where sampletype = 'PulpDup';"
Set RsCnn = CurrentProject.Connection

Set rs = New ADODB.Recordset
With rs
.ActiveConnection = RsCnn
.Properties("Jet OLEDB:Locking Granularity") = 1
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Source = strOpen
.Open
End With

Garry Robinson
Editor of vb123.com and
Author of a pragmatic Access security book

| 2004.02.12 07:03 PM

Garry - Thanks for your comments.

I tried some recordset-based updates on a single table using both page-level and record-level locking granularity (as per your example) and didn't experience the bloat you described. My database grew (pretty much) consistent with the size of the data I added with each update. However, the differences in our outcomes may be due to differences in the size of the table's rows, total number of rows, number of rows updated, update type (strings vs. numbers), etc. Did your updates involve any memo fields? Did you see any differences if you performed all the updates within a single transaction?

In any case, what I appreciate most about your comment is that you've provided the ADO recordset property used to control granularity: "Jet OLEDB:Locking Granularity". In my post, I was only able to track down the constant name from the OLEDB headers.

Thanks!

ewbi.develops | 2004.02.13 11:10 AM

Thanks Gary for confirming what I had long suspected - that the Citrix servers were causing our Access databases to become corrupted - I didn't know the precise causes, but knew that we didn't get the corruptions running local copies of Access.
We also regularly got problems with some users opening the database in exclusive mode, probably due to incorrect settings on some of the Citrix servers. My next task is to get the Citrix admins to change their setups for Access.

Thanks for a great source of information - please keep it coming.

Dena Jordan | 2004.03.05 03:00 PM

Thanks for the info about how to avoid having our Access databases corrupted in Citrix.
I have a different -but related- issue to resolve at the moment. We have numerous access databases at the hospital where I work (of the magnitude of 4,000!). We are currently migrating from a Novell environment to Citrix.
We need to set up some User-level locking on some of the Access DBs, and make sure that they aren't prompted for usernames on the remaining databases. We've published Access97, as that is what 99% of the databases are in, and when they open the database by double clicking on the MDB they open it with the Access RunTime (so they can't alter the MDB structure). Is there a way to enable User level locking on specified access databases within Citrix without locking down all other databases, and without setting the MDW for all users once one user logs on to a secure database.
And - from within Access97 (i.e. once they have opened access) they will either get the login prompt or a no login prompt depending on whether or not we want this MDB locked down.
Boy this sounds complicated once I put it all down in one place! My Google searching hasn't returned any other places where this has taken place. The Access97 runtime is from www.pieproducts.com.

Thanks heaps in advance. Perplexedly yours
Simon Westenra
Capital and Coast District Health
New Zealand

Simon Westenra | 2004.06.21 07:58 PM

We are running an application where the majority of user clients are A97 but a few are A2k. The back end database file is A97. Does anyone know whether the 'record level locking' option has any impact when an A2K client is linked to an A97 database back end?

Andrew Chanter | 2005.04.06 12:09 AM

Oh dear Simon, I never received notification from TypePad that you left a comment. I'm sending you a separate email to see whether any of these issues/questions are still open. I'm so sorry for the delay!

ewbi.develops | 2005.04.06 01:32 PM

Hi Andrew,

Based on what I've read, I have to believe that the record-level locking offered in Access 2000 (via JET 4.0) does not come into play when accessing linked Access 97 database tables. However, the easiest way to find out is to test it, so that's what I did.

Using Access 97, I created a small database having a single table with 2 fields (one being a number, the other being 50 chars of text) and added two rows. On another machine I started Access 2003 (sorry, don't have 2000, but have no reason to think they'll be any different in this regard) and created a new database and added a link to the newly created table in the newly created Access 97 database. I then set the 'Default record locking' option to 'Edited record' in both Access 2003 and Access 97, and enabled the record-level locking option in Access 2003. I closed both instances of Access and verified that the LDB disappeared (indicating a complete closure). Now on to the test.

I started Access 2003 and opened native database with the link in it and then opened the linked Access 97 table in datasheet view. (If this was going to impose record-level locking on the database, it would have done it here.) I then started Access 97, opened the database, and then opened the table in datasheet view. Back in Access 2003, I moved to the text field of row #1 and started typing, thus entering edit mode. I switched back to Access 97, moved down to row #2, and tried typing in the text field but was shown (via the record selector image) that the second row was locked. I moved up to the first row and found its record selector also indicated it was locked. I cancelled the edit in Access 2003 and tried the same thing starting first with Access 97 and got the same results.

This behavior confirms that page-level locking, not record-level locking, was being used.

Hope this makes sense and answers your question.

ewbi.develops | 2005.04.06 02:43 PM

In followup to this, we.ve sorted out how to put an Access 97 or XP database on citrix with Security without locking down all other users to the same MDW file. We've published the application with the command to fire up Access - passing the parameters for the database and the MDW file all in one line.
i.e. "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" /wrkgrp "MyWorkgrp.mdw" "\\MyDatabase.mdb"
It works fine, and now I just have to work out why it gets corrupted occasionally. But that is anotehr topic I'm discussing in
http://www.microsoft.com/office/community/en-us/default.mspx?
and
http://groups-beta.google.com/group/comp.databases.ms-access
Cheers.
Simon Westenra
Capital and Coast District Health, New Zealand

Simon Westenra | 2005.04.12 03:37 PM

Simon,

Thanks for the follow-up and the links. For anyone interested, the actual thread is here:

http://groups-beta.google.com/group/microsoft.public.access/browse_thread/thread/9b76fefc2e533d90/f5738f59af138b24

Interesting note in there from Albert D. Kallal regarding the oplocks setting on W2K. He says it isn't necessary to explicitly disable it (see the thread for Simon's recap of the Registry entries) if you're running SP3. I hadn't heard that. I'll have to do some more research on the subject.

Thanks again!

ewbi.develops | 2005.04.12 06:58 PM

My attempts at resolving database corruption issues with the suggestions above have not been successful. Despite turning off record-level locking, ensuring IT infrastructure is consistent, giving each user their front-end copies; etc etc. I am continually plagued with "unrecognize database format" msgs, which is a cue for me to fix the database backend.

My IT department seems to think it's a programming issue (a nice way of saying that I did not know how to develop a database). I don't have much confidence in Citrix farms or MS Access working well together. Corruption frequency "appears" to be random.

Perhaps when Citrix and Microsoft decide to support each other, develop another version of Jet, will I have confidence in using these two products together ever again. (Call it the Friday blues...!)

MM | 2005.07.07 10:06 PM

MM,

I understand your pain. Even though we've been running on our Metaframe farm now for months without incident, I feel in my bones that it's just a matter of time before something goes wrong. Like you I don't have "much confidence" in Citrix farms or MS Access working well together and I feel like the combination of things I implemented in search of a solution were too opaque to be trusted (in other words, I don't know exactly why these things in this combination work, so I feel like they could stop working at any moment).

I should also mention that in addition to the points made in this post, the application itself was designed to try and minimize corruptions. Specifically, it includes no bound edit forms, reducing open times for tables and queries, and relies on form-specific SQL DML UPDATE and INSERT statements to reduce table activity via open DAO.Recordsets.

Anyhow, I wish you luck. If you learn any more about this, please come back and share it with us.

ewbi.develops | 2005.07.08 03:36 PM

While this is not tested, I suggest that a nice way of dealing with this issue of setting row-level locking is by using a modification of a very small program loader which I often use in my applications, adding the code shown above that deals with the MS Access SetOption("Use Row Level Locking") issue, and then runs loader logic that basically determines if a new version exists, and downloads it if necessary, then launches the given application using the following SHELL command, and then immediately after kills the loader program itself.

This code may be able to avoid the automatic logout from Access to reset the "Use Row Level Locking" option.


If Application.GetOption("Use Row Level Locking") Then
Call Application.SetOption("Use Row Level Locking", False)
End If

'-- Download Access Front-End revised version if necessary --

Shell "MSAccess.exe " & Chr$(34) & "C:\MyPath\MyApp.mdb" & Chr$(34), vbNormalFocus

'followed by

Application.Quit 'to exit the loader program

Neal Miller / Miller Systems Inc. | 2007.04.07 11:15 PM

Hi Neal,

A controlled loader like that does make sense in a lot of cases. Thanks for pointing it out.

ewbi.develops | 2007.04.08 10:28 AM


TrackBack

TrackBack URL:  https://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d8345996b969e2

Listed below are links to weblogs that reference Access Corruption Mystery Solved: