SQL Server deadlocking issues
Hello all:
I am having some issues with sql server deadlocking. Just to explain my scenario: I have the following tables:
tblInvoice
tblInvoiceArchive
tblInvoice has a trigger that fires whenever a modification is done and adds or updates data to the tblInvoiceArchive. It''l basically add a new record if it's a new invoice or update existing data if the invoice was already existing.
Additionally, I have a SSIS package that runs based on a flag in the tblInvoiceArchive table. Once it runs it sets the flag to false.
What's been happening is people are making modifications to some of the data in the tblInvoice table, which then updates the tblInvoiceArchive table at the same time my SSIS package is trying to reset the flag in the tblInvoiceArchive table.
Is there something I can do that will allow these operations to occur with out the deadlocking? My SSIS package updates just one field, while a modification to tblInvoice may update any of the other fields (except the flag column).
Many thanks for your assistance!
I am having some issues with sql server deadlocking. Just to explain my scenario: I have the following tables:
tblInvoice
tblInvoiceArchive
tblInvoice has a trigger that fires whenever a modification is done and adds or updates data to the tblInvoiceArchive. It''l basically add a new record if it's a new invoice or update existing data if the invoice was already existing.
Additionally, I have a SSIS package that runs based on a flag in the tblInvoiceArchive table. Once it runs it sets the flag to false.
What's been happening is people are making modifications to some of the data in the tblInvoice table, which then updates the tblInvoiceArchive table at the same time my SSIS package is trying to reset the flag in the tblInvoiceArchive table.
Is there something I can do that will allow these operations to occur with out the deadlocking? My SSIS package updates just one field, while a modification to tblInvoice may update any of the other fields (except the flag column).
Many thanks for your assistance!
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Which is why us seeing the actual trigger code and DDL for the table would have been extremely helpful in determining the problem. Good luck with future qs.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
2) Worst case, you could transparently store the column in a different physical table, isolating the UPDATEs but leaving the access to it the same.
DDL for the table would be very helpful, as would, as noted above, the trigger code itself.