5 Replies Latest reply: Apr 22, 2013 7:56 AM by InoL RSS

    On release of a manually blocked procedure database is commiting itself

    1004397
      I have created a form with two blocks (Database block). Those two block is based on two master table . And there is a mapping table for those two block. user can open those two block from two menu option .

      When user is doing that user is opening the same form in two different session.(And I have to allow that. I cann't even make it one session because if it is in one session user will get the message (FRM-40405 "No changes to save") if he make any change in one form and don't make any change in other form).

      Now the requirement is that user want to make changes in one block and that will effect the mapping table(by update) and if at the same time without saving the first form user make any delete operation on second block my procedure is trying to delete the same row of the mapping table that have been updates by user from other form and creating DEADLOCK in database.

      So I have tried to lock my procedure by dbms_lock package and showing user a message that "You can not do this operation as some other process is going on . Please save the unsaved data." But the problem is when user want to do more than one update in block 1 i have to release the lock and again create the lock. While doing that oracle commit the first change .So when user is leaving the form without saving the changes. I will be able to revert only the last change.

      So what I should do ?
        • 1. Re: On release of a manually blocked procedure database is commiting itself
          GregorM
          Hi.

          By my experience having deadlock in the database implies there is something wrong with your data model as this is not an ordinary situation. One more thing to check before you dig into your data model. The reason for your deadlock could be also the lack of indexes on your mapping table. I assume you did a FK on your master tables pointing to your mapping table. If so check there is a index on that/those columns forming FK on your master tables.

          Best regards.
          • 2. Re: On release of a manually blocked procedure database is commiting itself
            user346369
            >
            I have created a form with two blocks (Database block). Those two block is based on two master table . And there is a mapping table for those two block. user can open those two block from two menu option .

            When user is doing that user is opening the same form in two different session.(And I have to allow that. I cann't even make it one session because if it is in one session user will get the message (FRM-40405 "No changes to save") if he make any change in one form and don't make any change in other form).>
            It is possible to have two base-table blocks in a form without needing to open a separate form in the same session. And there are ways to avoid seeing the FRM-40405 message without opening a separate form.

            I am not familiar with the use of a "mapping table". Please describe what that table is supposed to do.

            It sounds like you have made a very complicated form out of what is likely not that complex. If you describe the situation maybe people here can help you untangle things.
            • 3. Re: On release of a manually blocked procedure database is commiting itself
              1004397
              @gregor13
              Yes I have to create a unique index on a another column. But that is not the FK of the mapping table.


              One of the master table is created before hand that have primary key using index

              "alter table SCHEDULEA_TERRITORY
              add constraint SCHEDULEA_TERRITORY_PK primary key (SCHEDULEA_TERRITORY_ID)
              using index
              tablespace PDS_INDEX
              pctfree 10
              initrans 2
              maxtrans 255
              storage
              (
              initial 64K
              next 1M
              minextents 1
              maxextents unlimited
              );"

              did it effecting the situation?
              • 4. Re: On release of a manually blocked procedure database is commiting itself
                1004397
                @Steve Cosner

                Here "mapping table" means common table between two main table which contain the primary keys of those two table to create a relation between those two table.

                EMPLOYEE ----main table
                DEPARTMENT -----main table
                EMPDEPT -----mapping table.

                my situation is that one user want to enter a new employee in EMPLOYEE table at the same time he is associating some department with that employee. So i have to enter data in EMPDEPT table.
                Now before saving the data by the first user one second user delete one of the department that is associates with that employee So again I have to delete all the data from the EMPDEPT table associated with that department .
                Here I am facing Deadlock.
                • 5. Re: On release of a manually blocked procedure database is commiting itself
                  InoL
                  The usual way to create a form for this situation is a master-detail form only.
                  Master: EMPLOYEE
                  Detail: EMPDEPT

                  You only use DEPARTMENT as a lookup (LOV) in the EMPDEPT block.
                  one second user delete one of the department that is associates with that employee
                  In that situation the 1st user will get a FK error message (Department doesn't exist) when trying to save the changes in the form.
                  The point is: the database will always take care of data consistency. Don't bother coding your own logic.

                  Now, in your real table (the table instead of DEPARTMENT) the data may not be as static as in a DEPARTMENT table. Still, in a n:m relation, there is almost always one table that serves as a lookup.