Deadlock Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Deadlock Question

  1. #1
    Join Date
    Jan 2001
    Posts
    515
    I have a program that loads records into the database. We usually have about 6 of these loading programs running all day loading about 70,000 records as they come in. I am running into a deadlock problem because they all have to update this one table among others. In the long run I will change the code to avoid the deadlock. My question is , is there a way to specify to your program to wait for the lock to be released? Hopefully this will avoid the deadlock for now.

  2. #2
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    In PL/SQL it's possible with FOR UPDATE clause in a
    cursor.

    F.

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Assuming you do actually mean a deadlock then:

    A deadlock is a specific circumstance than cannot be resolved by waiting alone. The only resolution is for the process that is causing the deadlock to be rolled back:

    http://otn.oracle.com/docs/products/...cnsis.htm#6037

    The only way for you to solve this problem is to make sure all locks are always aquired in a manner which prevents deadlocks ie. a row in table A must always be locked before a row in table B can be locked. That way A can never lock a record that B is trying to lock, and vice-versa.

    The best way to do this is to write PL/SQL APIs to do all major load tasks. These can then do all the work in a consistent fashion.



    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Jan 2002
    Posts
    1
    If you have a parent-child relation between tables and you are updating the parent table's primary key, the entire child table will be locked in absence of an index on the foreign key. The same is true if you delete the parent table row.

    If you are doing any of the above, you can check if you have a foreign key that is not indexed.

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