INF: Handling Timeouts Correctly in DB-Library

Article translations Article translations
Article ID: 48712 - View products that this article applies to.
This article was previously published under Q48712
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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);
   }
				

Properties

Article ID: 48712 - Last Review: February 27, 2014 - Revision: 3.1
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
Keywords: 
kbnosurvey kbarchive kbinfo kbprogramming KB48712

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com