Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When working with SQL Server, there can be multiple application host accessing it, and each application can have one or many connection. Each connection can potentially have multiple transaction (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 thrown deadlock exception instead of timeout if two different application locking on each other 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 timeout and/or deadlock, it would help me to avoid these kind of cases when working on the application.

Thanks

share|improve this question
2  
Not a fan of the amount of down votes on this with absolutely no explanation to the user as to why it's down voted. You may want to check out the BOL(books on-line) to get a better understanding of table blocking/Locking. –  Zane Jan 11 '13 at 14:12
1  
If the first transaction only locked a resource and doesn't wait for any other resource (but just sits waiting), it's not a deadlock, so it can't be detected as such. –  ypercube Jan 11 '13 at 14:28
    
No idea why it got vote down, but I am glad that people in the community will still try to help out answer my question. –  dsum Jan 14 '13 at 22:58
add comment

2 Answers

up vote 4 down vote accepted

Well from an application point of view there are:

  • connection timeout (how long the app is willing to wait to establish a connection to SQL Server)
  • command timeout (how long the app is willing to wait for a command to complete, including pulling the results down from SQL Server)

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:

  • Remote query timeout. Default is 600 seconds (10 minutes).
  • Remote login timeout. Default is 10 seconds.
  • Query wait. Default is -1 (infinity).
  • Full-text protocol handler timeout. Default is 60 seconds.

You can see these values for your system here:

SELECT * FROM sys.configurations
WHERE configuration_id IN (1519,1520,1541,1557);

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 using SET 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:

SELECT message_id, [text]
  FROM sys.messages 
  WHERE language_id = 1033 
  AND ([text] LIKE '%timeout%' OR [text] LIKE '%time out%')

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...

share|improve this answer
    
Thanks, your information really helps. –  dsum Jan 14 '13 at 22:57
add comment

There are three different concepts touched on here. Hopefully this gives a good explanation of what they are, and from there you can figure out how to avoid them.

Blocking (aka live locking)
Blocking occurs when a query attempts to acquire a lock, but has to wait in the locking queue before the lock is granted. It can appear from the outside that the query is doing nothing, because it's waiting for the other process(es) to release the lock(s) ahead of it in the queue. Blocking can cause deadlocks if the locks are acquired in a specific order (I describe this below).

Timeouts
Timeouts occur when a client makes a request for a resource and waits for a response. If no response is received within a given period of time, an error is raised by the client instead of waiting forever. Timeouts can occur for a variety of reasons (blocking, or a query doing a ton of work, or the network was really slow, or ...), but all ultimately because the client was waiting, and decided that it didn't want to wait any more.

Deadlocks
Deadlocks occur when two or more processes hold locks on resources, and also try to take locks on resources held by the other process(es). This creates a situation such that neither query can continue unless one of them is terminated/rolled back. I demonstrated how this works in a demo video here.

share|improve this answer
    
I expanded on this answer in a blog post here: voluntarydba.com/post/2013/01/22/… –  Jon Seigel Jan 22 '13 at 14:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.