Can we set LOCK_TIMEOUT instance- or database-wide?

Question

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?

• user507: Wouldn't "server" and "instance" be the same thing?
• Fulproof: 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: "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

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.

• Fulproof: 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
• Thomas Stringer: Give me a scenario when you would want to use a nondefault lock timeout.
• Fulproof: It is not on scenario, it is after I set it. From the moment I set it further on
• user507: 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.

Answer 2

Blocking leads to worker thread exhaustion. It is better to allow a lock timeout that is explicit, rather than a command timeout that is ambiguous.

Using Lock timeout set < command timeout you can tell the user they have had a collision with another session. If they reach command timeout it is just a 'heavy' statement.