SqlConnection 如何管理IsolationLevel?

问题描述

这篇MSDN文章指出:

隔离级别具有连接范围范围，一旦设置为连接使用 SET 事务隔离LEVEL 声明，它仍然有效直到连接关闭或设置了另一个隔离级别.当一个连接关闭并返回池，隔离级别从最后设置事务隔离级别声明被保留.随后的连接重用池连接使用隔离级别这在当时有效连接已池化.

SqlConnection 类没有可以持有隔离级别的成员.那么连接如何知道运行在什么隔离级别???

我问这个的原因是因为以下场景:

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

问题:

1. 池连接如何仍然知道什么隔离级别与之相关???
2. 如何将其恢复为其他交易层面???

<块引用>

解决方案:
池化连接返回可序列化隔离级别的原因如下:

1. 您有一个连接池(比如 CP1)
2. CP1 可能有 50 个连接.
3. 您从 CP1 中选择一个连接 C1 并使用 Serializable 执行它.此连接现在已设置其隔离级别.无论你做什么，这都不会被重置(除非这个连接是用于在不同的隔离级别执行代码).
4. 执行查询后，C1(Serializable) 返回到 CP1.
5. 如果再次执行步骤 1-4，则使用的连接可能是 C1 以外的其他连接，比如 C2 或 C3.所以，这也会将其隔离级别设置为 Serializable.
6. 所以，慢慢地，Serialzable 在 CP1 中设置为多个连接.
7. 当您执行未进行显式隔离级别设置的查询时，从 CP1 选择的连接将决定隔离级别.例如如果这样的查询请求连接并且 CP1 使用 C1(Serializable) 来执行这个查询然后这个查询即使您没有明确指定，也会在 Serializable 模式下执行设置它.

希望能消除一些疑问.:)

隔离级别在底层 DBMS 中实现，比如 SqlServer.设置隔离级别很可能会设置 SQL 命令，用于设置连接的隔离级别.

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

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

This MSDN article states that:

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.

The SqlConnection class has no member that may hold the isolation level. So how does a connection know what isolation level to run in???

The reason I'm asking this is because of the following scenario:

1. I opened a transaction using TransactionScope in Serializable mode, say "T1".
2. Opened a connection for T1.
3. T1 is finished/disposed, connection goes back to connection pool.
4. Called another query on same connection (after getting it from connection pool) and this query runs in serializable mode!!!

Problem:

1. How does the pooled connection still know what isolation level was associated to it???
2. How to revert it back to some other transaction level???

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. :)

Isolation levels are implemented in the underlying DBMS, say SqlServer. Setting the isolation level most probably sets up SQL commands which set the isolation level for the connection.

The DBMS keeps the isolation level as long as the connection stays open. Because the connections is put into the pool, it stays open and keeps the settings made before.

When messing around with isolation levels, you should either reset the isolation level at the end of any transaction, or, even better, set it when a new connection is requested.

这篇关于SqlConnection 如何管理IsolationLevel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！