PRB: Shared Data Update Delayed by 5 Seconds in Jet 3.x (153046)

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:



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, &amp;hResult, &amp;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 *)&amp;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;
   }