java SQL 服务器“超出锁定请求超时期限”.. 再次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6468304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL server "Lock request time out period exceeded" .. again
提问by francisco
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:
我在尝试延长 sql server SP 中的锁定超时时遇到问题。无论我尝试什么，它都会不断抛出“超出锁定请求超时期限”。我正在使用 java + jtds 1.2.2、c3p0 0.9.1 和 sql server 2008。我尝试的设置：
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 );
SET LOCK_TIMEOUT 10000 inside the SP and with con.createStatement().execute("SET LOCK_TIMEOUT 10000 ")在致电 SP 之前。并在 SP 语句中： 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
SP 由 : s 调用tatement = con.prepareCall("dbo.store_procedure ?,?,?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);，它在里面设置了“SET TRANSACTION ISOLATION LEVEL REPEATABLE READ”
any sugestions? anyone with similar problems? thanks in advance
任何建议？有类似问题的人吗？提前致谢
回答by Thiago Dantas
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.
如果您不使用 SQL Server 2008 及更高版本，并指定 LOCK_ESCALATION=Disabled，您可以忘记 ROWLOCK 提示。SQL Server 可以，根据我自己的经验，可能会忽略它并获取它填充的任何锁（页面或表）。SQL Server 2008 之前没有强制锁定提示。
Having cleared that, you can use SET LOCK_TIMEOUT -1to specify an endless timeout.
清除后，您可以使用SET LOCK_TIMEOUT -1指定无限超时。
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 TargetSPIDto check what kind of locks are actually being taken
监视锁定的资源EXEC sp_lock TargetSPID以检查实际使用的锁定类型
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
另一个备注：SET LOCK_TIMEOUT 设置当前连接的超时时间，如果您使用的是连接池，则您正在为重用该连接的所有内容设置锁定超时，这可能会导致应用程序中出现意外行为
回答by zfz
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
选择@@TRANCOUNT
Use the above command to check any existing transaction ; commit or rollback the same .
使用上面的命令检查任何现有的交易；提交或回滚相同。
回答by Teju MB
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
选择@@TRANCOUNT
Use the above command to check any existing transaction ; commit or rollback the same .
使用上面的命令检查任何现有的交易；提交或回滚相同。
