1 2 Previous Next 22 Replies Latest reply on Jul 17, 2006 5:38 PM by 248691

    Could not reserve record (2). Keep trying ?....

    324137
      This has to be a classic Oracle forms problem.

      I would like to replace that useless message by a more user friendly message when a user tries to update a record which is already locked by another user. I found the following solution in here using the on-lock trigger:

      Declare
      is_locked boolean := false;
      dummy VARCHAR2(2);
      Begin
      Begin
      Select null INTO dummy from mytable
      where rowid = :myblock.rowid
      for update nowait;
      exception when Others then
      If sqlcode = -00054 Then
      is_locked := True;
      End If;
      end;
      IF is_locked then
      Showmessage('Another user is updating this record. Try again later.');
      raise form_trigger_failure;
      End if;
      End;

      However, when I add that code, it seems to change the behavior of the locking process. For instance, If the record is locked by user A, user B gets the expected "Another user is updating..." as long as the record lock is active.

      When the user A commit the changes, user B is then able to change the data when he should have get the "Record has been updated by another user. Please re-query" message. Therefore, user A loses it's changes when user B does a commit.

      Any ideas why or any suggestions ?

      Thanks.
        • 1. Re: Could not reserve record (2). Keep trying ?....
          maklewis42
          I dont think you can make any change to a record that is locked by another user/process. So there are no changes to lose at all because the message comes up as soon as you press a key indicating you are about to update before you update.

          Oracle forces the user to re-query so that you only change what you know rather than what you dont know. You should know the current values in the database before changing them which i think makes sense. So I think you shoulnt think of this as an error but very necessary oracle behaviour
          • 2. Re: Could not reserve record (2). Keep trying ?....
            324137
            Hello Mark,

            When I am not using the on-lock trigger, Oracle forms behave properly but I get this not user friendly "Could not reserve record..." message. Meaning that if you try to update a record which is locked, you get the "Could not reserve record..." message. After the record gets commited by the other user, you get the "Record has been changed... Please re-query". That way, no user can lose it's changes.

            However, using the on-lock trigger solution to trap the error message, Oracle forms will let me change a record which has been commited by another user, without telling me to first re-query. Therefore, that other user will lose it's changes because I will commit over them. I should have got instead the "Record has been changed...please re-query" message.

            Any ideas of what causes that or how to avoid that situation using the on-lock trigger ?

            Hope it is clear.

            Thank you.
            • 3. Re: Could not reserve record (2). Keep trying ?....
              maklewis42
              Sorry , but let me understand something. what do you want to achieve. is it that you want 2 people to update the same record at the same time and still manage to commit their changes?

              If this is the case , i dont think it is possible because Oracle transaction model is 'ISOLATED' meaning each transaction happens before or after another but not at the same time if you are talking about the same resource. This implies that a record can only be viewed in its state before or after a transaction, so 1 of the 2 users will see the record before the transaction and is able to update it and the other will see the record after the transaction and wiull be able to update it. But the only way to see the resource after the update is only by re-quering hence the message to re-query. so I dont think u can go past this one.

              Let me know if you crack it. I might be wrong
              • 4. Re: Could not reserve record (2). Keep trying ?....
                324137
                What I want to achieve is to trap the message "Could not reserve record (2)...Keep trying'. I want to replace that message with a more meaningfull message.

                So the solution I found here was to write code within the on-lock trigger (see above).

                This works since I can write my own message instead of the "Could not reserve..." message. However, a side effect of it is that as soon as user A commit his changes, user B is able to change that same record, without re-querying, which cause user A to loose his changes. Without the on-lock trigger, user B gets the standard "Record has been changed...you must re-query" which is the correct behavior but I'm stuck with that "Could not reserve..." message.

                I am wondering why with the on-lock trigger Oracle let a user modify a changed record (by another user) without giving the message to re-query. Maybe that someone ran into the same problem using that on-lock trigger solution and they found a work around.

                Hope I made it clearer.
                • 5. Re: Could not reserve record (2). Keep trying ?....
                  user346369
                  Denis,

                  You are only half-way there. Forms does TWO things in the on-lock trigger, it does the "select for update nowait" as you have coded, but it ALSO compares the values fetched into the record during the query with the current values in the database, by adding this to the where clause:

                  where rowid=:blk.rowid and :blk.Col1=Col1 and :blk.Col2=Col2, and...
                  (Not sure how they do it, but they have to handle null columns in the comparison, too.

                  Now, due to the where clause, if any column was changed and committed by the other user, the select will return a no_data_found.

                  If the ORA-00054 is detected, you get the "record is locked by another user... retry" message. But if select returns a no_data_found, you get the "Record has been updated by another user... Please re-query" message. (And it should be noted that at this point the record is now locked by the user getting the message, AND it cannot be updated by the user. Our forms here do a clear/rollback before the query so that the re-query process will unlock the row, but that is not the default in Forms.)

                  The two messages come up so rarely in the real-world situations here, that I do not try to override the default processing. However, in special cases, I have written such code, but it is usually in other places, like during special commit processing where other tables and data need to be updated in addition to the base-table data. In those situations, it is quite a task to get everything working correctly.

                  If you look at the on-line help on the "Lock the Row" topic, in the "continued" image, there is the process, "Has the row been changed or deleted by another operator" check. That is the clue to what you need to do in the second step.

                  Message was edited by:
                  Steve Cosner

                  One additional note: I ran a trace to see the SQL statement that Forms actually issues, and it is not what I gave above. Instead, it is just a select of all the columns in the block, with just the rowid in the where clause, and with the for update clause. Forms apparently fetches all the column data, then compares the newly fetched values with the database_value of each column in the record.
                  • 6. Re: Could not reserve record (2). Keep trying ?....
                    394073
                    Hi Denis,

                    It has been a while since I developed Forms but here is my 2 cents worth....

                    The ON-triggers are instead of normal operation triggers, therefore, if you use ON-LOCK, you are taking over the control and you have to issue your own LOCK_RECORD in the code.

                    But in this instance, you should be using a ON-MESSAGE (or ON-ERROR trigger). You only want to replace the standard message with your own message.

                    Regards,

                    John
                    • 7. Re: Could not reserve record (2). Keep trying ?....
                      433234
                      Hello Dennis,
                      i also found this solution on metalink ( 1004126.6 ) and it worked for me.
                      I 've put all the items from that datablock on a separate canvas and show 'my' message before navigation.

                      Bernd
                      • 8. Re: Could not reserve record (2). Keep trying ?....
                        324137
                        Thanks everyone for your tips, it gave me some clue on how to fix the problem.

                        Here is the re-written on-lock procedure that will work.

                        Declare
                        is_locked boolean := false;
                        dummy VARCHAR2(2);
                        Begin
                        Begin
                        Select null INTO dummy from mytable
                        where rowid = :myblock.rowid
                        for update nowait;
                        exception when Others then
                        If sqlcode = -00054 Then
                        is_locked := True;
                        End If;
                        end;
                        IF is_locked then
                        Showmessage('Another user is updating this record. Try again later.');
                        raise form_trigger_failure;
                        Else
                        LOCK_RECORD;
                        If Not Form_success Then
                        RAISE form_trigger_failure;
                        End If;

                        End if;
                        End;

                        Message was edited by:
                        Denis Demers
                        • 9. Re: Could not reserve record (2). Keep trying ?....
                          452548
                          Hi ! is there any way to show the oracle USERNAME that is locking the table, in the message...something like "The user USERNAME is locking the table ,please try again later" ...)..
                          Thanks in Advance!!
                          Robert
                          • 10. Re: Could not reserve record (2). Keep trying ?....
                            user346369
                            Not possible. Oracle does not record a usernames along with record locks.
                            • 11. Re: Could not reserve record (2). Keep trying ?....
                              447756
                              If you are desperate, you could implement your own mechanism of doing this but it will need a hell of a code. You know every time someone locks a row he saves the info on the server in order for the others to be able to see it. Hardcore
                              • 12. Re: Could not reserve record (2). Keep trying ?....
                                452548
                                10,000 sessions (around 8,000 users), 5 TS Database... yes, I'm desperate ...any clues ?
                                Thanks in advance
                                • 13. Re: Could not reserve record (2). Keep trying ?....
                                  447756
                                  I haven't ever done it of course, but I thought that if you need it you could have a table for example that holds locking information about users.. So whenever a user locks a record, hge places an entry in there and accordingly deletes it when he releases it. This would normally require some code on the on-lock trigger of your forms but I'm sure its not as easy as it might look..

                                  That's just a though I made but unfortunatelly I can't back it up technically for you. However I'm open for discussion on this to the extend my knowledge allows me.


                                  regards,
                                  teo
                                  • 14. Re: Could not reserve record (2). Keep trying ?....
                                    user346369
                                    10,000 sessions (around 8,000 users), 5 TS Database... yes, I'm desperate ...any clues
                                    And how many forms do you have running? I am afraid you would need to add code to every form.

                                    Why don't you step back and look at the problem from a different angle... On what table is the problem locking occurring? What forms can lock that table? If it is not too many, maybe someone should look at those forms and try to determine why locking is a problem.

                                    In a well-written OLTP/Forms environment, Forms lock records and release the locks automatically, and there is hardly ever a problem.

                                    However, if forms are written incorrectly, two users just trying to display the same data can lock each other up. Fixing the bugs in the problem forms would be a far more efficient method of correcting your situation.
                                    1 2 Previous Next