ODBC request colliding

[DMAN3021]

Fun problem,

I'm running an automated tests system that spins off threads of a test every 30 seconds or so. I've found that if I let it run for a long time, I can end up having more than one test running at a time, which is good, because in the end, I want many tests running at the same time.

My problem is that everything is logged in a horrible Access Database (ugh), and that it happens from time to time that tests will try to access this database at the same time. Now I get two ressources and end up having crazy cascading errors jumping back at me.

Any ideas...?

Dan...

Some info about the program:
It uses System.Timers.Timer to start a test at every tick (or elapsed), the interval is set to 35000. If I'm not mistaken, this kind of system timer "simulates" thread behavior (or so I believe I have read), and therefore I can get concurrently running tests.

Tests are all hard coded in VB and follow a series of simple commands that take about 30 seconds to run

Every test is logged at the begining, and either at the time of failure or passing. The following is what my logging code looks like:

        Dim SelectCommand, insertCommand As String
        Dim TestID As String
        insertCommand = "INSERT INTO TestResults (MTA, MTB, Type, Result) VALUES ('" & MTA & "', '" & MTB & "', '" & TestType & "', '" & TestResult & "')"
        SelectCommand = "SELECT TestID FROM TestResults ORDER BY testID DESC"
        Try
            OdbcConnection.Open()
            OdbcLogInsert.CommandText = insertCommand
            OdbcLogInsert.ExecuteNonQuery()

            OdbcLogSelect.CommandText = SelectCommand
            TestID = OdbcLogSelect.ExecuteScalar
            OdbcConnection.Close()

        Catch ex As Exception
            reply = MsgBox(ex.ToString, MsgBoxStyle.OKOnly, "Error")
            If OdbcConnection.State <> ConnectionState.Closed Then
                OdbcConnection.Close()
            End If
        End Try

 

[jebenson]

Perhaps instead of each test being responsible for writing its own log to the DB, you could create a "middleman" class that accepts logging requests, and either writes them to the DB (if there is not one already currently being written), or queues the log request until the DB is no longer busy.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[DMAN3021]

Thanks Jeb,

I kind of have that set up. When a test ends, it returns its results, and the results are sent to my application's console. The console logs everything it receives to the database. So you could say the console is the middle man.

You do bring up a good point though, how can the console try to access the database at the same time, when it's the only one that writes to the log...?? I will investigate,

Dan...