My recent troubleshooting engagement was about a SQL server blocking problem.

Imagine an insurance web application with large forms. The app automatically saves a draft version of the data of the half-filled form to avoid losing the customer’s work.

Saving the form is implemented by simple inserts and updates to a SQL Server table by Entity Framework.

My client experienced heavy blocking and deadlocks between the modification statements. Based on the frequency of the statements, I didn’t expect such frequent conflicts. SQL Server can easily handle hundreds of concurrent inserts and updates to the same table if it is not too heavily indexed. (By the way, I had an investigation when Indexed Views caused severe contention. I’ll write about it in the future.)

A large DML operation can block other modifications; this is natural. But these operations were quick; they took less than 10ms to complete. There were no other conflicting activities on the target table.

I examined the blocking info retrieved from the Extended Event logs of the SQL server, and it became apparent that the blocker process was sleeping instead of actively processing a DML statement. This fact suggests that the client sends the update to the SQL Server, leaving the transaction open for a more prolonged period. And we know transactions force SQL Server to keep the (exclusive) locks on the database rows alive till the transaction completes. 

This hypothesis was not consistent with the fact the data access layer used Entity Framework to execute the modifications by calling SaveChanges for only one entity. EF properly opens and closes the SQL connection during SaveChanges. Therefore there was no explanation why the transactions were so long.

However, examining the application logs, there were suspicious exceptions like: “There is already an open DataReader associated with this Command which must be closed first.”. Or: “The connection was not closed. The connection’s current state is open.”

I have seen similar exceptions in the past, and each of them involved racing threads. It is an indication that the single-threaded Entity Framework DbContext is used by more than one thread. However, it is designed for single-threaded use. For example, one thread opens the database connection via a DBContext and starts to fetch data from a select statement. Meanwhile, another thread tries to open the same connection via the same DBContext, which wreaks havoc on the state of the DBContext. Their behavior is undefined when multiple threads access a SqlConnection or a DbContext. In this case, DbContext can leave the connection open! You cannot blame Microsoft; this is documented: “

• DbContext is not thread-safe. Do not share contexts between threads. Make sure to await all async calls before continuing to use the context instance.
• An InvalidOperationException thrown by EF Core code can put the context into an unrecoverable state. Such exceptions indicate a program error and are not designed to be recovered from.”

Remember, an ASP.NET application is heavily multithreaded. How could multiple threads access the same DBContext? An inappropriate Unity lifetime manager shared the instances between the competing threads — the documentation address this issue here.

After correcting the lifetime issue, the exceptions and the blocking completely disappeared. Actually, there were literally zero inserts or update operations that lasted more than 1 second, even under the heaviest load. Earlier 5-30 second batch duration was “normal.”

So, next time you see severe blocking, investigate your connection usage and transaction duration instead of blaming the database.

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.