When working with SQL Server, there can be multiple application host accessing it, and each application can have one or multiple connections. Each connection can potentially have multiple transactions (please correct me if I am wrong). Each transaction can perform query or non-query SQL.
In my experience, I easily run into timeout if I query a table that is exclusively locked. I also saw SQL Server detect and throw a deadlock exception instead of timeout if two different applications lock on the same resource. I also show rebuilding index timeout, which possible due to someone still have connection to table.
However, I also encounter a kind of deadlock where SQL Server doesn't detect it or timeout out. In this application, it opened two connections, two separate transactions, where 1st transaction locked a resource, and the 2nd transaction try to access the same resource, but it hasn't close the first transaction.
Would someone provide a list of types of timeouts and/or deadlocks, it would help me to avoid these kind of cases when working on the application.
Best Answer
Well from an application point of view there are:
Back in my classic ASP days, the defaults for these were 15 and 30 seconds respectively, I have no idea what they are by default in .NET today.
SQL Server has its own set of timeouts, for example:
You can see these values for your system here:
There is also
@@LOCK_TIMEOUT
(which defaults to -1 (infinity)). This is how long SQL Server will wait on a blocked resource. You can override this for a particular session usingSET LOCK_TIMEOUT
. More details here.Deadlocks I suppose could also fall into this category as well. The system scans for deadlock situations every 5 seconds, and there is no magic formula to determine when the deadlock will occur in relation to when any of the involved requests started. This is because SQL Server doesn't let the oldest transaction win; it chooses the victim based on DEADLOCK_PRIORITY and the estimated amount of resources required to roll the victim back. More details here.
There is also a memory grant timeout (which may be customized using Resource Governor). Depending on concurrency, a query won't necessarily fail if it reaches the timeout before obtaining all of the memory requested, it will just run with the allocated amount (and therefore might be less efficient). If it fails you will likely see Msg 8645.
You can get an idea for other potential timeout scenarios that may occur within SQL Server by reviewing these error messages:
However I don't think it is practical, feasible or productive for anyone to try to provide you with a complete and exhaustive list of every single timeout situation possible. Solve the problems you're having, rather than prematurely solving a whole bunch of problems you probably never will...