You are currently offline, waiting for your internet to reconnect

INF: Handling Timeouts Correctly in DB-Library

This article was previously published under Q48712
This article has been archived. It is offered "as is" and will no longer be updated.
In SQL Server, an application attempting to access data that is lockedby another user will be blocked until the lock is released. If thiscauses a deadlock (both requesting locks that the other holds), SQLServer will immediately terminate one of the participants (no timeoutis involved).

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

With DB-Library (DB-Lib), it is possible to continue issuing commandsafter 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.

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);   }				
dblib Windows NT

Article ID: 48712 - Last Review: 02/27/2014 18:29:37 - Revision: 3.1

Microsoft SQL Server 4.21a Standard Edition, Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbinfo kbprogramming KB48712