Losing database connectivity

[TonyV]

My application is now running in the field on some devices as part of our beta testing process. Apparently, the application is losing connection to the database, which is weird, as it's running locally. In any event, here's the stack trace we're getting:

Code: Select all

2012-03-01 16:29:06,968 [PubSubBufferMemory::RunSecondaryInput] ERROR PubSubBufferMemory::PubSubBufferMemory [(null)] - PubSubBufferMemory::RunSecondaryInput failed while Running: System.Exception: Secondary buffer has failed. Data is being flow controled until it has recovered. ---> System.InvalidOperationException: Connection must be opened.
   at Devart.Common.Utils.CheckConnectionOpen(IDbConnection connection)
   at Devart.Data.PostgreSql.PgSqlConnection.Commit()
   at Devart.Data.PostgreSql.ag.ag()
   at Devart.Common.DbConnectionBase.Close()
   at Devart.Common.DbConnectionBase.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()
   at System.Data.EntityClient.EntityConnection.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()
   at System.Data.Objects.ObjectContext.Dispose(Boolean disposing)
   at System.Data.Objects.ObjectContext.Dispose()
   at LPRCore.Devices.PubSubBufferPostgres.PutMessages(BufferedNode subscriber, IEnumerable`1 message, CancellationToken cancel)
   at LPRCore.Devices.PubSubBufferMemory.RunSecondaryInput()
   --- End of inner exception stack trace ---

The code in PubSubBufferPostgres.PutMessages is structured as follows:

1. An Entity Context is instantiated inside of a using statement
2. If the Context's connection is not open, it is opened.
3. A DbTransaction is started by calling context.Connection.BeginTransaction().
4. Data processing occurs inside of a try-catch block
5. If an exception occurs, the transaction is rolled back inside the catch block.
6. If no exception occurs, the transaction is committed.
7. The end of the using statement is here.

As the code is running in a release build on a remote machine, I don't know which line the error occurred at. It seems that the Commit is occurring after the Entity context was removed from memory. I can't imagine a scenario in which that could happen. Can you provide any insite into how this might happen and how I can code to correct this situation? What would happen if connectivity to the database were somehow lost? How would I detect it? What would be the correct procedure for handling that case?

Tony

[TonyV]

It turns out that the problem has not recurred since I reported it. I think the issue had to do with my not closing the transaction objects. I didn't put the call to connection.BeginTransaction() in a using statement.

We've placed all calls to BeginTransaction in a using statement and now we're not losing connection any more. I believe the unclosed transaction caused the server to use up the 100 connections in the connection pool and the database crashed.

Tony