9 Replies Latest reply on Jan 20, 2011 7:58 PM by Andreas Weiden

    Error - could not reserve record

    Wency
      This is in oracle forms 10G/win xp
      I am getting this message: Could not reserve record (2 tries). Keep trying?

      The code that causing this error:
      select count(*) into :blk.recordcount from mytable where xid= :blk2.xid;
      The field is updateable. When I run this query in toad I get the desired number. I am the only who access the table.

      Any idea about this error?
        • 1. Re: Error - could not reserve record
          kingadmin
          Hi,

          This is happen when you are try to update by other user to same table. check your transactions again.

          it's not coming from select .

          Regrads,
          • 2. Re: Error - could not reserve record
            Moazzam
            Perhaps table or record is locked. So to fix it, you should unlock it.
            • 3. Re: Error - could not reserve record
              Amatu Allah Neveen Ebrahim
              Hi Wency

              Yes, this could be if u r running 2 instances of this form in the same pc;this happens with me after Executing the Query.

              it's not a condition to update or create only...


              When u got it again u can either do the following:

              1.Click No in response to the message. Do not keep trying to reopen a locked record.
              Pls Note*: your form locks every record it fetches

              2.Wait 1 minute and try to reaccess the record could possibly someone is sharing u same table.

              3.There is 1 way i don't recommand really to get remove from this message is :

              go to Data block property palette > Database Locking Mode > Default Automatic Set it to Delayed. then you can't received this error...
              cause it has a draw back but i leave the choice according to ur form bussiness requirements to decide.
              4.To show if there are any blocking database locks and if so - which user is locking the Database
              use the following queries to get the information you want.
              SELECT * FROM v$LOCK ;
              SELECT * FROM V$LOCKED_OBJECT ;
              v$session and dba_waiters are two other useful tables u or any one may need...

              Hope this helps...

              Regards,

              Abdetu...

              Edited by: Abdetu on Jan 18, 2011 2:06 AM
              1 person found this helpful
              • 4. Re: Error - could not reserve record
                Wency
                Thanks for answering guys!

                From V$LOCKED_OBJECT , I can see my name on 3 entries, locked_mode is 2,3,2 (row shared and row exclusive). I'm not familiar with these.

                Also, from v$session, I found 3 entries with may name.
                Program = toad.exe, seconds_in_wait = 503589 the most, state = WAITING.


                Give these informations, what should be the next thing to do to avoid the error?
                • 5. Re: Error - could not reserve record
                  832298
                  I think you have opened multiple instances of same form. If it is so close all forms and then again run it.
                  • 6. Re: Error - could not reserve record
                    Amatu Allah Neveen Ebrahim
                    Hi Wency

                    Let's Trace

                    Here is a query that gives us a list of blocking sessions and the sessions that they are blocking:
                    SELECT Program ,  blocking_session, sid, SERIAL#, state ,  seconds_in_wait
                    FROM        v$session
                    WHERE       blocking_session is not NULL
                    ORDER BY    blocking_session;
                    In this case, we find out ...

                    1. WHAT program is blocking what session ?
                    2.is blocking what db sid ?
                    3.for how many seconds...?
                    4.SERIAL#

                    For example:_ if you know the SQL_ID and serial number for an Oracle session, you can join into the v$sql view and actually get ...
                    the source code for the SQL statement that is currently executing.


                    We would then want to find out who is running session ..., and go find them and see why they are having a problem..?

                    u have then many alternatives easiest one is to ....
                    SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
                    Oracle 11g introduced the ALTER SYSTEM DISCONNECT SESSION syntax as a new method for killing Oracle sessions.
                    Unlike the KILL SESSION command which asks the session to kill itself.

                    Hope this helps...

                    Regards,

                    Abdetu...
                    • 7. Re: Error - could not reserve record
                      Andreas Weiden
                      Hi Abdetu,
                      it's not a condition to update or create only...
                      In fact, automatic locks only occur when you update or delete a row. A query will NEVER lock a row(except you specify an FOR UPDATE). If you have a lock after some execute_query in forms, There is some wrong code in your POST-QUERY-trigger.

                      Edited by: Andreas Weiden on 19.01.2011 19:43
                      • 8. Re: Error - could not reserve record
                        Amatu Allah Neveen Ebrahim
                        Hi ,thanks for the info Andreas

                        But, i believe u r correct Regarding ,These DML operations INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE.

                        These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.


                        But some other cases as if not having this...

                        e.g. running form once on Firefox i do make execute_Query...,then made some modification, then re-run the form i got the same message so ...

                        i have to close the old one if it is executed :( ...

                        but this is not happening in case of usingthe IE

                        Thanks again Andreas

                        My Regards,

                        Abdetu...
                        • 9. Re: Error - could not reserve record
                          Andreas Weiden
                          e.g. running form once on Firefox i do make execute_Query...,then made some modification, then re-run the form i got the same message so ...
                          i have to close the old one if it is executed :( ...
                          What exactly does that mean?