Database Blocking [closed]

Question

We have been using SQL Server 2014 Enterprise Edition for one of our applications and have been experiencing severe blocking the past few days. This application is supported by a vendor and hence we don't know much about the details of the product and its code.

For a work-around, I suggested them to enable Read Committed Snapshot on the database, which would reduce the blocking as readers won't block writers and vice versa. However, some blocking is still occurring and they are not clearing themselves.

This blocking is involving one sequence object, which generates unique numbers and is allocated to every session for maintaining a unique number. The moment I kill that spid, everything starts working normally and the blocks get cleared in few seconds.

I couldn't find any posts about sequence objects and blocking on any forums.

There are two types of locks held - one is LCK_M_X and other one is LCK_M_U. Basically there are two tables, which is updated and selected at the same time; which seems to be causing the DB blocking however query with longest time, which is displayed in result set (sp_whoisactive) is the query responsible to fetch the next sequence number. Blocking happens only when next seq and update runs simultaneously.

It's not using a table, they have used a SEQUENCE object only to maintain unique incremental number with cache value of 25. This seems to be flaw with application architecture only however I was trying to provide some solution in my environment to avoid DB blocking. Not sure, if anything else can be done other than enabling read committed snapshot (without changing code).

Answer 1

Since you have not posted any details about the blocking queries and related tables, your question can only be answered with generalities. If you can post more information that leads to a specific answer I will gladly delete this answer.

In general, if a third-party application has code that results in excessive blocking, the best solution is to open a support incident with the vendor so that they can deliver a fix. I state this knowing full well how painful this is in some situations. My only advice is to give the technical support representatives all of the information they request without any delay, let them know the impact on the business, and graciously request that the incident be escalated to engineering if they don't have a solution. Kindly remind them that delaying for a couple of days and asking for more information is not acceptable. Get the vendor's Sales team involved if quick progress is not made.

Enabling Read Committed Snapshot was probably a good move technically as it might have solved it, but unfortunate that it did not. It is possible, but not likely in my estimation, that if exposed code (e.g., code in a stored procedure or web page) is involved in the blocking that it could be fixed without support from the vendor. However, I wouldn't recommend this unless the vendor for some reason refuses to fix it and you can't live with the problem. Modifying the vendors' code may lead to unexpected results, even worse problems, possibly data corruption, and will put you in a compromised position with the vendor from a support standpoint. Again, I am not suggesting this is a reasonable or realistic alternative, and in fact I would recommend avoiding this approach.