SET LOCK_TIMEOUT

[PNC]

Hi,

I'm having lots of blocking in my DB, and I always need to kill the blockers in order for the web application to function properly (update, insert, delete), so I would like to know if it's possible to use SET LOCK_TIMEOUT for every session started in the DB?

Thanks,
PNC.

 

[Catadmin]

Remarks
At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection.

The setting of SET LOCK_TIMEOUT is set at execute or run time and not at parse time.

The READPAST locking hint provides an alternative to this SET option.

Permissions
SET LOCK_TIMEOUT permissions default to all users.

Does this help you out?

It doesn't say execute time of what, but I assume you'd have to go in and set it on all your code. It looks like this works per query as opposed to just generically on the server.

On the other hand, if you go into Server Properties to the Server Settings tab, there is a "Use query governer to prevent queries from exceeding a certain cost" that you could use to cut queries off, server wide, when they hit a certain point. There's work involved in setting up the costs, but it might be worth it.

There's also a query timeout on the Connections Tab under Remote Server Connections.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???