Sql-server – SQL Server LCK_M_X lock from .NET application

lockingsql server

We've recently enabled READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION on our SQL Server 2014 database, which is accessed primarily from 2 versions our ASP.NET application via ADO.NET.

The new version (still in beta) is working fine, while our legacy (currently still in production) version is exhibiting a peculiar problem inside a particular API.

This API opens a DB transaction and executes a number of SELECTs, INSERTs, and UPDATEs over a set of tables (between 3 and 5, depending on the exact request).

The problem is, the API gets stuck when executed by the legacy version of the app. Inspection by Activity Monitor shows that there are two processes with distinct Session IDs (which correspond to two separate DB connections, right?), one of which is trying to execute an UPDATE, while the other is doing nothing else but blocking the first one.

My question is: am I correct in assuming that the legacy version of our app has some sort of bug that causes the API request to use two separate connections in parallel (possibly one for reading and one for updating), which then trigger the lock we're observing?

If not, what else could possibly explain the behavior we're seeing?

What's bothering me is the fact that the new snapshot isolation level should not cause ANY locks between parallel transactions. At least that's how I understand it. I'd be happy to be shown otherwise!

FWIW, killing the inactive session allows the UPDATE to succeed without a problem. Without that, they both remain "hung" forever. The problem also didn't occur (to my knowledge, at least) before we enabled the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options.

Best Answer

It turned out we had a bug that sometimes caused a connection with a still active transaction to leak out of the pool without being properly disposed. It was this transaction that caused locks down the road. After fixing the leak, the problem is now gone!

The READ_COMMITTED_SNAPSHOT change was a red herring.