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.