SQL server "Lock request time out period exceeded" .. again
I'm having a problem trying to extend the lock timeout in a sql server SP. No matter what I try it keeps throwing "Lock request time out period exceeded". I'm using java + jtds 1.2.2, c3p0 0.9.1 and sql server 2008. The settings I tried:
SET LOCK_TIMEOUT 10000 inside the SP and with con.createStatement().execute("SET LOCK_TIMEOUT 10000 ") before calling the SP.
and in the SP statement : statement.setQueryTimeout( 10 );
The SP is called by : statement = con.prepareCall("dbo.store_procedure ?,?,?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
and it sets "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ" inside
any sugestions? anyone with similar problems? thanks in advance
Answer
If you are not using SQL Server 2008 and onwards, and specifying LOCK_ESCALATION=Disabled, you can forget about the ROWLOCK Hint. SQL Server can, and from my own experience, probably will ignore it and take any lock (page or table) it fills like. There is no forcing locking hints before SQL Server 2008.
Having cleared that, you can use SET LOCK_TIMEOUT -1 to specify an endless timeout.
I highly discourage you to do so and instead try and troubleshoot and optimize (assuming you're responsible for this) the queries that really need to take locks on that table, speed them up as much as possible to reduce time locked.
Monitor locked resources with EXEC sp_lock TargetSPID to check what kind of locks are actually being taken
Another remark: SET LOCK_TIMEOUT sets the timeout for the current connection, if you are using a connection pool, you are setting the lock timeout for everything that reuses that connection possibly causing unintended behavior in your application