DEADLOCK between 2 SELECT statements

Question

question

Ange asked Jun 13, '18

DEADLOCK between 2 SELECT statements

Hi,

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.

The Query:

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

Angeline

sql deadlock deadlock-graph redgate

deadlock.png (29.6 KiB)

deadlock.png (49.7 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-06-13T13:00:28Z

WRBI answered Jun 13, '18

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).

thoughts:

have a query execution plan?
can the ORDER BY be taken care of in the front end application?
Sargability - Can you do SuppInvoices.SuppInvDate >= '2018-01-01' and SuppInvoices.SuppInvDate < '2019-01-01' (parameters if needed)

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ange commented · Jun 13, 2018 at 01:21 PM

Thank you for your reply.

Good idea about eliminating the need for parrallelism. The query execution plan currently has a cost of over 20 with near 20k memory grant so I think you are right in saying I should look at the indexing to see if it resolves it.

I have on many occasion advised the developers that the Order By is better dealt with in the application and using the date Functions in the where clause isn't the best option but to be fair I am dealing with quite an old application that we are about to re-write. I would just like to stop these deadlock issues in the mean time. Unfortunately the query is built within the application rather than using stored procedures so this probably doesn't help the case either.

I will do some testing and post back with my results.

Thank you

1 ·

WRBI Ange commented · Jun 13, 2018 at 01:27 PM

Yes, definitely getting it as a SPROC into the DB will help loads. Good news that it's getting redeveloped!

0 ·

Ange commented · Jun 20, 2018 at 08:33 AM

After some testing I can confirm that things are much better now with the elimination of parallism for these queries. I have set Maxdop to 2 and Cost threshold for parallelism to 50 and additionally tuned the queries and added more indexes to keep the cost below 50.
Thanks for your help guys, much appreicated.

0 ·

Answer 2 · 2018-06-13T16:44:46Z

Elysian answered Jun 13, '18

One more point which i would like to add. It might be possible that these selects statements are getting executed though a session which holds IX LOCK on the table. I have faced the similar situation on my one of the production envr.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ange commented · Jun 14, 2018 at 08:21 AM

Interesting you say that because it was the first thing I looked into. Can I ask for more information on what your situation was Elysian? Was it specifically requesting an IX LOCK for the Selects?

I do a little development in our winforms applications but my knowledge is limited. It is using adLockReadOnly and adOpenForwardOnly. One thing I have noticed is that it leaves the recordset open. I believe the reason for this is for the use of the refresh button. I am thinking maybe we would be better closing the recordset and calling for the data again for the refresh button.

We have so many variances of the same application for different departments so to suggest to the developers this needs to be changed I would need to be sure of what I am saying. Appreciate this is now more of a VB6 query but if you or anyone else have any ideas on it I would be very appreciative.

Edit: I have just found out that there is an additional reason for keeping the recordset open and that is for the end user to be able to filter the dataset in the grid.

0 ·

question