foreign keys and a lock query

[pst]

I have a table

CREATE TABLE LANGUAGE_TRANSLATIONS (
ACCOUNT_ITEM_ID VARCHAR2 (40) NOT NULL,
COUNTRY_CODE VARCHAR2 (20) NOT NULL,
TRANSLATION VARCHAR2 (1000),
CONSTRAINT PK_LANGUAGE_TRANSLATIONS
PRIMARY KEY ( ACCOUNT_ITEM_ID, COUNTRY_CODE ) );

Then I create a foreig key with these 2 columns account_item_id and country_code separately,
like

alter table language_translations
add constraint fk_lt_c
foreign key (country_code) references countries(country_code)
/

alter table language_translations
add constraint fk_lt_ai
foreign key (account_item_id) references account_items(account_item_id)
/

Is this the right way ? Should the foriegn key also be composite ? But the Countries table doe not contain account_item_id column and vice versa.

Please pour in your opinions. There seems to be lots of locks occuring with this table . What do I do when a table is locked ?

And can you please tell me how to come out of a deadlock and how to prevent it. The last time it happened, I had to ask everyone to log out and restarted the db.

thanks a lot

pst

[venudba]

Is this the right way ?

Yes

[dvc]

hi,

You might need to check the apps programs accessing the
tables, there might be emplicit/explicit locks in them.

[tamilselvan]

Add indexes on the FK, your locking problem will be solved.

[Raminder]

Originally posted by pst:

Please pour in your opinions. There seems to be lots of locks occuring with this table . What do I do when a table is locked ?

I think you need to consult your application developers. They
should avoid higher level locking unless extremely critical to design. If they do use high level locking, they should set up processes so that they acquire locks in the same order.

And can you please tell me how to come out of a deadlock and how to prevent it. The last time it happened, I had to ask everyone to log out and restarted the db.

To come out of the deadlock, unfortunately, you have to terminate the user processes contending for the lock.
Identify the holding sessions and terminate them using Alter System Kill session.