foreign keys and a lock query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: foreign keys and a lock query

  1. #1
    Join Date
    Sep 2000
    Posts
    103
    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

  2. #2
    Join Date
    Aug 2001
    Location
    Hyderabad, India
    Posts
    29

    Smile

    Is this the right way ?

    Yes
    Venkateshwarlu.K

  3. #3
    Join Date
    Jul 2001
    Posts
    45
    hi,

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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Add indexes on the FK, your locking problem will be solved.



  5. #5
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width