SQL server "Lock request time out period exceeded" .. again

Question

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

Solution 1

Check whether some transaction are still in progress or not. One cause for this issue is to have uncommitted or non-rollback transactions.

SELECT @@TRANCOUNT

Use the above command to check any existing transaction ; commit or rollback the same .

Solution 2

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