Avoid locks in Oracle UPDATE command

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).

开发者_Go百科

(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.)

---

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.