Deadly Embrace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Deadly Embrace

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    Deadly Embrace

    Hi

    My friend has a informatica (ETL) tool running that causes deadlock when certian mapping is run.
    The mapping is a simple aggregate on a table and based on the results of the aggregation a table is updated and this causes deadlock portion of the trace file is reproduced below

    Code:
                           ---------Blocker(s)--------  ---------Waiter(s)---------
    Resource Name          process session holds waits  process session holds waits
    TX-0008000c-0000f779        41    2427     X             38    2465           S
    TX-00020007-0000d6d4        38    2465     X             39    2418           S
    TX-00040007-0000de97        39    2418     X             41    2427           S
    session 2427: DID 0001-0029-00004144	session 2465: DID 0001-0026-0000345B
    session 2465: DID 0001-0026-0000345B	session 2418: DID 0001-0027-0000475D
    session 2418: DID 0001-0027-0000475D	session 2427: DID 0001-0029-00004144
    Rows waited on:
    Session 2465: obj - rowid = 00017940 - AAAX8SAI/AAAF9gAAA
      (dictionary objn - 96576, file - 575, block - 24416, slot - 0)
    Session 2418: obj - rowid = 00017940 - AAAX8SAI/AAAFwAAAA
      (dictionary objn - 96576, file - 575, block - 23552, slot - 0)
    Session 2427: obj - rowid = 00017940 - AAAX8SAQoAAABdYAAA
      (dictionary objn - 96576, file - 1064, block - 5976, slot - 0)
    The strange thing is if i add a step into the flow immediately after aggregation to store the resultset into a temp table and then use the temptable to update the target table there are no deadlocks.Any idea why the deadlock is happening

    1)The rowids do not match to any rows in the target table
    AAAX8SAI/AAAFwAAAA
    AAAX8SAI/AAAF9gAAA

    regards
    Hrishy

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    may be a select for update on source table.. and corresponding row getting updated.. on destination table..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Abhay

    Thanks for your time.
    What i have seen is informtica tool actually is unloading the records from the source table into a .dat and .idx file which is its propreitary format and then does the aggregation and updates the traget table

    regards
    Hrishy

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    well may.. it does what so ever.. all it matters is lock.. does it release the locks on source table until target updation?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Does the lock on the source table matter ?

    I am getting the deadlock when i am updating a target table which is different from the source table.

    In the trace file there are no locks on the source table.

    regards
    Hrishy

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    well well.. imagine something like this..

    select .. trgt.x from source_table src, target_table trgt where .. for update of ..

    and then you update target table using this records in file..

    again u do some select for update.. then you messsup here..


    abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Abhay

    Thanks for taking a look at this again.

    The ETL happens like this

    1)Informatica first partitions the input dataset(a.k.a table) and then on the partitioned dataset it does some aggregations and writes this to a file system

    2)After step 1 informatica does not attach to the source table

    3)It taken the aggregated resultset on the filesystem and then matches this with the primary key of the target and spawns again 5 threads to run the update and that is when i hit the deadlock error

    If instead of writing the aggregate to the file system i write it to a temp table in the database and continue i dont hit the deadlock error

    regards
    Hrishy

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Now is the temp table empty... may be it just does a insert.. (also does this tmp table have PK enabled on it)?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Abhay

    Yes the temp table is empty before the load and it does not have a PK.

    Ia m suspecting informatica is doing something funny under the covers

    regards
    Hrishy

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    not really..

    ***
    3)It taken the aggregated resultset on the filesystem and then matches this with the primary key of the target and spawns again 5 threads to run the update and that is when i hit the deadlock error
    ***

    5 threads.. means 5 sessions to oracle.. right? and you dont know the manner of updates.. you can have many scnerios for why ur seeing deadlock..

    look temp table dosent have any constraints, but target table do have..

    while loading into temp you will not see any lock waits across sessions as it dosent have any constraints.. however when it comes to target table.. and you have constraints on it.. there comes waits across sessions...

    rgds
    abhay
    Last edited by abhaysk; 09-20-2007 at 07:32 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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