PRB: Updated Data Delayed by Five (5) Seconds in Jet 3.x

The information in this article applies to:

• Microsoft Visual C++, 32-bit Edition, versions 4.0, 4.1

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) seconds.

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:

1. Manually edit the registry. 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.

2. Make the changes to the system registry programmatically. The sample code below demonstrates how to achieve this.

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 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:

   ARTICLE ID: Q153491
   TITLE: PRB: Jet 3.0 UserCommitSync & ImplicitCommitSync Values Wrong

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.