Slow SQL transaction blocks table

问题:

I have a very slow sql transaction, which inserts new rows in the table. All other "select" queries from another connections wait for this transction to unlock the table.

Is it posible to fetch old rows from the table, while the first transaction works?

SqlExpress 2008 R2. For 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 method doesnt't fetch a row immidiately, it waits for commit in I have a very slow sql transaction, which inserts new rows in the table. All other "select" queries from another connections wait for this transction to unlock the table.

Is it posible to fetch old rows from the table, while the first transaction works?

SqlExpress 2008 R2. For 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 method doesnt't fetch a row immidiately, it waits for commit in button1_Click thread.

回答1:

One quick 'get out of jail' card is enabling read committed snapshot on the database, see Choosing Row Versioning-based Isolation Levels, also mentioned in Deadlocked!. When RCSI is enabled on the database your butonn2 click read will do exactly what you want: it will read an old version of the row, w/o waiting for button1 to commit.

To enable RCSI simply run this once:

ALTER DATABASE [test]  SET READ_COMMITTED_SNAPSHOT ON;

Of course there is no free lunch: enabling row-versioning will occur a cost in tempdb IO and size. See Row Versioning Resource Usage. For an Express instance there will be no measurable impact.