ID Number: Q47603
1.10 1.11 4.20
OS/2
Summary:
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.
RESOLUTION
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 reference words: Optimization and tuning