I am maintaining a program that reads records from a Access '97 table using a Timer. A different program outside my control writes records to this table. So the reading and writing is not synchronized. I am wondering if this could occasionally lead to data corruption. Writing a testing environment is difficult for several reasons. Does someone know the answer to this, so that I can save myself the trouble of writing an emulator?
A:
By "Access 97" I assume you're referring to old Jet 3.x format MDB files. The locking and concurrency control was weaker in this older MDB format than in the more modern Jet 4.x format, so you'd gain a boost in robustness by abandoning the old format from the 1990s. There is also better support for row-level locking in Jet 4.x, which could have an impact on performance.
But people used to use Jet 3.x tables to simulate queues all the time back in the old days. I don't recall significant problems that could be traced to data corruption. If that's what you're doing we've had MSMQ for a long time though, which offers some real benefits, for example greatly reduced network overhead over a Jet table solution.
The most common source of data corruption in Jet MDBs though is a client that dies or loses file server connectivity while holding dirty pages.
Bob Riemersma
2010-06-26 19:01:36
@Bot Riemersma: please give some documentation on your claim that "locking and concurrency was weaker" before Jet 4.0. I've never heard this claim anywhere, and I've been developing in Access professionally since 1996. I'd expect that if this were one of the many improvements in Jet 4.0 that it would have been publicized at some length. Perhaps you can provide the pointers to info. I missed.
David-W-Fenton
2010-06-26 22:05:47
"Better support for row-level locking." Well, Jet before version 4 had NO record-level locking, so I guess that's true. But it didn't work in all situations and you had to be careful you didn't do things that subverted row-level locking (DAO couldn't use it, if I recall correctly). In general, row-level locking was too inefficient to be usable, and most Access developers I know of ignored it.
David-W-Fenton
2010-06-26 22:07:14
"used Jet 3.x tables to simulate queues": I have no idea what this means. What's a queue?
David-W-Fenton
2010-06-26 22:07:59
I think that this answer is 100% preposterous and has no truth in any part of it. Any interaction with MS Message Queuing is going to be exactly the same with Jet 3.x and Jet 4.0.
David-W-Fenton
2010-06-26 22:08:56
Do you have any URLs for your comments about locking and cocurrency control being weeker in Jet 3.x format MDB files? I disagree with your comment about the causes of corruption being holding dirty pages. The corruption happens in that very samll moment of time when Access/Jet is actually updating the Access database file pages. I've had power failures at clients with 25 users in doing updates and such. When the power came back on the database was not flagged as being corrupted.
Tony Toews
2010-06-26 22:45:23
By default Jet does not write updated pages to disk immediately. Most writes are done on an async basis after one or more of the delay settings expires. This was not meant as an indictment of Jet, just a fact. When many clients are performing updates at the rate of human input it is seldom a problem but the potential is always there and I stand by my opinion that this is where most MDB corruption comes from. The code in the Jet engine itself is quite robust and bug fixes have been made available quite quickly for the exceptions found over the years.
Bob Riemersma
2010-06-27 13:36:53
A few references describing the advatnages of Jet 4.0 over Jet 3.x include: http://support.microsoft.com/kb/275561 http://support.microsoft.com/kb/303528 http://support.microsoft.com/kb/240317 http://msdn.microsoft.com/en-us/library/aa189633(office.10).aspx http://msdn.microsoft.com/en-us/library/aa165435(office.10).aspx As for some of these other comments, I find them to range from simply ignorant to pointlessly inflammatory. Row-level locking for example is only appropriate where it's appropriate, there is no question of "inefficiency" involved and people use it every day.
Bob Riemersma
2010-06-27 13:52:46
The question is not "the advantages of Jet 4.0 over Jet 3.x" but a very specific assertion of yours, that locking and concurrency control are weaker in Jet 3.x. Citing a bunch of URLs does nothing at all -- point out the specific places where those articles say things that support your assertion.
David-W-Fenton
2010-06-27 22:08:32
As to record-level locking, it is one of those things that sounds like it's more useful than it really is. It's like pessimistic vs. optimistic locking -- it seems logically that pessimistic would be better, but, in fact, it's simply not. While record-level locking can help out in a few situations where page locking cause problems, those situations are not as common as all that *if* you're using optimistic locking, and *if* you've designed your schema appropriately.
David-W-Fenton
2010-06-27 22:10:09
It is clear nothing is going to convince you that your convictions are incorrect. Ask for citations and I provide them. Nothing changes, as you continue to argue the point. We don't have the space here to educate you, you will have to do some reading and research of your own. Your hostile tone is unappreciated.
Bob Riemersma
2010-06-27 23:17:18
Providing general citations that don't clearly address the assertion is not providing any useful support for your assertions. Point out exactly where those articles support what you've claimed and maybe you can win your point. If you don't want to do that, then you look lazy, or as though you recognize that your original point was simply wrong.
David-W-Fenton
2010-06-28 20:18:09
My hostility comes to the posting of erroneous assertions -- particularly when you don't seem willing to actually stand behind those assertions in any meaningful way.
David-W-Fenton
2010-06-28 20:18:43