4 Replies Latest reply: Nov 19, 2012 11:12 AM by Stew Ashton RSS

    select for update and short/long running DMLs

    946279
      Here is what documentation says: "SELECT... FOR UPDATE statements are often used by interactive programs that allow a user to modify fields of one or more specific rows (which might take some time); row locks are acquired so that only a single interactive program user is updating the rows at any given time."

      My questions:
      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)?
      3) if "SELECT ... FOR UPDATE" is used on a query with a view, are underlying tables locked too?

      thanks
        • 1. Re: select for update and short/long running DMLs
          Stew Ashton
          943276 wrote:
          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.
          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".
          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
          • 2. Re: select for update and short/long running DMLs
            BluShadow
            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.
            • 3. Re: select for update and short/long running DMLs
              946279
              BluShadow, I'm not asking for "the best way"...
              • 4. Re: select for update and short/long running DMLs
                Stew Ashton
                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:
                http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:11504247549852