c# - SqlConnection 是如何管理 IsolationLevel 的？

Question

这MSDN article指出:

An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled.

SqlConnection类没有可能拥有隔离级别的成员。那么连接如何知道要在哪个隔离级别运行？？？

我问这个的原因是因为以下情况:

1. 我使用Serializable 中的 TransactionScope模式，说“T1”。
2. 为 T1 打开了一个连接。
3. T1 完成/配置，连接回到连接池。
4. 调用了另一个相同的查询连接(从连接池)并且此查询运行在可序列化模式下!!!

问题:

1. 池连接如何仍然知道隔离级别是什么与之相关？？？
2. 如何将其还原为其他交易级别???

Resolution:
The reason why pooled connections are returning the serializable isolation level is because of the following reason:

1. You have one connection pool (let's say CP1)
2. CP1 may have 50 connections.
3. You pick one connection C1 from CP1 and execute it with Serializable. This connection has its isolation level set now. Whatever you do, this will not be reset (unless this connection is used to execute a code in a different isolation level).
4. After executing the query C1(Serializable) goes back to CP1.
5. If steps 1-4 are executed again then the connection used may be some other connection than C1, let's say C2 or C3. So, that will also have its isolation level set to Serializable.
6. So, slowly, Serialzable is set to multiple connections in CP1.
7. When you execute a query where no explicit isolation level setting is being done, the connection picked from CP1 will decide the isolation level. For e.g. if such a query requests for a connection and CP1 uses C1(Serializable) to execute this query then this query will execute in Serializable mode even though you didn't explicitly set it.

Hope that clears a few doubts. :)

Answer 1

隔离级别在底层 DBMS 中实现，例如 SqlServer。设置隔离级别很可能会设置为连接设置隔离级别的 SQL 命令。

只要连接保持打开状态，DBMS 就会保持隔离级别。因为连接被放入池中，所以它保持打开状态并保留之前所做的设置。

当弄乱隔离级别时，您应该在任何事务结束时重置隔离级别，或者更好的是，在请求新连接时设置它。