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
Answer by WRBI ·
Following because I this looks interesting & I'm not sure of the answer :D
MSDN Blog might help - I was looking Exchange Event parallelism deadlocks & found it.
A small extract:
Workaround #1: Add an index or improve the query to eliminate the need for parallelism.In most cases, the use of parallelism in a query indicates that you have a very large scan, sort, or join that isn't supported by proper indexes.If you tune the query, you will often find that you end up with a much quicker and more efficient plan that doesn't use parallelism, and therefore isn't subject to this type of problem. Of course, in some queries (DSS/OLAP-type queries, in particular) it may be difficult to eliminate all large scans.
Workaround #2: Force single-threaded execution with an "OPTION (MAXDOP 1)" query hint at the end of the query.If you can't modify the query, you can apply the hint to any query with a plan guide (assuming that you're running SQL 2005 or later).