SQL Server Performance Forum – Threads Archive

auto lock release?

We have several java app servers that use a SQL Server 2k table to keep track of who is hosting each account. There seems to be a problem when one of these servers crash that the table is locked and never released. Is there a way to set a property for the table that will prevent locks from lasting more than a specified time period? Any other ideas on how to fix this? thanks

From BOL: NOLOCK
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement. HTH Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

?? I think the better option is to fix your application though. The NOLOCK reads will ensure you’re not blocking people and causing contention. The orphaned connection that’s causing this problem in the first place though will still be there consuming resources. I would consult a java expert on the java connection object. It’s obviously not setup right if it’s not breaking the connection or allowing it to die on failure. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

We’re trying to figure out the Java side of it, but in the meantime want a way to prevent everything from crashing when this occurs. I’ve manually tested (in T-SQL) setting LOCK_TIMEOUT at the beginning of our suspected INSERT statement. It never times out and remains locked until I close T-SQL, which I assume means the connection itself needs to be closed to kill the lock. Is there a way to timeout a connection? All of the connection timeout properties I’ve seen are related to connection initialization, not timing out an active connection.
Thanks for all recommendations.

Check the user connection timeout setting on SQL Server (Server properties) and set QUERY timeout value. What was the error while crashing everything out? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>