SqlConnection 如何管理 IsolationLevel?

How does SqlConnection manage IsolationLevel?(SqlConnection 如何管理 IsolationLevel?)

问题描述

此 MSDN 文章 指出:

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

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) 执行这个查询然后这个查询即使您没有明确表示，也会以可序列化模式执行设置它.

希望这能消除一些疑虑.:)

隔离级别在底层 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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！