This discussion is archived
1 2 3 4 5 Previous Next 61 Replies Latest reply: Aug 14, 2009 7:11 AM by 681560 Go to original post RSS
  • 45. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    I appreciate everyone's feedback. I have a revision to this original proposal.

    The Automatic DML Update process will be altered to issue a SELECT FOR UPDATE when fetching the values to compute the checksum. An item or substitution string named exactly APEX_DML_LOCK_WAIT_TIME will be inspected. If the value is null, no change to the SELECT FOR UPDATE. If it is 0, the SELECT FOR UPDATE will be issued with NOWAIT. If the value is positive and an integer, the SELECT FOR UPDATE will be issued with WAIT v('APEX_DML_LOCK_WAIT_TIME').

    So this solves the following cases:

    1) First and foremost, this will rectify the possibility of a lost update.

    2) Default behavior does not change from previous APEX releases. If the row is locked, SELECT FOR UPDATE will wait indefinitely.

    3) If you want NOWAIT behavior, simply specify 0 in a substitution string or item called APEX_DML_LOCK_WAIT_TIME. If you want WAIT behavior, specify a positive integer in a substitution string or item called APEX_DML_LOCK_WAIT_TIME.

    4) This gives someone complete programmatic control whether an UPDATE issued through Automatic DML waits or doesn't wait.

    Comments?

    Joel
  • 46. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Just one:

    Happy New Year!

    ;-)
  • 47. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Joel,

    I thought .. how to incorporate with Automatic DML?
  • 48. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Damir,

    >> ...how to incorporate with Automatic DML?

    I don't understand the question. This enhancement works solely with the UPDATE through Automatic DML and nothing else.

    Joel
  • 49. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Joel,

    I think that all mentioned at the end of this thread is enough and promising. I do not want to continue explaining my standing because this is not under this thread...

    NOWAIT and WAIT xxx i cool option and will probable solve 99,99% of all user needs...

    What I want to point at the end is that in a case of unsuccessful locking no error page is displayed but "some normal" and more classic message ... in any case not an error page...if that is possible.

    THX!
  • 50. Re: Locked record - update hangs - bug?
    135285 Oracle ACE
    Currently Being Moderated
    Damir,

    all errors (Checksum, format mask, insert/update errors) of the "Automatic DML" process are displayed on the error page. So the lock error should behave in the same way.

    What you are requesting is to get rid of the error page for the "Automatic DML" process errors in general. That's a different topic, maybe you should add it to the APEX 3.0 feature request posting. enhancement requests for APEX 3.0

    Patrick
    PS: Joel, I'm fine with the new proposed solution.
    ------------------------------------------------------------------------------------
    Check out my APEX-blog: http://inside-apex.blogspot.com
  • 51. Re: Locked record - update hangs - bug?
    Arie Geller Guru
    Currently Being Moderated
    Hello Joel,

    As I'm probably the only user on this thread who will actually use this enhancement (all the others are experts, who can do very well even without it …) the proposed solution seems to cover all the options, using native APEX technique (substitution string) so it seems like the optimal solution.

    The point that Damir raised in his last post is similar to what I was trying to do in mine ("While revising the code, please consider giving us some more control over the error message page, so we'll be able to produce a user friendly error message, and please bear in mind the NLS support (mainly using translatable strings)." Error messages are a very important part of the application UI. This new approach can yield more error messages to the user, and not from the "call your system administrator" kind. It is important to explain to the user, the nature of the problem his facing, and what's need to be done in order for him to continue the smooth (error free) working.

    It seems that the APEX team is investing a lot of work in a better, more dynamic ways to create UI in the next version (like item's drag & drop for example), and by that, helping us to style and create better UI for our users. It seems only right that the APEX error message system will be enhanced accordingly. (IMHO, but that's just me, it is more important then the other stuff, as we can do pretty good job styling the pages with the current tools. It can always be better, but as the resources of the developer team are limited, priorities seem to be the name of the game).

    Regards,
    Arie.
  • 52. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Patric,

    Your error handling solution is very original contribution to error manipulation and we are using it in our app solution very smoothly-thx!

    But, locked record is not in root of the problem an error!

    It is more TEMPORARY state of data in database.

    Even thought this way of thinking came from forms (where this is ORA-xxxxx) and early versions, here it is change in one important way. In forms it is shown in status bar of the form...not on the next form...or so... I know that there are reasons for that ... no prob...but for this kind of enchantment, I think it should stay as a pure message.

    So my standing is still the same about error page in this case.

    And IMHO "drag and drop" innovation is far from what today ApEx developer need most. I do not want to judge but to express my standing as an example of totally missed enchantment for future release.

    That is my last contribute to this thread...hope it was useful...

    Message was edited by:
    Funky
  • 53. Re: Locked record - update hangs - bug?
    681560 Newbie
    Currently Being Moderated
    Hello everybody,
    I have read this thread but I still don't understand if the OCC implemented in Apex is or not secure.
    In particular, is a "SELECT FOR UPDATE" statement used for the checksum control? Starting from which Apex release?

    I just want to understand if there are any "lost update" risks in an APEX 2.x (and 3.x) environment, where the db is accessed just by the apex application. I don't mind about error messages or waiting time, I just want to be sure not to have lost updates.

    Thanks in advance for your help.

    Ciao.

    Eugenio
  • 54. Re: Locked record - update hangs - bug?
    60437 Employee ACE
    Currently Being Moderated
    The method Joel described here was implemented in 3.0. Earlier versions would have a small risk of lost updates.

    Scott
  • 55. Re: Locked record - update hangs - bug?
    681560 Newbie
    Currently Being Moderated
    Thank you for your reply; so starting from 3.0 I am sure not to get lost updates if I generate automatically forms and process in apex.
  • 56. Re: Locked record - update hangs - bug?
    Arie Geller Guru
    Currently Being Moderated
    Hello,

    >> so starting from 3.0 I am sure not to get lost updates if I generate automatically forms and process in apex.

    Yes, however this is not the default behavior. Please read the following - http://download.oracle.com/docs/cd/E14373_01/appdev.32/e11838/advnc.htm#sthref2090 . In order to completely avoid “lost updated” you should set APEX_DML_LOCK_WAIT_TIME to 0.

    Regards,
    Arie.

    -------------------------------------------------------
    Please remember to mark appropriate posts as correct/helpful. For the long run, it will benefit us all.
  • 57. Re: Locked record - update hangs - bug?
    681560 Newbie
    Currently Being Moderated
    APEX_DML_LOCK_WAIT_TIME should affect just the amount of time spent in getting the lock and not the use of "SELECT FOR UPDATE", shouldn't it? Even specifying NULL I thought lost updates should be avoided, am I wrong?

    From here:
    http://www.oracle.com/technology/products/database/application_express/html/3.0_new_features.html#28
    I can read, for version 3.0:

    "You can now control how Application Express responds for Automatic DML when a record is locked by another user (e.g. an Oracle Forms User). You simply define a new Application Item, APEX_DML_LOCK_WAIT_TIME, with values of NULL (default - wait indefinitely), 0 (no wait), or x (seconds to wait). This modifies the default response."

    so it seems that version 3.0 just add the waiting time parameter...so the "SELECT FOR UPDATE" was used even in the 2.x version? I'm getting confused :(

    Thanks again!

    Eugenio
  • 58. Re: Locked record - update hangs - bug?
    Arie Geller Guru
    Currently Being Moderated
    Hello Eugenio,

    >> APEX_DML_LOCK_WAIT_TIME should affect just the amount of time spent in getting the lock and not the use of "SELECT FOR UPDATE", shouldn't it? Even specifying NULL I thought lost updates should be avoided, am I wrong?

    Unfortunately you are wrong, and that was the reason to the enhancement in 3.0.

    APEX always used SELECT FOR UPDATES in its Automatic DML, however, always with the WAIT option. As Patrick demonstrated at the beginning of this thread, in some rare cases, using WAIT can lead to a lost updates. In order to avoid that, while maintaining functionality compatibility with earlier versions, Joel added the APEX_DML_LOCK_WAIT_TIME item/substitution string. That allows you to control the behavior of the SELECT FOR UPDATE. Doing nothing – not defining the item, or leave it null – will preserve the pre-3.0 behavior, with the rare risk of lost updates. Setting the item to zero (0) will completely prevent lost updates, but with the rare risk of your users getting an error message in cases of potential option of lost updates (the error message doesn’t mean “lost update has occurred”, it only means “other user holding this record, and might change it. You need to try again”).

    Hope things are clearer now.

    Regards,
    Arie.

    -------------------------------------------------------
    Please remember to mark appropriate posts as correct/helpful. For the long run, it will benefit us all.
  • 59. Re: Locked record - update hangs - bug?
    681560 Newbie
    Currently Being Moderated
    Thanks, now it's clearer for me.
    Let me summarize to understand if I got it correctly: the "select for update" statement used to verify the changes, without a nowait option could lead to lost updates because, if the record is locked by another transaction, the "select for update" operation could pass the checksum verification, wait for the record release and then perform the update even if, during the waiting time, the record has changed.

    What I still don't understand: could this situation happen only if different type of application access the same database or even if all the access comes from Apex?

    Eugenio