Multi-threaded woes

[joshmouch]

Hello,

Sorry to post this three times, but I think I got the code pasted right this time.

I just purchased MysqlDirect.Net recently, and am having a hard time trying to get it to work in a multi-threaded environment (an asp.net site).

The problem is that I get a lot of expections like this:

"Net packets out of order: received[0], expected[3]"

....whenever two pages try to query the database at once (mostly SELECT's).

I am doing my own connection pooling on top of the connection pooling for reasons I don't need to get into. My logic is that when a connection is requested, check the available connection objects in memory. If any of them have a state of open or close, then they are available to be used. Is this correct? This is the only thing I could think would be causing the problem.

Here is the code to get a connection. I should note that this exact same code doesn't throw exceptions with the Mysql ADO.Net Connector provided by MySql AB (still in beta).

Code: Select all

Private Shared _db As New Generic.Collection(Of MysqlConnection)
Public Shared Property DatabaseConnection() As MysqlConnection
    Get
        Dim db As MysqlConnection

            Dim connectionFound As Boolean = False
            For Each tdb As MysqlConnection In _db
                If tdb.State = ConnectionState.Open Or _
                    tdb.State = ConnectionState.Closed Then

                    Dim m As New Threading.Mutex()
                    m.WaitOne()
                    If tdb.State = ConnectionState.Open Or _
                        tdb.Connection.State = ConnectionState.Closed Then
                        ' Dispose of unused connections immediated
                        If connectionFound Then
                            _db.Remove(tdb)
                            tdb.Dispose()
                        Else
                            db = tdb
                            connectionFound = True
                            If Not db.State = ConnectionState.Open Then db.Open()
                        End If
                    End If

                    m.ReleaseMutex()
                End If
            Next

            If db Is Nothing Then
                db = New MysqlConnection
                db.Open()
                _db.Add(db)
            End If
            Return db
        End Get
        Set(ByVal value As MysqlConnection)
            _db.Add(value)
        End Set
    End Property

[/code]

[Oleg]

You cannot execute commands asynchronous at the same connection.
You should change your code approximately so that it will work:

Code: Select all

  Private Shared _db As New ArrayList()
  Private Shared _m As System.Threading.Mutex = New System.Threading.Mutex()
  REM Private Shared mSet As System.Threading.Mutex = New System.Threading.Mutex()

  Public Shared Property DatabaseConnection() As MySqlConnection
    Get

      _m.WaitOne()
      Try
        Dim db As MySqlConnection

        Dim connectionFound As Boolean = False
        Dim tdb As MySqlConnection
        For Each tdb In _db
          If connectionFound Then
            _db.Remove(tdb)
            tdb.Dispose()
          Else
            db = tdb
            connectionFound = True
            If Not db.State = ConnectionState.Open Then db.Open()
          End If
        Next

        If db Is Nothing Then
          db = New MySqlConnection()
          db.ConnectionString = "User Id=root;host=server;Database=test"
          db.Open()
          _db.Add(db)
        End If

        Return db

      Finally
        _m.ReleaseMutex()
      End Try
    End Get

    Set(ByVal value As MySqlConnection)
      _m.WaitOne()
      Try
        _db.Add(value)
      Finally
        _m.ReleaseMutex()
      End Try
    End Set

  End Property

  Private Shared Sub MyThreadProc()
    Dim i As Integer
    Dim connection As MySqlConnection
    Dim command As MySqlCommand = New MySqlCommand("select * from emp")
    For i = 1 To 1000
      connection = DatabaseConnection
      command.Connection = connection
      command.ExecuteReader()
      connection.Close()
      connection = Nothing
    Next i
  End Sub

  Private Sub test()
    Dim i As Integer

    For i = 1 To 10
      Dim connection As MySqlConnection = New MySqlConnection("User Id=root;host=server;Database=test")
      _db.Add(connection)
    Next i

    For i = 1 To 10000
      Dim myThread As New System.Threading.Thread(AddressOf MyThreadProc)
      myThread.Name = String.Format("Thread{0}", i)
      myThread.Start()
    Next i
  End Sub