Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-23-2019, 02:49 PM   #16
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,588
Thanks: 92
Thanked 1,684 Times in 1,562 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Rename Database and Corruption Error Goes Away?

Micron - you would be right except that the letters give an explicit mechanism related to the advent of SMBv2 and v3, and in one of the letters we learned that SMBv2 is now the default for Win7 and Win10.

You can have ordinary locking, but the advanced SMB versions allow for "Opportunistic" or "Leased" file locking. The purpose of the new locking options is to reduce network traffic by taking over the buffer for a longer time and not forcing a writeback every time your actions would update the contents of the buffer. (SMBv1 by contrast WOULD have to write back the buffer every time you update its contents.)

The new methods force an exclusive lock even though Access is opened for sharing because that is the only way to safely NOT write back the buffer with every update. You can only defer the write back if you know nothing else can happen to the buffer.

According to the letters, what happens is that when there is a lock "collision" with one of the new lock types, you have to "break" the lock (which was exclusive, remember). And apparently there is an issue with breaking exclusive locks that could lead to buffer invalidation. This next statement is conjectural on my part based on my understanding of operating systems: It sounds like there might be a race between the former lock owner and lock breaker and if the breaker wins, the buffer is invalidated and that could lead to destructive interference - i.e. database in an inconsistent state.

At LEAST until Microsoft can figure out what is eating their locks on this one, it would be wise to close anything that no longer needs to be open, because as long as it is open, the buffer is still locked since the optimization is to defer write back operations as long as possible. The way to prevent the problem is to always close anything that potentially involves any disk buffers. Recordsets involve disk buffers so definitely would run afoul of the problem. The letter also mentioned xxxdef structures that could be opened and that should be closed sooner rather than later.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-23-2019, 06:25 PM   #17
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,109
Thanks: 10
Thanked 222 Times in 210 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Rename Database and Corruption Error Goes Away?

I don't get what your "lock" comments have to do with what I wrote about, which was destroying (or not) objects that one creates and whether or not failing to do so does or doesn't induce corruption.
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
Tera (06-23-2019)
Old 06-23-2019, 08:42 PM   #18
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,588
Thanks: 92
Thanked 1,684 Times in 1,562 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Rename Database and Corruption Error Goes Away?

Ah, simple enough. Let's use a recordset for the discussion.

The letter clearly suggests that ANY object on a disk will be managed through Windows file locks and implies that the locks are per buffer as opposed to per file. A recordset object refers to the contents of a table directly or through a query. That set of records resides on a disk so will be the point of corruption if this error occurs.

For Access, the mechanism of corruption is almost always the same though often from various different first steps. You corrupt a database when you start making changes to that database but don't finish the process. This leaves the database in an inconsistent, half-updated state. The "inconsistent state" error message we have been discussing is quite literal here.

Based on what I read in the two letters, this inconsistent state occurs when Access opens a file for sharing BUT Windows in its infinite (?) wisdom takes out a non-shared lock on a buffer AND sets up one of the new buffering optimization schemes.

In older versions of Windows, the SMB protocol would write back buffer contents every time you updated the buffer, even if your next operation was in the same buffer. In SMBv2 and v3, though, they have an option to NOT write that back immediately. They do that to reduce network traffic.

When you have a recordset open and there is a currently selected record, the active copy of that record resides in a memory buffer corresponding to the place on the disk where that record resided BEFORE you opened the recordset. So you have this recordset and do some sort of .Edit/.Update sequence and the active buffer gets updated in memory. The new protocols, however, allow that buffer to NOT get updated to disk right away.

The optimizer cannot tell why you pause after your most recent update to the recordset. So it does nothing. That new optimization doesn't know that you are done with the recordset object until you CLOSE the recordset and start to destroy it. At that point, the lock and buffer optimization must flush the modified buffers back to the disk. This is where your "destroying (or not)" issue kicks in.

IF before this point of closure, another thread or a different part of your own process thread try to touch that buffer again, you have to unlock it. According to what the letter and linked articles describe, the buffer can be invalidated before it gets written back. If you were doing a sequence of updates and the last buffer doesn't get written back correctly, you now have a corrupt database.

The way to avoid that is to CLOSE whatever object you opened and set it to Nothing. That action would flush the disk buffer and destroy the object. That will prevent the corruption.

Did that help you see the mechanism any better?

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-01-2019, 05:29 AM   #19
swat
Newly Registered User
 
Join Date: Jun 2013
Posts: 17
Thanks: 3
Thanked 4 Times in 1 Post
swat is on a distinguished road
Re: Rename Database and Corruption Error Goes Away?

Quote:
Originally Posted by HiTechCoach View Post
Do your database with the issue have any VBA code?
Yes, the databases that encountered problems all had VBA code in them.
swat is offline   Reply With Quote
Old 07-01-2019, 05:33 AM   #20
swat
Newly Registered User
 
Join Date: Jun 2013
Posts: 17
Thanks: 3
Thanked 4 Times in 1 Post
swat is on a distinguished road
Re: Rename Database and Corruption Error Goes Away?

Quote:
Originally Posted by Micron View Post
I learned that the decompile switch is supposed to take care of this situation; i.e. it completely rebuilds the compiled code based on what you see as the written code, which as you say, can get out of sync.
Thanks Micron! I'm pretty sure I tried the decompile (I was trying everything in the book) but I for sure will try that again the next time it happens.

swat is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Rename Database and Corruption Error Goes Away? swat General 17 09-17-2018 06:22 AM
database corruption? BennyLinton Queries 7 06-19-2018 07:34 AM
Suspected database corruption - error after compacting GS500 General 2 02-04-2015 07:17 AM
Database corruption Error "AOIndex is not an index in this table" MsLady Forms 8 12-14-2014 10:53 PM
Database Corruption Darren26 General 22 07-13-2005 04:49 AM




All times are GMT -8. The time now is 06:18 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World