SQL Server“超出锁定请求超时期限” .. 再次
我在尝试延长 sql server SP 中的锁定超时时遇到问题。无论我尝试什么，它都会抛出“超出锁定请求超时期限”。 我正在使用 java + jtds 1.2.2、c3p0 0.9.1 和 sql server 2008。 我尝试过的设置：
在 SP 内设置 LOCK_TIMEOUT 10000，并在调用 SP 之前使用 con.createStatement().execute("SET LOCK_TIMEOUT 10000 ")。 并在 SP 语句中：statement.setQueryTimeout( 10 );
SP 的调用方式为： statement = con.prepareCall("dbo.store_procedure ?,?,?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 中设置“SET TRANSACTION ISOLATION LEVEL REPEATABLE READ”
并在任何建议 ？有人有类似的问题吗？ 提前致谢
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
检查某些交易是否仍在进行中。导致此问题的原因之一是存在未提交或未回滚的事务。
SELECT @@TRANCOUNT
使用上述命令检查任何现有交易；提交或回滚相同。
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 .
如果您不使用 SQL Server 2008 及更高版本，并且指定 LOCK_ESCALATION=Disabled，则可以忘记 ROWLOCK 提示。从我自己的经验来看，SQL Server 可能会忽略它并获取它填充的任何锁（页或表）。 SQL Server 2008 之前没有强制锁定提示。
清除这一点后，您可以使用 SET LOCK_TIMEOUT -1 来指定无限超时。
我强烈建议您不要这样做，而是尝试排除故障并优化（假设您对此负责）真正需要锁定该表的查询，尽可能加快它们的速度以减少锁定时间。
使用
EXEC sp_lock TargetSPID监视锁定的资源，以检查实际采取了哪种锁定另一注：SET LOCK_TIMEOUT设置当前连接的超时时间，如果您使用连接池，则正在设置锁定重用该连接的所有内容的超时可能会导致应用程序中出现意外行为
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 -1to 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 TargetSPIDto check what kind of locks are actually being takenAnother 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