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.

It is possible to change the SQL Server's default (which is -1 or indefinite period of time) LOCK_TIMEOUT value session-wide.

Is it possible to set LOCK_TIMEOUT default value server-, instance- and/or database-wide? If not, why not?

share|improve this question
    
Wouldn't "server" and "instance" be the same thing? –  Shawn Melton Dec 11 '13 at 8:14
    
Probably. Really I'd like to set it database-wide or instance-wide. I do not see any sense in impossibility to change indefinite hanging on locked resource behavior or I'd like to understand why it is not configurable. Or might be I am missing this functionality –  Fulproof Dec 11 '13 at 8:53
    
"Instance" and "server" are synonyms in this context as far as opening an instance properties shows a window titled "Server Properties". People seeking for this kind of questions will most probably use "server" keyword in their searching –  Fulproof Dec 12 '13 at 4:04
add comment

1 Answer 1

No it isn't possible to set this as a default and change it from -1 (no timeout).

If it needs to be changed, you have to be explicit with a call to SET LOCK_TIMEOUT <number of ms for timeout>.

As for why there is no way to alter the default, we can only speculate. But my guess would be that the lack of a timeout would satisfy the majority of requirements for queries, and need to have a lock wait timeout is a pretty corner case scenario, requiring a simple SET statement.

Regardless, you're talking about client-side handling. Not server-side. In fact, most providers that I work with by default enforce a command timeout (i.e. System.Data.SqlClient.SqlCommand.CommandTimeout is set to a default of 30 seconds). Although a bit of a nested requirement with lock requests, it's the same idea.

share|improve this answer
    
Thanks but speculation on satisfying the majority of requirements is wrong. The majority probably uses SQL Server as storage media of out-of-the-box applications without ability to mess with their code base. Even for home-developed apps it is hardly "corner case scenario". And I am talking about database administration ... through its server management –  Fulproof Dec 12 '13 at 0:27
    
Give me a scenario when you would want to use a nondefault lock timeout. –  Thomas Stringer Dec 12 '13 at 0:51
    
It is not on scenario, it is after I set it. From the moment I set it further on –  Fulproof Dec 12 '13 at 2:52
    
As it states in the BOL link Thomas provided it can only be set at the session level. Microsoft provides no other documented method at the database or instance level. –  Shawn Melton Dec 12 '13 at 4:11
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.