Avatar of Juan Ocasio
Juan OcasioFlag for United States of America

asked on 

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 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!
Microsoft SQL ServerSSISSQL

Avatar of undefined
Last Comment
Scott Pletcher
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

1) Review the trigger to make sure it is as efficient as possible, even at the expense of clarity.  
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.
Avatar of Juan Ocasio
Juan Ocasio
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
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

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.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo