PRB: Dealing with locks and timeouts in DB-Library (47603)

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.