CODEWITHSUNDEEP
sql-servervb.net
HypeZ
HypeZ

Reputation: 4127

Open connection while another connection is open

i need to call a function that do some query while another connection is opened and its doing a transaction.

Ok i get this is weird, here some code:

Main part:

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Dim command As SqlCommand = connection.CreateCommand()
    Dim transaction As SqlTransaction
    transaction = connection.BeginTransaction("myTransaction")
    command.Connection = connection
    command.Transaction = transaction
    command.CommandText = sSQL
    Try
        command.ExecuteNonQuery()
        Dim functionResult As String = myFunction(param1, param2)
        If functionResult <> "" Then
            'error! i need to rollback the first query done here!
            transaction.Rollback()
        else
            transaction.Commit()
        End If
    Catch ex As Exception
        transaction.Rollback()
    End Try
    End If
End Using

myFunction do lot of stuff, and a lot of querys. Every query needs to reopen connection (without transaction this time) but everytime i try to execute the first query inside my function i got timeout error from database (after 30 seconds).

I know i can do this work "copy-pasting" all the myFunction code inside that already opened connection and using the already opened connection, but i use that function more than once and i don't want to mess up my code.

How can i solve this?

edit for more information: that was an already reduced version of the code i'm using, but here a reduced version on what "myFunction" do:

Dim connectionString As String = "my connection string"
Dim queryString As String = "SELECT id FROM foo WHERE param1 = @myValue"
Dim ds As DataSet = New DataSet()
Try
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()
        command.CommandText = queryString
        command.Parameters.Add("@myValue", SqlDbType.Int).Value = 10
        Dim adapter As New SqlDataAdapter()
        adapter.SelectCommand = command
        adapter.Fill(ds, "randomName")
        If ds.Tables("randomName").Rows.Count < 0 Then
            'error!
            connection.Close()
            Return "error"
        End If
    End Using
Catch ex As Exception
    Return "Database error - " & ex.Message
End Try

The code execution (even in debug) freeze on the adapter.Fill(ds, "randomName") command for 30 seconds, after that i get a timout error

Upvotes: 1

Views: 1316

Answers (1)

Victor Zakharov
Victor Zakharov

Reputation: 26454

You can use as many connections as you want, just make sure they don't interfere with each other. SQL server is very diligent about preserving data integrity, so if one uncommitted transaction conflicts with another uncommitted transaction, you get a deadlock.

You may want to play with transaction isolation level, default is READ COMMITTED for SQL server, try to set it to READ UNCOMMITTED. Please read the docs to be aware of the consequences.

From the above link:

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

  • The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
  • The SNAPSHOT isolation level.

Upvotes: 3

Related Questions