PRB: Updated Data Delayed by Five (5) Millliseconds in Jet 3.x |
Q153046
The information in this article applies to:
- Microsoft Windows NT Server version 4.0
- Microsoft Windows NT Workstation version 4.0
- Microsoft Visual C++, 32-bit Professional Edition, versions 4.0, 4.1
IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.
SYMPTOMS
When two users are trying to gain access to the same Access database using Jet 3.x, there is a delay before data modified by one user becomes visible to the other user even if the code re-queries the database.
CAUSE
In order to improve performance, Microsoft Jet version 3.x uses threads to process read-ahead and write-behind caches asynchronously. Jet 3.0 defines a key in the registry that determines the delay before the internal cache is refreshed.
The PageTimeout property specifies the length of time, in milliseconds, between when data is placed in an internal cache and when it is checked to be potentially invalidated. Jet 3.x has a default value for the PageTimeout property of five (5) seconds, which means that the shared data will be refreshed after no more than five (5) milliseconds.
RESOLUTION
To ensure that the data is being written out to the disk, set PageTimeout to a value that is appropriate for your system and wait at least that amount of time before checking for the updated data.
Note that setting the value of PageTimeout too low may adversely affect system performance.
There are two techniques for setting the PageTimeout value:
- Manually edit the registry. WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk. For information about how to edit the registry, view the "Changing Keys And Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD). On Windows NT, run REGEDT32.EXE and select the HKEY_LOCAL_MACHINE hive. On Windows 95, run REGEDIT.EXE. Go to HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.0\Engines\Jet and HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Engines\Jet3.5 for Jet 3.5. You may need to add the last "Jet" key. Make a REG_DWORD entry into the key called PageTimeout, and set the value to an appropriate value, in milliseconds.
- Make the changes to the system registry programmatically. The sample code below demonstrates how to achieve this.
NOTE: Starting with DAO 3.5, you can use the Idle method with the dbRefreshCache parameter to perform the same function as the PageTimeout. See the online DAO 3.5 SDK documentation for more information. This method is preferred because changing the registry entry affects all applications.
STATUS
This behavior is by design.
MORE INFORMATION
There are two factors involved in determining when updated data becomes visible to another user: the data must be written to the database and the updated data must become visible to the other user. The PageTimeout property addresses only the second issue; it specifies when the updated data becomes visible to another user after it has been written to the database.
The simplest way to ensure that the data is written to the database is to wrap the update in a transaction. The issue of when updated data is written to a database is affected by:
- whether explicit transactions are used.
- whether transactions are synchronous.
- the size of the internal cache.
- the delay before asynchronous changes are written out.
- whether the database is opened exclusively.
If an explicit transaction is used, then no data will be written to the database until the transaction is committed. When the transaction is committed, the value of the UserCommitSync key in the registry determines whether the data is written synchronously or asynchronously. In synchronous mode, the data is written before the commit returns. The default for explicit transactions is synchronous.
If explicit transactions are not used, then an implicit transaction is used and the value of ImplicitCommitSync determines whether the data is written synchronously or asynchronously. In synchronous mode, the data is written before control returns to the application code. The default for implicit transactions is asynchronous.
In asynchronous mode, changes are stored in a cache. A worker thread writes the data from the internal cache to the database when one of two things happens: the buffer pool becomes full (MaxBufferSize is exceeded) or a period of time passes after the first stored change. This period of time is specified by the SharedAsyncDelay key in the registry, or by the ExclusiveAsyncDelay key if the database is opened exclusively.
If you are considering modifying the default values for transaction synchronization, please see the article in the Microsoft Knowledge Base:
Q153491 PRB: Jet 3.0 UserCommitSync & ImplicitCommitSync Values Wrong
Note that Microsoft Jet version 3.0 uses different interpretations of the registry entries than previous versions of Microsoft Jet.
You should also note that changing the ODBC Page Timeout property will have no effect on this issue. The ODBC Page Timeout value is displayed in the ODBC Administrator under the Setup Options for a data source, and in the registry under the HKEY_CURRENT_USER\Software\ODBC\ODBC.INI key for the data source.
Sample Code
#include <winreg.h>
int SetPageTimeoutInSystemRegistry( )
{
HKEY hkey = HKEY_LOCAL_MACHINE;
HKEY hResult;
char szSubKey[]=_T( "Software\\Microsoft\\Jet\\3.0\\Engines\\Jet" );
char szValue[]=_T( "PageTimeout" );
char szClass[]=_T( "" );
DWORD TimeOut = 20; // set to desired timeout value, in milliseconds
LONG lRetVal;
DWORD dwDisp;
// If key doesn't exist, it will be created.
lRetVal = RegCreateKeyEx( hkey, szSubKey, 0, szClass,
REG_OPTION_NON_VOLATILE,
KEY_ALL_ACCESS, 0, &hResult, &dwDisp );
// check the return value
switch( lRetVal )
{
case ERROR_SUCCESS:
case ERROR_FILE_NOT_FOUND:
if( ERROR_SUCCESS != RegSetValueEx( hResult, szValue, 0,
REG_DWORD, (const unsigned char *)&TimeOut, 4 ) )
{
::MessageBox( NULL, _T("error"), _T("error"), MB_OK );
return 0;
}
break;
// default is error condition
default:
::MessageBox( NULL, _T("error"), _T("error"), MB_OK );
break;
} // end of switch
// Must close the key.
if( ERROR_SUCCESS != RegCloseKey ( hResult ) )
{
::MessageBox( NULL, _T("error"), _T("error"), MB_OK );
return 0;
}
// Success!
return 1;
}
REFERENCES
Microsoft Jet Database Engine Programmer's Guide.
Additional query words: 4.00 4.10
Keywords : kbprogramming kbusage kbDAOsearch _IK kbMFC kbODBC kbVC kbprb
Issue type : kbprb
Technology : kbVCsearch kbVC400 kbWinNTsearch kbWinNTWsearch kbWinNTW400 kbWinNTW400search kbWinNT400xsearch kbWinNTSsearch kbWinNTS400xsearch kbWinNTS400 kbAudDeveloper kbVC410 kbVC32bitSearch
Last Reviewed: October 27, 2000 |
