Sql-server – SQL Server: INSERT in transaction locks the table until transaction finished

sql server

The sample VB.Net code below gets stuck and times out at the first read operation on CMD2.
That seems to contradict the top ranked answer to question 6374.

If that answer was correct, what can help me avoid the lock here?

SQL Server 2008 was used for testing this.

    Using cn1 As New SqlConnection("Server=(local);database=tempdb;Integrated security=SSPI"),
          cn2 As New SqlConnection("Server=(local);database=tempdb;Integrated security=SSPI"),
          cmd1 As SqlCommand = cn1.CreateCommand,
          cmd2 As SqlCommand = cn2.CreateCommand

        cn1.Open()
        cn2.Open()

        Try
            cmd1.CommandText = "CREATE TABLE ttt1 (x int, y int)"
            cmd1.ExecuteNonQuery()

            cmd1.Transaction = cn1.BeginTransaction(IsolationLevel.ReadCommitted)

            cmd1.CommandText = "INSERT INTO [ttt1] VALUES (1,2)"
            cmd1.ExecuteNonQuery()

            ' The next query holds up execution,
            ' waiting for the table to become available
            ' until a timeout occurs.
            ' Tried "select top 1 x" as well: also times out.
            cmd2.CommandText = "SELECT count(*) FROM ttt1"
            Dim i1 As Integer = CInt(cmd2.ExecuteScalar)

            cmd1.Transaction.Commit()

            Dim i2 As Integer = CInt(cmd2.ExecuteScalar)
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
        Finally
            cmd1.CommandText = "DROP TABLE ttt1"
            cmd1.ExecuteNonQuery()
        End Try

        cn2.Close()
        cn1.Close()
    End Using

I would post this as a comment to the highest quoted answer to question 6374, but (1) it's too long to post as a comment, (2) I don't have the required points to comment yet, (3) posting a question as an answer to another question seemed even more wrong than launching a new question about the same thing.

Best Answer

By issuing a SELECT COUNT(*) FROM ... you are requesting to read every row in the table. This will include the newly inserted, uncommitted, rows. As the original answer says, at no time is the table locked. By designing a test that explicitly looks at the locked rows you are, as expected, blocking yourself.

Well tuned workloads never scan the entire table. Scans create performance problems because they need to read the entire table and, as you discovered, are guaranteed to block behind any lock.

Related Question