1

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.

Improve this question
4
  • 2
    RCSI and SNAPSHOT only eliminates locks from SELECT. INSERT, UPDATE and DELETE still use locks. And this could be caused by uncommitted transaction on a connection that is not closed.
    – David Browne - Microsoft
    Aug 2, 2017 at 12:54
  • @DavidBrowne-Microsoft: I see. Our app (both versions) also employs its own connection pool instead of the one ADO.NET provides by default. While we do strive to commit or rollback transactions after every request, the connections themselves are kept open and simply returned to pool. I'm assuming this is fine? I probably have to look for possible bugs in the releasing algorithm. I suspect that the legacy app at least might be keeping a transaction or two open past its time.
    – aoven
    Aug 2, 2017 at 14:40
  • 1
    You can always issue "if @@trancount > 0 rollback" to definitively roll back any outstanding transaction when returning the connection to your custom connection pool.
    – David Browne - Microsoft
    Aug 2, 2017 at 14:41
  • That seems like a nice stop-gap measure until I identify the actual leak. Thank you!
    – aoven
    Aug 2, 2017 at 14:43

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.