Home » Developer & Programmer » Forms » Commit_Form issue in Oracle Forms (Forms 10G, Oracle 10G)
Commit_Form issue in Oracle Forms [message #539121] Wed, 11 January 2012 07:54 Go to next message
bahubcd
Messages: 33
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 #539126 is a reply to message #539121] Wed, 11 January 2012 08:35 Go to previous messageGo to next message
cookiemonster
Messages: 10568
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are the columns that form the foreign key in table b indexed?
Re: Commit_Form issue in Oracle Forms [message #539185 is a reply to message #539126] Wed, 11 January 2012 23:30 Go to previous messageGo to next message
bahubcd
Messages: 33
Registered: July 2007
Location: Bangalore
Member
Table B has 3 columns as primary key. So there is a primary key index on these 3 columns. One of these 3 columns is referenced to a column in table A.
Re: Commit_Form issue in Oracle Forms [message #539203 is a reply to message #539185] Thu, 12 January 2012 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 10568
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is it the first column in the index? If not you probably need a seperate index on it.
Re: Commit_Form issue in Oracle Forms [message #539214 is a reply to message #539203] Thu, 12 January 2012 03:54 Go to previous messageGo to next message
bahubcd
Messages: 33
Registered: July 2007
Location: Bangalore
Member
It is not the first column in the index.

I tried the same scenario creating a separate index, but the problem still exists.
Re: Commit_Form issue in Oracle Forms [message #539216 is a reply to message #539214] Thu, 12 January 2012 04:01 Go to previous message
cookiemonster
Messages: 10568
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.
Previous Topic: Navigation problem using go item
Next Topic: Need Help
Goto Forum:
  


Current Time: Wed Apr 16 00:55:30 CDT 2014

Total time taken to generate the page: 0.15634 seconds