This content has been marked as final. Show 5 replies
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.
>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 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).>
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.
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)
did it effecting the situation?
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.
The usual way to create a form for this situation is a master-detail form only.
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 employeeIn 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.