慢SQL事务块表

Question

我有一个非常慢的sql事务，它在表中插入新行。来自另一个连接的所有其他"select“查询都会等待此转换解锁该表。

在第一个事务工作时，是否可以从表中获取旧的行？

SqlExpress 2008 R2。对于exapmle：

    private void button1_Click(object sender, EventArgs e)
    {
        System.Threading.Thread t = new System.Threading.Thread(
            delegate()
            {
                var conn = new SqlConnection(@"Data Source=ARTNB\SQLEXPRESS;Initial Catalog=test;User ID=**;Password=******");
                conn.Open();
                var cmd = conn.CreateCommand();
                var tr = conn.BeginTransaction( IsolationLevel.RepeatableRead, "test");
                cmd.Transaction = tr;
                cmd.CommandText = @"INSERT INTO Cards (SerialNumber,OperationID,TariffID,RequestTime,State,AgentInfo) VALUES('1213','345',13, GETDATE(),1,'')";
                cmd.ExecuteNonQuery();
                //very slow transaction
                System.Threading.Thread.Sleep(300000);
                tr.Commit();
                conn.Close();
            });
        t.Start();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        var conn = new SqlConnection(@"Data Source=ARTNB\SQLEXPRESS;Initial Catalog=test;User ID=**;Password=******");
        conn.Open();
        var cmd = conn.CreateCommand();
        var tr = conn.BeginTransaction(IsolationLevel.RepeatableRead, "test2");
        cmd.Transaction = tr;
        cmd.CommandText = @"SELECT COUNT(*) FROM Cards";
        var r = cmd.ExecuteReader();
        r.Read();
        r.Close();
        tr.Commit();
        conn.Close();
    }

button2_Click方法不会立即获取行，它会等待提交，因为我有一个非常慢的sql transaction，它会在表中插入新行。来自另一个连接的所有其他"select“查询都会等待此转换解锁该表。

在第一个事务工作时，是否可以从表中获取旧的行？

SqlExpress 2008 R2。对于exapmle：

    private void button1_Click(object sender, EventArgs e)
    {
        System.Threading.Thread t = new System.Threading.Thread(
            delegate()
            {
                var conn = new SqlConnection(@"Data Source=ARTNB\SQLEXPRESS;Initial Catalog=test;User ID=**;Password=******");
                conn.Open();
                var cmd = conn.CreateCommand();
                var tr = conn.BeginTransaction( IsolationLevel.RepeatableRead, "test");
                cmd.Transaction = tr;
                cmd.CommandText = @"INSERT INTO Cards (SerialNumber,OperationID,TariffID,RequestTime,State,AgentInfo) VALUES('1213','345',13, GETDATE(),1,'')";
                cmd.ExecuteNonQuery();
                //very slow transaction
                System.Threading.Thread.Sleep(300000);
                tr.Commit();
                conn.Close();
            });
        t.Start();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        var conn = new SqlConnection(@"Data Source=ARTNB\SQLEXPRESS;Initial Catalog=test;User ID=**;Password=******");
        conn.Open();
        var cmd = conn.CreateCommand();
        var tr = conn.BeginTransaction(IsolationLevel.RepeatableRead, "test2");
        cmd.Transaction = tr;
        cmd.CommandText = @"SELECT COUNT(*) FROM Cards";
        var r = cmd.ExecuteReader();
        r.Read();
        r.Close();
        tr.Commit();
        conn.Close();
    }

button2_Click方法不会立即获取行，它会在button1_Click线程中等待提交。

Answer 1

一个快速的“出狱”卡片是在数据库上启用读取提交的快照，请参阅Deadlocked!中也提到的Choosing Row Versioning-based Isolation Levels。当在数据库上启用RCSI时，您的butonn2单击read将执行您想要的操作:它将读取行的旧版本，但不等待button1提交。

要启用RCSI，只需运行一次：

ALTER DATABASE [test]  SET READ_COMMITTED_SNAPSHOT ON;

当然，没有免费的午餐:启用行版本控制将以临时数据库IO和大小为代价。参见Row Versioning Resource Usage。对于Express实例，将不会有可测量的影响。