Microsoft KB Archive/48712

= INF: Handling Timeouts Correctly in DB-Library =

Article ID: 48712

Article Last Modified on 12/3/2003

-

APPLIES TO


 * 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

-



This article was previously published under Q48712



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.



Sample Program
#define INCL_BASE #define DBMSOS2 #include   #include    #include    #include 

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

main {  LOGINREC  *login;

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

dbcmd(dbproc,&quot;select command from sqlhelp&quot;); 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(&quot;SQL Server Message %ld, state %d, severity %d:\n%s\n&quot;,               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(&quot;DB-LIB Error %d,\n%s\n&quot;,dberr,dberrstr); if( oserr>0 ) printf(&quot;OS Error %d,\n%s\n&quot;,oserr,oserrstr); }      return(INT_CANCEL); }

Additional query words: dblib Windows NT

Keywords: kbinfo kbprogramming KB48712

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.