INF: Handling Timeouts Correctly in DB-Library

• Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

In SQL Server, an application attempting to access data that is locked by another user will be blocked until the lock is released. If this causes a deadlock (both requesting locks that the other holds), SQL Server will immediately terminate one of the participants (no timeout is involved).

If there is no deadlock, the application requesting the locked data will be blocked until the other user chooses to release the lock. There is no mandatory timeout period, and there is no way to test whether or not a data item is locked, except to attempt to access the data (and potentially get blocked indefinitely).

With DB-Library (DB-Lib), it is possible to continue issuing commands after a timeout has occurred. To regain control after a timeout period, do the following:

1. Use dbtimeout() to set a nonzero timeout value.

2. Within the error handler, test for dberr of SQLETIME to determine if a timeout has occurred.

3. If a timeout has occurred, your error handler has the option of either returning control to DB-LIB for another timeout interval (DB_CONTINUE) or canceling the current command (DB_CANCEL).

4. If DB_CANCEL is used, an attention will be sent to the server and control will be returned to the statement following the dbsqlok(), dbresults(), or dbnextrow() that was waiting.

5. Do NOT call dbcancel(dbproc) from within the error handler if a timeout has occurred. This is true even if the error handler address has been set to null to prevent recursive calls to the error handler. Instead, have your error handler return INT_CANCEL. When the error handler returns INT_CANCEL after a timeout, DB-Lib will send a cancel request to SQL Server and will continue reading data.

MORE INFORMATION

Sample Program

   #define INCL_BASE
   #define DBMSOS2
   #include <os2.h>
   #include <stdio.h>
   #include <sqlfront.h>
   #include <sqldb.h>

   DBPROCESS *dbproc;
   int msg_handler();
   int err_handler();
   static int timeout=FALSE;

   main()
   {
   LOGINREC  *login;

        login = dblogin();
        DBSETLUSER(login,"sa");
        dbproc = dbopen(login, "server");
        dbmsghandle(msg_handler);
        dberrhandle(err_handler);
        dbsettime(5);

        dbcmd(dbproc,"select command from sqlhelp");
        dbsqlexec(dbproc);
        timeout_processing();
        while( dbresults(dbproc) != NO_MORE_RESULTS )
        {
          while( dbnextrow(dbproc) != NO_MORE_ROWS )
          {
            .
            .
            .
          }
        }
   }


   int msg_handler(dbproc, msgno, msgstate, severity, msgtext)
   DBPROCESS   *dbproc;
   DBINT       msgno;
   int         msgstate;
   int         severity;
   char        *msgtext;
   {
       printf("SQL Server Message %ld, state %d, severity %d:\n%s\n",
               msgno, msgstate, severity, msgtext );
       return(DBNOSAVE);
   }


   int err_handler(dbproc, severity, dberr, oserr, dberrstr,
                   oserrstr)
   DBPROCESS   *dbproc;
   int         severity;
   int         dberr;
   int         oserr;
   char        *dberrstr;
   char        *oserrstr;
   {
       if( dberr==SQLETIME )      <--- test for timeout condition
         timeout=TRUE;
       else
       {          printf("DB-LIB Error %d,\n%s\n",dberr,dberrstr);
         if( oserr>0 )
           printf("OS Error %d,\n%s\n",oserr,oserrstr);

       }
       return(INT_CANCEL);
   }