-
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
-
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"
-
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
-
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"
-
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
-
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"
-
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
-
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"
-
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
-
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 06: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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|