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 concurrency"

I'd like to understand:

What is persistent connection in ADO.NET?

I simply do not remember that, while programming .NET apps, I configured any persistence of SQL Server connections.

Where was it?

Is "persistent connection" antonym of disconnected mode?

What are implications of the need to have persistent connection in coding ADO.NET client apps (in, for ex., C#)?

How snapshot transaction isolation is to be used in ADO.NET client apps?

What is its (snapshot transaction isolation levels) main target area of engagement?

Also, I am somewhat confused, having read that most .NET frameworks and features, like, for ex., Linq2SQL, do not support pessimistic locking (well, hints)... but distributed transactions do not support snapshot isolation [2]

Should SQL Server built-in optimistic concurrency functionalities be avoided and substituted by custom optimistic (versioning) transaction support?

Again, what's for and why were they introduced in such (difficult to understand for me) implementation?

[1] Dan Guzman's Blog.

Concurrency Model Confusion II

h开发者_开发技巧ttp://weblogs.sqlteam.com/dang/archive/2008/06/07/Concurrency-Model-Confusion-II.aspx

[2]

Why isn't Snapshot isolation supported with Distributed Transactions in SQL Server

Why isn't Snapshot isolation supported with Distributed Transactions in SQL Server

[3]

when to prefer pessimistic model of transaction isolation over optimistic one?

when to prefer pessimistic model of transaction isolation over optimistic one?

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.