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
  • 30. Re: Locked record - update hangs - bug?
    Arie Geller Guru
    Currently Being Moderated
    Hi Damir,

    "lost update" could and will happened in client-server environment under some circumstances...so the main thing is to show that...as nice as possible to user...but there is no way to prevent that

    Totally unacceptable and I'm pretty sure this is not correct. One of the basic functionalities of every concurrency control algorithm is to prevent "lost updates" and a lot of great minds spent a lot of time and energy (not to mention money) to insure that. Even at your application, you are using self-developed code to ensure just that.

    The UI is important, and you should use friendly techniques to help your user overcome concurrency problems, but the system should be able to detect them first.

    The main issue is how to implement a good and optimized mechanism to do just that. From the APEX documentation, one can understand that if you are using the built-in automatic DML, you shouldn't worry about that. I'm quoting from the user's guide – "This approach offers three advantages. First, you are not required to provide any SQL coding. Second, Oracle Application Express performs DML processing for you. Third, this process automatically performs lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently." (The emphasis is mine). It seems that in some situations this is not the case. Joel claims these situations are rare, and to be honest, I'm not so sure he's right. I believe the reason that he didn't hear of it often is because most of the power users, eligible to encounter these situations, are taking your road, and developing their own code, rather then using the APEX built-in process

    I appreciate your code examples, and I'll definitely study them, but as I wrote on my first post, the all point, and basically the point of having automatic DML processes as part of APEX, is to allow "regular" users to avoid writing code.

    Thanks,
    Arie.
  • 31. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Arie,

    my error is using wrong term..."lost updates" is in my case totally different used...I thought that user who cannot perform update has "lost his update".

    So you're right "lost updates" in a case of transaction is ensured...as I said "all or nothing".

    Once again, in my case, I just wanted to point that user updating may be lost in a way that this update is not possible ... so "lost update" is here in this term...not in term as you mentioned.

    But your using is more regular then mine so my apologize for wrong using of that term...;-)

    "Third, this process automatically performs lost update detection."
    Frankly I do not what is so hard to ensure....In all our C-S application we have
    BEGIN
      START TRASACTION
      ....code
      COMMIT;
    EXCEPTION
      WHEN ...
          ROLLBACK; --better safe then sorry!
          or
          ROLLBACK TO SAVEPOINT  ...;
    END;
    We have application that is in use 10 years Oracle 9R2 and have 250 concurrent users with 400 sessions in all the working hours...with heavy load in some periods.

    We have never...never get any problem with any of lost update...and find that normal.

    What here is the problem (as I can see) is that ApEx DML process try to perform DML without trying to ensure that WHOLE process could end well. So error page is shown in a case of any error. There could not be any "missing updates" AFAIK....in any way.

    Problem with NOWAIT may easy ensure that all needed rows are locked to ensure that DML can successfully finished. Frankly we do not use DML amd MRU/MRD/MRI in any of our forms (we use dedicated functions that do the task...to ensure security because we do not allow INSERT/UPDATE/DELETE grant to WEB schema user, but to grant execute on functions that do that!). So we haven't phase any of these problems and not aware of it's existence...

    Please correct me if I'm wrong...

    THX!
  • 32. Re: Locked record - update hangs - bug?
    Arie Geller Guru
    Currently Being Moderated
    Hi Damir,

    I'm glad we are finally speaks and understand the terms we are using, in the same way. Makes things easier … :-)
    " "Third, this process automatically performs lost update detection."
    Frankly I do not what is so hard to ensure....In all our C-S application we have … "

    You are using a completely different concurrency control algorithm then the APEX built-in one. In your case, using and controlling db transactions, "lost updates" is not an issue. APEX is using a different method – optimistic locking – in which timing plays a critical role. As you can see from Patrick's scenario (and my example), in very specific situation, lost update is not detected by the system, because the way it was implemented. As far as I understand, Joel came up with a suggestion that can fix this problem, and still maintains the advantages that lead them to pick this algorithm in the first place.
    " We have application that is in use 10 years Oracle 9R2 and have 250 concurrent users with 400 sessions in all the working hours...with heavy load in some periods.
    We have never...never get any problem with any of lost update...and find that normal."

    Just for the record – concurrency control is not always about number of concurrent users or the db workload. It is mostly about the nature of your activities. If I remember correctly, you are working for a financial institute. You can have 100 tellers serving your customers, at the same time, but each of them is working with a different set of records – one record set for each customer. In this case, "lost updates" is really a miner issue. On the other hand, each banking transaction can span more then one db table, and you have to make sure that the data in all of them will be consistent. This issue, for example, is not includes at all in APEX automatic DML, as this process is working against one table only.

    I hope this issue will be resolved, so will be able to safely continue using APEX built-in DML processes, in all the cases that fits the functionality of these services.

    Regards,
    Arie.
  • 33. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Just to close out this thread and solicit remaining customer feedback - any other strong objections to SELECT FOR UPDATE NOWAIT at the time of checksum computation?

    Joel

    P.S. Mathias' objection has been noted.
  • 34. Re: Locked record - update hangs - bug?
    107829 Explorer
    Currently Being Moderated
    My objection aside, could you share your view on the idea of capturing the value of every column and then using that in the update statement similar to how you would use ora_rowscn? It sounds good to me, but maybe it's not good due to architectural concerns. I'd just be interested if you saw a general probelm with it in case I'd need to do something like this in my own non APEX application in the future.
  • 35. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Mathias,

    My personal view is - it's no more or less computationally efficient than the current implementation.

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

    If I understand good this will be a part of Multi DML choice(option) in page wizzard?

    However I'd like to have and "WAIT xxx" option as well...if this is easy to implement as NOWAIT part.

    THX for accepting this feature
  • 37. Re: Locked record - update hangs - bug?
    Arie Geller Guru
    Currently Being Moderated
    Hello Joel,

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

    Thanks,
    Arie.
  • 38. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Damir,

    Your understanding is incorrect. A choice will not be provided. The Automatic DML will be augmented to issue a SELECT FOR UPDATE NOWAIT when issuing an UPDATE process in Automatic DML. The purpose of this is to:

    1) Although rare, ensure no lost updates when using Automatic DML
    2) Avoid the situation that Patrick describes in mixed environments, where these UPDATE processes will block for extended periods of time (and the user is predisposed to clicking over and over again, only consuming more processes from the finite pool).

    Depending upon customer feedback (and I'm really only hearing from 2 or 3 people right now), we can consider making the WAIT option variable post Application Express 3.0.

    Joel
  • 39. Re: Locked record - update hangs - bug?
    107829 Explorer
    Currently Being Moderated
    I would be a little worried that some applications would not work with this as the mandatory mode. If they based their architecture on this wait model, then the new way would maybe not let their application work at all. Sure, the design would be surprising and probably considered illadvised. However, an application that works today should probably not break in a way that causes the whole architecture (for that application) to be broken.

    I don't see myself using this a lot, so I'm not really the intended audience. I just participate because I think this is important and interesting.

    Do you think a select for update + update is equal in processing and scalability impact to an update that specifies all the old values in the where clause (for overwrite protection)? I would have guessed that the update only would be almost twice as fast.
  • 40. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Mathias,
    If they based their architecture on this wait model
    I think this is supposition, I'm looking for empirical evidence and I have received little from other customers.

    Go back to Patrick's very first posting in this thread. What does a typical user of a Web application do when they have to wait for more than a few seconds? They click Refresh. I know I do. And then they wait some more. And then they click Refresh again. Unwittingly adding to the queue of processes and database sessions which are beholden to the one lock.

    I do agree that it is dangerous to change the behavior of the APEX engine and running the risk of changing applications that work today. I will consider that. So maybe I'm missing it altogether, but I don't see customers building Web applications and basing their architecture on this wait model.

    Joel

    P.S. I stand by my statement of "my personal view is - it's no more or less computationally efficient than the current implementation.", and unfortunately, have no time for a theoretical discussion to prove or disprove this.
  • 41. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Joel,

    here is one real situation....

    90% of working hours we'd like to have "SELECT FOR UPDATE NOWAIT"...because overhead in these moments is not too big.

    Probably we all knows the peaks in our production but for others ... let say that some bigger batch processing/or bigger number of concurent users takes place in certain time of working hours, so overhead is significantly bigger. In such a periods we are setting one variable and according conditional processing we'd like to have "WAIT xxx" situation, because we know that batch can "lock" for most xxx sec some resources.

    Later when peak is gone we change that variable and would use "NOWAIT" option as normal part.

    My point is to mention that both versions are very interested and will have usage in conditions that I have mentioned (what almost any production has)....

    In our case it will be not acceptable that in one period we use Apex DML and in others our solution (on the DIFFERENT pages, because AFAIK only one DML can exist on one page, regardles conditional term!)...huuuuuuu!

    Hope now is all more clear...

    Message was edited by:
    Funky
  • 42. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Damir,

    Interesting feedback. Thank you for this.

    1) So today, you have no choice. Today with APEX 2.2, you essentially have UPDATE with WAIT. Does this suffice for the scenario you give above?

    2) A declarative attribute of WAIT xxx on the process would not suffice for this requirement. You're seeking programmatic control of this option. Would it need to be on a process by process basis, or would it be sufficient for most use cases to simply have the WAIT xxx apply to all UPDATE DML for a given application?

    Thanks.

    Joel

    P.S. By the way, you essentially have all of this today at your disposal. You could use Automatic DML in combination with an earlier call to DBMS_LOCK to acquire some named lock.
  • 43. Re: Locked record - update hangs - bug?
    445907 Newbie
    Currently Being Moderated
    Joel,

    THX for your attention. I'm not sure that I do understand 100% your questions but will try to answer.

    1) No ... that is why we use our custom function based solution. At least in some rare parts ... where security (we do not grant select/Update/Insert on table but perform our function with grant execute to WEB schema) is not so important. In such a ceses we coulduse ApEx DML when we can differ WAIT and NOWAIT option as we like

    2) "WAIT xxx ... You're seeking programmatic control of this option"
    Yes

    Would it need to be on a process by ...
    Process based...because in some other forms or parts of application the same logic maybe is not sufficient or needed at all!

    By the way, you essentially have all of this today at your disposal. You could use Automatic DML in combination with an earlier call to DBMS_LOCK to acquire some named lock

    We have no experience for such a support in ApEx. Any demo...more to read?

    Hope I was clear enough...
  • 44. Re: Locked record - update hangs - bug?
    jkallman Employee ACE
    Currently Being Moderated
    Damir,

    >> We have no experience for such a support in ApEx. Any demo...more to read?

    Yes. Google "dbms_lock".

    Joel