This content has been marked as final. Show 4 replies
943276 wrote:Basically, yes. The drawback is that the second DML will wait for the first DML to commit. You can avoid this by saying "FOR UPDATE NOWAIT".
1) Is "SELECT ... FOR UPDATE" approach generally good and safe approach when dealing with long running and short running DMLs that can influence one another like, for example, delete a row and cause ORA-08006: specified row no longer exists while merging etc.
2) what other approaches are worth considering (if any)?Using SELECT FOR UPDATE is called "pessimistic locking". In Web applications, "optimistic locking" is your only real choice. Please search on asktom.oracle.com for "optimistic locking" for a good explanation.
3) if "SELECT ... FOR UPDATE" is used on a query with a view, are underlying tables locked too?Yes. I would suggest not doing that, unless you are going to update the view. That is not a widely used technique.
[Edit: the underlying *tables* are not locked +per se+, but the rows in the underlying tables are locked. Thanks to BluShadow for pointing out the potential misunderstanding.]
Edited by: Stew Ashton on Nov 19, 2012 2:25 PM
As with most "What's the best way" type questions, the answer is "it depends". There's no one correct way, because it depends what issue and requirements you are trying to solve.
3) if "SELECT ... FOR UPDATE" is used on a query with a view, are underlying tables locked too?
Oracle does not escalate locks to table level by default, you're probably thinking of some other database engine, but in terms of a view, then if you consider that a view is just a stored query, then yes of course the underlying tables have their records locked.
I forget to mention something: I believe SELECT FOR UPDATE is usually used in "pessimistic locking" scenarios, where a user wants to see some data and then change it.
If you are doing batch updates, or any updates where the user doesn't look at the data before changing it, then you don't need to do the SELECT FOR UPDATE at all. You can just do the UPDATE (or DELETE) directly. Oracle will make sure the read part of the DML is "consistent".
How Oracle does this (with a "restart" under the covers) is not easy to explain. Here is a link to an explanation: