Transaction Lockout Of Reads

Hi,

I have been seeing a basic scenario of a write transaction appearing to unexpectedly lock-out reading.

The database has isolation set to "READ COMMITTED".

The scenario is:

1.) Start a transaction (for doing a write)

2.) Do a read before the transaction (for doing the write) is committed (e.g. sqlCommand2.ExecuteReader()).

--> the code will appear to lock-up (then time out).

I see the same behavior if I step through the "write" code with the debugger (to a point after the transaction is started, but before it is committed), and run a "SELECT * FROM" type query from Microsoft SqlServer Management Studio.

Following is the code sample demonstates the issue.

Thoughts on how to resolve the issue (to let me do "read committed" reading of the database table)?

Thanks!

Andy







Module Transaction

Sub Main()

Dim exception1 As Exception

Try

' Create/Open Database Connection

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Server=GRB-AB;Database=Transaction;Trusted_Connection=True;")

sqlConnection1.Open()

' Start transaction

Dim sqlTransaction1 As System.Data.SqlClient.SqlTransaction = sqlConnection1.BeginTransaction()

' Set Parent record

Dim sqlCommand1 As New System.Data.SqlClient.SqlCommand("INSERT INTO Parent (Name) VALUES ('ParentValue');", sqlConnection1)

sqlCommand1.Transaction = sqlTransaction1

sqlCommand1.ExecuteNonQuery()

' Get Id from parent record (note: this code assumes the table was empty when this program starts)

sqlCommand1 = New System.Data.SqlClient.SqlCommand("SELECT Id FROM Parent;", sqlConnection1)

sqlCommand1.Transaction = sqlTransaction1

Dim parentId As Integer = CType(sqlCommand1.ExecuteScalar(), Integer)



'

' Do reading test to test concurrently reading table being written to

'

' Create/Open Database Connection for reading test

Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection("Server=GRB-AB;Database=Transaction;Trusted_Connection=True;")

sqlConnection2.Open()

Dim sqlCommand2 As New System.Data.SqlClient.SqlCommand("SELECT Id FROM Parent;", sqlConnection2)

sqlCommand2.ExecuteReader()

Dim i As Integer

While (sqlCommand2.ExecuteReader.Read = True) ' <===== LOCKS UP HERE **************

i = i + 1

End While

'

' End reading test

'



' Set child record

sqlCommand1 = New System.Data.SqlClient.SqlCommand( _

"INSERT INTO Child (Name, ParentId) VALUES ('ChildValue', " & parentId.ToString & ");", sqlConnection1)

sqlCommand1.Transaction = sqlTransaction1

sqlCommand1.ExecuteScalar()

' Either 1.) commit transaction OR 2.) rollback transaction

Dim test As Boolean = False

If test = False Then

sqlTransaction1.Commit()

Else

sqlTransaction1.Rollback()

End If

sqlConnection1.Close()

sqlConnection2.Close()

Catch ex As Exception

exception1 = ex

End Try

End Sub

End Module