We've recently enabled
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