I am hoping someone can put me out of my misery trying to get to the bottom of this issue I am having. I keep getting deadlocks in our database between 2 select statements and it is showing IX locks on the deadlock graph which I just cannot make any sense of. Both selects should take a shared lock and not impact each other so I don't understand where the IX lock is coming from.
Both spid's are running the exactly same query which is via a Winforms VB6 application. The query is a basic select from the supplier invoices table with a join to the suppliers table.
SELECT SuppInvoices.ID, SuppInvoices.Type, SuppInvoices.SupplierID, Suppliers.Name AS [Supplier Name], SuppInvoices.SuppInvNo AS [Inv No], SuppInvoices.SuppInvDate AS [Date], SuppInvoices.DateDue AS Due, SuppInvoices.DateRcvd AS Rcvd, SuppInvoices.SuppInvTOT AS [Invoice Total], SuppInvoices.AmountPaid AS [Amnt Paid], SuppInvoices.DatePaid AS [Date Paid], SuppInvoices.Reference, SuppInvoices.Status, SuppInvoices.Posted FROM SuppInvoices INNER JOIN Suppliers ON SuppInvoices.SupplierID = Suppliers.ID WHERE YEAR(SuppInvoices.SuppInvDate) = 2018 ORDER BY Suppliers.Name, SuppInvoices.SuppInvNo;
There are 3 triggers on the SuppInvoices table but they are only for Inserts/Updates/Deletes so I don't see how they would cause a problem.
There are no triggers on the Suppliers table
From my understanding the Exchange Events are from parallel processing but feel free to correct me if that is incorrect.
Appreciate any help at all. I am going around in circles googling and funnily enough doing the same thing with the deadlock graph :-)
Thanks in advance