Connections...

Dear All,
SQL Server 2000, Service Pack 3a
We have a problem with locking where as soon as something deadlocks the
application fails.
There are two solutions that spring to mind, either totally re-writing the
Application or setting the SET LOCK_TIMEOUT command.
Now currently we cannot do the re-write as the developers don't have time (I
was out voted) so we are looking to the Set Lock_Timeout, but here is the
problem.
The Connections used are set by JBOSS and our developers cannot find a way
of setting the SET LOCK_TIMEOUT to the connection through JBOSS.
What I was wondering then was is there a way through SQL, i.e. Whenever a
connection is created it automatically does a SET LOCK_TIMEOUT ?
If not could someone go through how a connection is actually created?, so I
can attempt to put a trigger somewhere.
I would totally agree that this is a 'hack' fix and does not solve the
underlying problem but they want a quick fix
JJulie,
changing the Lock timeout won't solve you're problem. The Lock timeout
only defines how long a query wait for a resource until a exlusive lock
is released. By default a query waits indefinetely until the lock is
released.
Deadlocks are different. SQL Server detects if a deadlock occurs and
the kills one process, the deadlock victim. This happens very fast, so
the time the resource is locked would be shorter than your timeout.
You need to catch the error 1205 and the handle it in your application.
I don't think there's a alternative for that.
Markus