This discussion is archived
1 2 3 5 Previous Next 61 Replies Latest reply: Aug 14, 2009 7:11 AM by 681560 RSS

Locked record - update hangs - bug?

135285 Oracle ACE
Currently Being Moderated
Hi,

on another thread there was a question about what happens when a record is locked and I just did a quick test.

When I issue in a separate session a SELECT * FROM xxx WHERE abc=1 FOR UPDATE; to lock a record and then try to submit a change for this table in a form/tabular form which updates this record, APEX will just hang. I think until it get an timeout from Apache (haven't waited that long).

In a web environment wouldn't it be better that the record is first locked (the record is selected anyway to check if it has changed) and then try to update/delete it.

I know that APEX just has short running transactions, so it can't produce this types of locks, but there are also other tools around (eg Oracle*Forms) which allow to have long running transactions.

BTW, if the user stops submitting the page and tries again and tries again. This will consume each time another db connection from the mod_pls session pool and block it for usage for other users.

Thanks for considering it
Patrick
------------------------------------------------------------------------------------
Check out my APEX-blog: http://inside-apex.blogspot.com
  • 1. Re: Locked record - update hangs - bug?
    475167 Newbie
    Currently Being Moderated
    It's not a bug. It works as it is supposed to be. Use FOR UPDATE NOWAIT instead. Your Query will return with an error immediately.

    Dim
  • 2. Re: Locked record - update hangs - bug?
    135285 Oracle ACE
    Currently Being Moderated
    Hi Dim,

    seems that there was a misunderstanding. The point is that the default APEX DML process doesn't issue a FOR UPDATE NOWAIT when it reads the data to do the checksum check.

    The SELECT you see above was just for demonstration purpose, to show what happens when you execute that in a separate DB session (eg. with SQL*Plus) and then fire the APEX DML process.

    Patrick
    ------------------------------------------------------------------------------------
    Check out my APEX-blog: http://inside-apex.blogspot.com
  • 3. Re: Locked record - update hangs - bug?
    475167 Newbie
    Currently Being Moderated
    Ah ok. Well this is called optimistic locking. If apex would use for update the whole recored could be locked, well for a very long time.
    Just imagine: User X wants to change some data, but suddenly notice that its time for lunch. The record keeps locked until he comes back.
    But I never had your phenomenon, but perhaps this is because I don't use the generated DML functions in ApEx. I always write a little PL/SQL process where I have full control of how things work (even if it is a little more to code).

    I only have problems when I use Database links within ApEx. It gets incredible slow, and I can see some strage waits in v$session_event.

    Dim
  • 4. Re: Locked record - update hangs - bug?
    135285 Oracle ACE
    Currently Being Moderated
    Hi Dim,

    there seems to be another misunderstanding.

    I'm not talking about that APEX should lock the record when it fetches the data. Wouldn't work by the way, because the chances that mod_plsql uses the same database connection from the pool isn't very high when the user triggers the next action.

    I'm talking about the transaction APEX opens when you save changes on a form. After validation APEX will fire the "Automatic Row Processing (DML)" process or the "Apply MRU/MRD" process for tabular forms. And in this processes APEX does the checksum check because of the optimistic locking. When it does this check it reads the old data from the database, but not with a FOR UPDATE NOWAIT , that's causing the problem when it does a UPDATE afterwards and another session holds a long running lock on this records the session will just wait forever (because the UPDATE statement doesn't have a timeout).

    Patrick
  • 5. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Dr,
    If I may make express Patric in a different way.

    "Update Row of xxx" process DML should work like:
      busy_resource EXCEPTION;
      PRAGMA EXCEPTION_INIT(busy_resource, -00054);
    begin
      -- try to lock that row
      select * from table where id=xxx for update nowait (wait xxx);
      --do that ML row processing!
      update....
    exception
      when busy_resource then
        -- no exception is made...just some message...
        null;
    end;
    There will be one problem with multi row update...but many of us are avoiding MRU,MRD because of problems that on heavy multi user environment this operation may fail more then is acceptable...of course with not accepting error on error page.
  • 6. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Now when I get back to PC, came to my mind that these 2 options (create nowait SQL and create WAIT xxx option) could be implemented in next release as new feature...
  • 7. Re: Locked record - update hangs - bug?
    475167 Newbie
    Currently Being Moderated
    Hi Patrick,
    now I see your point. But what's the solution?
    If APEX would use for UPDATE NOWAIT, it will return and say "not now dear user" The user tries again, again...

    There should be no long running transactions on a OLTP system. Its simply a design problem.
    But that's only my personal oppinion.

    Dim
  • 8. Re: Locked record - update hangs - bug?
    135285 Oracle ACE
    Currently Being Moderated
    Hi Dim,

    sure the user will try it again and again, but the difference is that because of the error the page rendering gets finished and the DB connection goes back into the pool of available DB connections for mod_plsql. If no error is raised, each request by the user will consume another/new DB connection. I think another issue is that independent if the user has already canceled the submit and closed the application the update will processed in the background as soon as the lock is released... I'm sure the user thinks that he didn't do the update, because he canceled the submit...

    About the design problem of long running transactions. For Oracle*Forms it's/was the default setting and it never was a real problem, because you always where able to view which user is holding the lock and there is normally a reason why the other is holding the lock...

    From an user perspective I think immediate locking is better, because when you start changing the record it will acquire the lock and check if the record has changed since it has been displayed. And you can be sure that when you press save you are able to save the record, because you have the lock.

    With a delayed locking as you have to have it for web environments, the user views the data, does all the changes (maybe a lot) and then when he presses save he is informed that he has to discard his changes he typed in the last 15 min and do it again... I don't know if that makes the user that happy...

    But as always in "real" world it depends on the table, there are also situations where I wouldn't suggest using pessimistic locking.

    Patrick
  • 9. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Just my 2 c,

    We find some unresolved issues when using "for update wait xxx", which was based on 92 tables link (update from Apex enviroment, based on 10gr2.2, on 9.2 db table) in autonomous transactions call which resides in 10g...

    Issue was that sometimes "wait xxx" last forever, so Apex never get returned ANY result value to user.

    Removing "wait xxx" to "NOWAIT" now shows that this issue never shown again...
  • 10. Re: Locked record - update hangs - bug?
    135285 Oracle ACE
    Currently Being Moderated
    Bump.

    Scott, will that be considered in the next release? Haven't read a statement from you about this topic.

    Thanks
    Patrick
  • 11. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Patrick,

    Firstly, thanks for your response, your feedback, and your suggestion. It's always worthwhile.

    So to answer your very first question - is this a bug? The answer is no. The automatic DML of APEX was intentionally implemented with no inclusion of WAIT or NOWAIT. Thus, the default behavior will apply, namely "If you specify neither WAIT nor NOWAIT, then the database waits until the row is available and then returns the results of the SELECT statement." So this is the behavior you are witnessing and it is expected.

    The example you cite is one where the inclusion of NOWAIT in the DML statement would be useful. But my question is - how common is that, especially in an OLTP system? Sure - there are instances where someone will be updating the exact same row at the exact same millisecond that you are, but those are very rare indeed. And how common is the case that someone would implement a "hard" lock on a row? I'm not saying that the situation does not exist, but in my 8 years of working with APEX / HTML DB, this is the first time this has come up.

    The problem you cite is real - the page will appear to wait and the pooled database connection will be consumed until it times out or the update can be performed. But I'll basically echo Dr.Dimitri's comments - I believe it's a design problem. If we are serializing access to data for extended periods of time, then something is most likely wrong with the design.

    So sure - an option could be added to the Automatic DML processing to specify NOWAIT or WAIT xxx, but then a UI could be added for every option we ever wanted, and the result is the "Christmas tree" effect in APEX itself - too many knobs to turn and understand. I believe the current implementation suffices for 99.5% of all of our typical customers. Please correct me if I'm wrong - I'm certainly open to input from our customers.

    One idea is for you to add your own process before the Automatic DML process, attempt to select the row for update, and if it fails, raise an error so the Automatic DML process never executes.

    To your last points, I believe pessimistic locking is essentially incongruent with stateless Web enviornments, regardless of the associated issues with database connection pooling.

    I hope this helps and I look forward to anyone's feedback on this topic.

    Joel
  • 12. Re: Locked record - update hangs - bug?
    135285 Oracle ACE
    Currently Being Moderated
    Hi Joel,

    thanks for your answer!

    I agree that this situation with the lock will probably never occur if you have an APEX only database, but in an environment where you have different tools to access the data, eg APEX for self-service and Oracle Forms for the power users, this locking problem can occur. As I have written in my previous post, deterministic locking is the default in Oracle Forms. If that's a good default is another question, but that's how it is. What I'm more concerned about this issue, is the effect it has on the connection pool.

    If I understood the "Automatic DML process", it does a SELECT before the update to verify if the checksum still matches with the value stored. From a consistency standpoint, doesn't have this check to lock the record anyway, to be able to return a correct answer. I know this is more theoretically to have two transactions in the same second, but anyway the second transaction will not get an error that the data has changed, it will just update it.

    My question is, does it harm the system or the existing applications when the "Automatic DML process" does a SELECT FOR UPDATE NOWAIT as default? I think it's not necessary to create a switch here.

    I know that I can write an additional process, but that's why I have the "Automatic DML process" so that I don't have to do anything - I'm lazy :-)

    Regards
    Patrick
    ------------------------------------------------------------------------------------
    Check out my APEX-blog: http://inside-apex.blogspot.com
  • 13. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Joel,

    could there be implementation based on page (with WAIT/WAIT XXX and "regular" DML pages) ... so we do not mix that on process level (different processes on the the same page) but on page level (all processes are the same type!)...what is much more easy to follow and to control.

    Just my 2 c!
  • 14. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Hi Patrick,

    >> My question is, does it harm the system or the existing applications when the "Automatic DML process" does a SELECT FOR UPDATE NOWAIT as default?

    Let's contrast the two cases.

    Scenario 1: User A clicks the Save button. User B clicks the Save button a microsecond later. They're both updating the same row.

    Current Functionality: User A's transaction will go through. User B will wait until User A's transaction completes. The data has changed. User B will receive an error stating that the data has changed.

    NOWAIT Functionality: User A's transaction will go through. User B will fail, with an error stating that a lock cannot be acquired (or something to that effect).

    Scenario 2: User A clicks the Save button, but actually changes no data. User B clicks the Save button a microsecond later. They're both updating the same row.

    Current Functionality: User A's transaction will go through. User B will wait until User A's transaction completes. The data has not changed. User B's transaction will go through.

    NOWAIT Functionality: User A's transaction will go through. User B will fail, with an error stating that a lock cannot be acquired (or something to that effect).


    I'm not sure if a default of NOWAIT is desirable. I would be interested in other's thoughts.

    Joel
1 2 3 5 Previous Next