SQL Server "Lock request timed out" .. again

Question

I had a problem trying to increase the lock timeout in SQL Server SP. No matter what I tried, it keeps throwing a “Lock Request Timeout”. I am 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 SP. and in the SP expression: statement.setQueryTimeout (10);

SP is called: s tatement = con.prepareCall("dbo.store_procedure ?,?,?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); and it sets the "SET PICTURE PICTURE LEVEL" inside

any sugestions? anyone with similar problems? thank you in advance

Answer 1

If you are not using SQL Server 2008 onwards and specify LOCK_ESCALATION = Disabled, you may forget about the ROWLOCK hint. SQL Server can, and from its own experience, probably ignore it and take any lock (page or table) that it fills. There are no forcing hints to block before SQL Server 2008.

SET LOCK_TIMEOUT -1 removing this, you can use SET LOCK_TIMEOUT -1 to specify an infinite timeout.

I am very much inclined to have you do this and instead try to troubleshoot and optimize (provided that you are responsible for it) queries that really require locking on this table, speed them as much as possible to reduce the lock time.

Monitor locked resources with EXEC sp_lock TargetSPID to check which locks are actually running

One more note: SET LOCK_TIMEOUT sets a timeout for the current connection, if you use a connection pool, you set a lock timeout for everything that reuses this connection, which can lead to unintended behavior in your application.

Answer 2

Check if any transactions are ongoing or not. One reason for this problem is to have transactions with incomplete or not rolling back.

SELECT @@ TRANCOUNT

Use the command above to verify any existing transaction; commit or cancel the same.

Answer 3

Check if any transactions are ongoing or not. One reason for this problem is to have transactions with incomplete or not rolling back.

SELECT @@ TRANCOUNT

Use the command above to verify any existing transaction; commit or cancel the same.