Deadlock should not be confused with waiting for access. In SQL Server, there is no time-out period for deadlocks. One of the participating processes is immediately aborted. The dbproc can still be used. The effect is the same as though a trigger procedure caused a ROLLBACK to be executed. Microsoft has confirmed this with two copies of ISQL.

On the other hand, if a process requests a row that is being used by another process, the first process will wait until the second process releases the row. This is not a deadlock. A deadlock would occur if the second process were also waiting for the first process (or any closed loop of waiting processes). In the non-deadlock case, the second process may release the row at any time, and the first process will wait until it does (or until the user-specified time-out expires). If dbsqlexec returns via time-out, the user-defined error handler will be called with message 10003 and dbsqlexec will return FAIL. The dbproc will be dead and must be reopened. Microsoft has confirmed this, also. Also, remember to check your return codes.