Microsoft KB Archive/47603

= PRB: Dealing with locks and timeouts in DB-Library =

Article ID: 47603

Article Last Modified on 3/14/2005

-

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 Q47603



SYMPTOMS
SQL Server does not notify an application when a nondeadlocking lock collision occurs with another application. Since the other application could hold the lock for a long time, there should be some way for an interactive application to regain control and do something else. The same is also true for queries that take a long time to process.



WORKAROUND
Rather than sending the command via dbsqlexec, use dbsqlsend. Then use dbdataready repeatedly to determine when results have begun to arrive. If too much time has elapsed, issue dbcancel to abort the current command. If results have arrived, issue dbsqlok and if the command was successful, enter the normal dbresults\dbnextrow loop.

This technique allows the application to regain control before results have begun to arrive and also between calls to dbresults and dbnextrow. Once a call to dbresults or dbnextrow has been issued, the only way for the application to regain control is via a timeout.

Unfortunately, the dbproc is marked DEAD when timeout occurs. Although it may be possible to clear the DEAD flag and continue processing, the prescribed technique is to reopen the dbproc.

In either case, there is no way to determine whether the timeout is due to a lock collision or just a long-running query.

Additional query words: Optimization and tuning Windows NT dblib

Keywords: kbprogramming KB47603

-

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

© Microsoft Corporation. All rights reserved.