SqlDataReader对象和数据库访问的并发 [英] SqlDataReader and database access concurrency

问题描述

来说明我的问题最简单的方法是使用本C＃code：

The easiest way to illustrate my question is with this C# code:

using (SqlCommand cmd = new SqlCommand("SELECT * FROM [tbl]", connectionString))
{
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        //Somewhere at this point a concurrent thread, 
        //or another process changes the [tbl] table data

        //Begin reading
        while (rdr.Read())
        {
            //Process the data
        }
    }
}

所以会在这样的情况在数据RDR 会怎么样呢？

推荐答案

我实际测试了这一点。测试code：

I actually tested this. Test code:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString))
{
    conn.Open();
    using (SqlCommand comm = new SqlCommand("select * from test", conn))
    {
        using (var reader = comm.ExecuteReader())
        {
            int i = 0;
            while (reader.Read())
            {
                if ((string)reader[1] == "stop")
                {
                    throw new Exception("Stop was found");
                }
            }
        }
    }
}

要测试，我有一些虚拟的数据初始化表（确保与价值'一站式'没有行被列入）。然后，我把一个破发点就行了 INT I = 0; 。而执行停产后的破发点，我插入了'一站式'的价值在表中的一行。

To test, I initialized the table with some dummy data (making sure that no row with the value 'stop' was included). Then I put a break point on the line int i = 0;. While the execution was halted on the break point, I inserted a line in the table with the 'stop' value.

其结果是，根据初步表中的行量，异常被抛出/没投进。我并没有试图牵制哪儿行限制了。对于十行的异常不抛，这意味着读者没有注意到从另一个过程中添加的行。一万行，异常被抛出。

The result was that depending on the amount of initial rows in the table, the Exception was thrown/not thrown. I did not try to pin down where exactly the row limit was. For ten rows, the Exception was not thrown, meaning the reader did not notice the row added from another process. With ten thousand rows, the exception was thrown.

因此​​，答案是：那要看。如果没有包装命令/读者交易里面，你不能依赖任一行为。

So the answer is: It depends. Without wrapping the command/reader inside a Transaction, you cannot rely on either behavior.

强制性免责声明：这是它是如何工作在我的环境...

Obligatory disclaimer: This is how it worked in my environment...

编辑：

我测试使用的是本地SQL Server上我开发计算机。它报告本身：

I tested using a local Sql server on my dev machine. It reports itself as:

Microsoft SQL Server的2008 R2（SP1） - 10.50.2550.0（X64）

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

关于交易：

下面是code，我使用事务：

Here's code where I use a transaction:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString))
{
    conn.Open();
    using (var trans = conn.BeginTransaction())
    using (SqlCommand comm = new SqlCommand("select * from test", conn, trans))
    {
        using (var reader = comm.ExecuteReader())
        {
            int i = 0;
            while (reader.Read())
            {
                i++;
                if ((string)reader[1] == "stop")
                {
                    throw new Exception("Stop was found");
                }
            }
        }
        trans.Commit();
    }
}

在此code，我创建事务而不显式指定的隔离级别。这通常意味着 System.Data.IsolationLevel.ReadCommitted 将被使用（我觉得默认的隔离级别可以在SQL Server设置的地方进行设置）。在这种情况下，阅读器的行为和以前一样。如果我改变它的使用方法：

In this code, I create the transaction without explicitly specifying an isolation level. That usually means that System.Data.IsolationLevel.ReadCommitted will be used (I think the default isolation level can be set in the Sql Server settings somewhere). In that case the reader behaves the same as before. If I change it to use:

...
using (var trans = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
...

停止记录的插入被阻止，直到该交易comitted。这意味着，当读取器是活动的，没有变化的数据基础，允许由SQL Server

the insert of the "stop" record is blocked until the transaction is comitted. This means that while the reader is active, no changes to underlying the data is allowed by Sql Server.

这篇关于SqlDataReader对象和数据库访问的并发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！