“设置事务隔离级别读取未提交"不服用?还是我看错了? [英] "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" not taking? Or am I looking in the wrong way?

问题描述

我们有一些数据库代码的问题，这些代码显然以错误的隔离级别执行.在代码的这一特定部分中，它应该以READ UNCOMMITTED"执行以最小化锁定.不一致的数据在这一点上是可以的.

We have a problem with some database code that apparently executes with the wrong isolation level. In this particular part of the code, it is supposed to execute with "READ UNCOMMITTED" to minimize locks. Inconsistent data is OK at this point.

然而，代码实际上读取的是 READ COMMITTED，我们无法弄清楚原因.

However, the code actually reads with READ COMMITTED, and we can't figure out why.

这是我们所做的:

1. 打开连接
2. 在此连接上执行SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
3. 遇到断点
4. 执行 SQL

在断点处，我们向数据库发出这个命令:

On the breakpoint, we issue this command to the database:

select s.session_id, s.transaction_isolation_level, st.text from sys.dm_exec_sessions s
inner join sys.sysprocesses sp on (sp.spid = s.session_id) 
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) st

此 SQL 现在报告了 4 个池连接，其中之一是我们可以越过断点执行 SQL 的连接，它具有以下状态:

This SQL reports 4 pooled connections right now, one of which is our connection that we can step beyond the breakpoint to execute our SQL with, that has this state:

53  2   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

即.会话 53 具有隔离级别 2(READ COMMITTED)，并且在此会话上执行的最后一条 SQL 是SET TRANSACTION ..."命令.

ie. session 53 has isolation level 2 (READ COMMITTED), and the last SQL that was executed on this session was that "SET TRANSACTION ..." command.

这怎么可能?

我们使用 SQL Profiler 验证此连接在我们的 .NET 代码打开它之前不存在，因此它没有从连接池中重用.

We verified with SQL Profiler that this connection did not live before our .NET code opened it, so it was not reused from the connection pool.

然而，有了新的连接，并且在其上执行的唯一和第一个 SQL 明确告诉它使用 READ UNCOMMITTED，连接怎么可能仍然是 READ COMMITTED?

Yet, with a fresh connection, and the only and first SQL executed on it explicitly told it to use READ UNCOMMITTED, how can the connection still be READ COMMITTED?

我们应该在这里看什么?

What should we look at here?

连接字符串(编辑了位)是这样的:

The connection string (with bits redacted) is like this:

SERVER=hostname;DATABASE=dbname;Integrated Security=false;USER ID=sa;PASSWORD=****;Application Name=appname;Type System Version=SQL Server 2000;Workstation ID=hostname;

连接是正常的SqlConnection连接，正常打开.

The connections are normal SqlConnection connections, opened in the normal way.

不幸的是，如果我们编写打开 SqlConnection 的普通代码，我们将无法重现该问题，因此必须与应用程序状态有关，但是由于 SqlProfiler 和 Sql Server 都告诉我们是的，SQL 已执行，但是不，我不在乎.

Unfortunately we're unable to reproduce the problem if we write normal code opening a SqlConnection, so there has to be something with the application state, but since SqlProfiler and Sql Server both tells us that yes, the SQL was executed, but no, I don't care.

什么会对此产生影响?

完全相同的代码也打开其他连接，即代码执行多次并打开许多连接，因此池中的连接不止一个，但只有第一个连接最终出现此问题.

The exact same code also opens other connections, that is, the code is executed many times and opens many connections, so more than one connection ends up in the pool, yet only the very first connection ends up having this problem.

这是 SQL Server 2008 R2，我们在 2012 年也重现了这个问题.

This is SQL Server 2008 R2 and we have also reproduced this problem on 2012.

编辑

好的，提供更多信息.

首先，我们启用了池化，或者更确切地说，我们没有明确禁用它，也没有使用连接字符串来创建N"个池.

First, we are enabling pooling, or rather, we're not explicitly disabling it, nor are we twiddling the connection string to make "N" pools.

但是，此连接是第一个使用此特定连接字符串打开的连接，因此不会从池中检索.另请参阅我下面关于它永久生病"的说明.

However, this connection is the first being opened with this particular connection string, thus it is not retrieved from the pool. Also see my note below about it being permanently "sick".

这个连接是这样设置的:

This connection is being set up like this:

var conn = new SqlConnection(...);
conn.StateChance += connection_StateChange;

