deadlocks because of triggers
-
Thursday, June 14, 2012 9:13 PM
I am currently modifying a process which loads a target table from different source databases through a query which run in parallel through a set of ssis packages which are called dynamically.
the target table has a trigger which does the below funcitionality
ALTER TRIGGER [dbo].[table_trg]
ON [dbo].[table]
FOR DELETE, UPDATE
AS INSERT INTO tablehistory
(col1..)SELECT col1... FROM DELETED
when i am doing my full volume testing using 151 source databases, i ended up in deadlock on the "tablehistory" table. I avoided this by moving the logic to the SSIS package, but I am not happy with the timelines i got while testing this with this new logic.
I want to know if there is a way to avoid deadlock in the history table.. the history table has same structure as base table except for the identity column which is a new column.
Can someone also point me in the direction if we have any miscrosoft tool (no MDM please) which does database comparision and stores history and is more efficient, which i can call later at the end of my package.
- Edited by jag008-im Thursday, June 14, 2012 9:17 PM
All Replies
-
Thursday, June 14, 2012 9:25 PMModerator
-
Thursday, June 14, 2012 9:43 PM
I tried your approach above but that takes longer than the triggers.. my business logic is little complicated. Tranasactional file comes from clients (multiple sources). I load into source table and trigger puts only the updated records into the history table and not the inserted records. so there is a lookup before the insert into the target table, the lookup has one branching to insert into target table and other into the update process ( for update process, i load the keys into the temp which has actual key and surrogate key). the surrogate key(identity column) is used to load the value into the history into from base table and then actual update is issued on base table through execute sql task in ssis, but this is taking long time as there is clean up of this temp table and update step is extra and also this process for all the table objects in the db.
I would like to see if there is any option like no lock which can be icorporated in the triggers like the DFT table lock option as shown in the figure above in the previous thread.
An "inefficient,unscalable solution" is as good as "no solution".
-
Friday, June 15, 2012 3:17 PMModerator
Longer is better that having the process failed.
Lookups are both, time and resource consuming, this is the source of "length".
I suggest you try using the SQL Merge: http://technet.microsoft.com/en-us/library/bb510625.aspx
Arthur My Blog
-
Friday, June 15, 2012 3:22 PMModerator
You say a lot about updates in your solution... are you using the OLE DB Command component?
That will be a major source of slow...
If I understand you correctly, you're using a Lookup component to spot updates. Instead of routing those updates to an OLE DB Command, you could route all those rows to a Destination - a temporary table. After the Data Flow, you can then issue a batch update with an Execute SQL Task to insert the existing rows in your destination table that match keys in your temporary table to the history table. Use another EST to delete those rows (or you could have two statements in your first EST). You can then use another Data Flow to insert your "changed" rows.
Talk to me now on
-
Tuesday, June 19, 2012 2:03 PM I do not use ole db command for updates as it is done on per row basis. I made my lookup query dynamic based on source client and this made my process efficient. just fyi, go to DF task, modify the lookup query through a variable , the variable is coded through expression taking source as parameter.
An "inefficient,unscalable solution" is as good as "no solution".
- Marked As Answer by jag008-im Tuesday, June 19, 2012 2:03 PM
.png)
