Commit_Form issue in Oracle Forms [message #539121] |
Wed, 11 January 2012 07:54 |
bahubcd
Messages: 40 Registered: July 2007 Location: Bangalore
|
Member |
|
|
Hi all,
Hope you all are doing well.
Let me brief you about the issue.
There are 2 tables : Table A and Table B.
Table B has foreign key reference to Table A.There are 2 forms in the application based on table A (form 1) and table B (form 2).
Scenario:
Now when we open form 2, the functionality is such that it acquires a lock on table B for the selected record during the search criteria. Lock is acquired by using "select 1 from table_B where column = :column for update no wait".
So when the form 2 is opened by any other user/session and same record is tried to be selected, then an exception is raised to the user that the current record is being edited by some other user and does not allow him to edit that record.
Now imagine if User has opened screen 2 (One record in Table B has been locked). With this lock existing, we open form 1, and click a button which performs a COMMIT_FORM. At this point the form hangs. On checking the locked objects, there is a lock on both table A and table B.
When the Form 2 is closed, then the Form 1 which was hanging a while ago starts responding.
When the foreign key relationship is dropped and the above scenario is tried, there is no issue encountered. Form 1 works fine even if form 2 is open.
Can you please share your knowledge and experiences to help us get out of the crisis. We are not supposed to drop the Foreign key relationship as well.
Appreciate your help on this.
Regards,
Bahubcd
|
|
|
|
|
|
|
Re: Commit_Form issue in Oracle Forms [message #539216 is a reply to message #539214] |
Thu, 12 January 2012 04:01 |
cookiemonster
Messages: 13938 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I assumed the problem was this.
If not I can only assume there is some code in form 1 that is trying to do something to table B (or a DB trigger on table A). You may have to trace the session to see at what point the lock is taken.
|
|
|