private void connection_StateChange(Object sender, StateChangeEventArgs e)
{
    if (e.CurrentState == ConnectionState.Open)
    {
        using (IDbCommand cmd = ((SqlConnection)sender).CreateCommand())
        {
            cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
            cmd.ExecuteNonQuery();
        }

在此之前我们没有执行任何其他 SQL.

We're not executing any other SQL before this.

请注意，此代码在应用程序的生命周期中被多次使用，只有它打开的第一个连接才会出错.

Note that this code is used many times during the lifetime of the application, it is only the very first connection it opens that ends up being wrong.

这种联系也会永久生病.由于每次我们打开连接(即使我们可能将它从连接池中取出)，上述状态更改事件都会执行，并尝试再次设置隔离级别.这也失败了，但仅针对此单个连接.

This connection also becomes permanently sick. Since every time we open the connection (even though we might get it out of the connection pool), the above state change event executes, attempting to set the isolation level again. This also fails, but just for this single connection.

<打击>此外，自从我发布这个问题以来，我们发现了一件事会对此产生影响.

Additionally we've found one thing that impacts this since I posted this question.

通过更改我在上面发布的连接字符串:

By changing the connection string, that I posted above:

...;Type System Version=SQL Server 2000;...

为此:

...;Type System Version=SQL Server 2008;MultipleActiveResultSets=true;...

然后这个问题就消失了，在前面列出的断点处，连接现在具有READ UNCOMMITTED"状态.

then this problem goes away, at the breakpoint listed earlier, the connection now has "READ UNCOMMITTED" state.

这是一个红鲱鱼，在我们实际执行代码之前，我们的概览中不再报告连接.

This was a red herring, the connection was no longer being reported in our overview until we had actually executed code there.

我们正在继续调试.

推荐答案

这里的问题是 SqlConnection.BeginTransaction 不带参数的默认读取已提交.我想我们不明白该页面上的默认隔离级别"文本是什么.

The problem here is that the SqlConnection.BeginTransaction that does not take parameters defaults to read committed. I guess we didn't understand what the "default isolation level" text is on that page.

那个页面有这样的文字:

That page has this text:

如果不指定隔离级别，则使用默认隔离级别.要使用 BeginTransaction 方法指定隔离级别，请使用采用 iso 参数 (BeginTransaction) 的重载.为事务设置的隔离级别在事务完成后一直存在，直到连接关闭或释放.在未启用快照隔离级别的数据库中将隔离级别设置为 Snapshot 不会引发异常.事务将使用默认隔离级别完成.

If you do not specify an isolation level, the default isolation level is used. To specify an isolation level with the BeginTransaction method, use the overload that takes the iso parameter (BeginTransaction). The isolation level set for a transaction persists after the transaction is completed and until the connection is closed or disposed. Setting the isolation level to Snapshot in a database where the snapshot isolation level is not enabled does not throw an exception. The transaction will complete using the default isolation level.

(我的亮点)

这是一个 LINQPad 脚本，用于演示:

Here's a LINQPad script that demonstrates:

void Main()
{
    using (var conn = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated security=true"))
    {
        conn.Open();
        Dump(conn, "after open");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after set iso");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "BEGIN TRANSACTION";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after sql-based begin transaction");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "COMMIT";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after sql-based commit");

        var trans = conn.BeginTransaction();

        Dump(conn, "after .net begin transaction", trans);

        trans.Commit();

        Dump(conn, "after .net commit");
    }
}

public static void Dump(SqlConnection connection, string title, SqlTransaction transaction = null)
{
    using (var cmd = new SqlCommand())
    {
        cmd.Connection = connection;
        if (transaction != null)
            cmd.Transaction = transaction;
        cmd.CommandText = "SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID";
        Debug.WriteLine(title + "=" + Convert.ToInt32(cmd.ExecuteScalar()));
    }
}

它会输出:

after open=2
after set iso=1
after sql-based begin transaction=1
after sql-based commit=1
after .net begin transaction=2
after .net commit=2

在这里您可以看到，通过 SQL 手动启动和提交事务不会改变隔离级别，但在 .NET 中启动事务而未明确说明隔离级别仍会将其更改为已提交读.

Here you can see that manually beginning and committing a transaction through SQL would not change the isolation level, but beginning a transaction in .NET without explicitly stating the isolation level still changes it to read committed.

因为我们读到的任何地方，在没有明确说明隔离级别的情况下启动事务都表示它继承了会话的隔离级别，我想我们不明白.NET不会这样做.

Since everywhere we read, starting a transaction without explicitly stating the isolation level said that it inherited the isolation level of the session, I guess we didn't understand that .NET would not do the same.

这篇关于“设置事务隔离级别读取未提交"不服用?还是我看错了?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！