0

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.

6
  • 1
    I'm not sure why you expect connection 2 to be able to read rows that haven't yet been committed by connection 1? Connection 2 is waiting for connection 1 to commit or roll back. Jul 13 '15 at 14:46
  • "what can help me avoid the lock here?" -- nothing. You cannot avoid locks when changing records (that includes inserting them). The statement in the question is wrong though -- it's not the table that is locked, it's the rows that you have inserted.
    – mustaccio
    Jul 13 '15 at 14:50
  • @Aaron, I didn't expect connection 2 to be able to read any uncommitted data, but I did expect it to return something based on the state from before the transaction was started, instead of being held until it times out. I expected the two reads on connection 2 to return two different results - 0 before and 1 after the commit.
    – Luc VdV
    Jul 15 '15 at 13:41
  • @mustaccio - I also tried it with 'select top 1 x' instead of 'count (*)'. If the table wasn't locked, shouldn't the second query then have returned something (in this case NULL, because there were no committed records yet)?
    – Luc VdV
    Jul 15 '15 at 13:46
  • 1
    May be you could read about transaction isolation?
    – mustaccio
    Jul 15 '15 at 13:55
1

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.

2
  • This was just sample code, but also with 'select top 1 x' (i.e. NOT scanning the entire table) the second query was held until it timed out.
    – Luc VdV
    Jul 15 '15 at 13:47
  • SELECT TOP 1 ... can just as well scan the entire table. Only by anbayzing the execution plan and execution stats can one tell if the table was scanned or not. You can easily inspect the lock resource on which the blocking occurs and judge if is a table or a row. Jul 15 '15 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.