Should Snapshot Transaction Isolation Be Avoided In Client ADO.NET Applications? Then, What Was Its Main Purpose?

Having read in [1]: 'The persistent database connection requirement limits SNAPSHOT isolation scalability so it shouldn’t be used as a general means of implementing optimistic

Solution 1:

Although the documentation is not quite clear to me, here is how I interpret it. If I have a connection in my exclusive use, I can use snapshot isolation to implement optimistic concurrency, as I described in this article: Developing Modifications that Survive Concurrency. If there is a conflict, you will get an error message: "Snapshot isolation transaction aborted due to update conflict."

However, to utilize this approach, we must keep the connection open, and we cannot use connection pooling. Note that in some environments idle connections are automatically killed after some idle period.