避免 Oracle UPDATE 命令中的锁定
如果我尝试在 Oracle 10g 中获取锁定（例如使用 SELECT...FOR UPDATE），则可以使用 NOWAIT 选项在行被锁定时收到错误消息，而不是挂起查询。有没有办法通过简单的 UPDATE 语句来实现此目的？ Oracle 11g中有一个DDL_LOCK_TIMEOUT选项，我需要类似的 DML 操作（在 10g 中）。
（背景：我有一些查询数据库的单元测试（不幸的是，这不是一个独立的测试数据库，而是用于各种事物的开发数据库），我希望它们立即抛出错误，而不是在出现任何问题时挂起。）
If I am trying to acquire a lock in Oracle 10g (e.g. with SELECT...FOR UPDATE), there is a NOWAIT option to get an error when the row is locked, instead of the query just hanging. Is there a way to achive this for a simple UPDATE statement? There is a DDL_LOCK_TIMEOUT option in Oracle 11g, I would need something similar for DML operations (and in 10g).
(Background: I have some unit tests which query the database (which is unfortunately not an isolated test database, but a developement DB used for various things), and I want them to throw an error instantly instead of hanging when anything goes wrong.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不可以。如果其他会话锁定了它尝试更新的行，则 Oracle 中的简单 UPDATE 语句无法超时。当然，您可以对单元测试进行编码，以在执行 UPDATE 之前执行
SELECT ... FOR UPDATE WAIT <> 。这将确保当您进行更新时，您将保证已经拥有锁。我还对您要对其他会话同时修改的行运行单元测试的想法感到有点困惑。这似乎违背了进行单元测试的目的，因为永远不清楚测试失败是因为代码做错了什么，还是因为其他会话在测试期间以意外的方式修改了数据。
No. There is no way to have a simple UPDATE statement in Oracle time out if some other session has locked the row it is trying to update. You could, of course, code your unit tests to do a
SELECT ... FOR UPDATE WAIT <<n>>before doing the UPDATE. That would ensure that by the time you got to the UPDATE, you would be guaranteed to already have the lock.I'm also a bit confused by the idea that you'd be running unit tests against rows that other sessions are modifying at the same time you are. That would seem to defeat the purpose of having unit tests since it would never be clear whether a test failed because the code did something wrong or because some other session modified the data in an unexpected way during the test.