OleDbConnection.Close(), System.Threadin.Timer and DeadLock

  • Thread starter Thread starter Daniel Bello Urizarri
  • Start date Start date
D

Daniel Bello Urizarri

Hello:

I'm creating and opening an OleDbConnection on the main thread of my
application, but I need that to close connection with a timer.
When the method connection.Close() is called on the timer method, the thread
is locked and the next code line is never executed.

Why a connection can't be closed from another thread? Is this a bug? If so..
is documented?

With SqlConnection everything works ok.
 
Hi Daniel,

In first place, why the heck you need to close connection with timer?
The other thing is, that connection class isn't thread safe - that means you
should take care of the issue.
However, your problem is probably the design (why timer?)
 
Hello Miha:

I need to keep the connection open, because the application usually will
send many sql sentences one after the other. With sqlserver, openning and
clossing connections does not iplies extra time, but with oledb using an
access database it does. So, i open the connection, perform the query and
start closing the connection with a timer (5 seconds). If some new query
arrives, the timer is reseted, and the connection reused.
The problem is that when the timer is finally executed, the application
stops in the invocation to the Close method, wich is in a critical section
that uses the same monitor that is used to open connections and the
application deadloks.

Maybe I should use a single-thread timer, but i dont like this, because im
developing a dll, that could be used without windowsforms, and im not sure
those timer will work without a message queue.. i will try it now.

Saludos.
 
So, the trick is to wait until the connection is fully closed before trying
to reopen it, right? Seems like you can accomplish this by keeping OLEDB
Commands in a collection. The thread that builds and stores commands would
be asynchronous to the one that issues them to the DB. When the collection
is empty, the thread that issues commands would close its connection. The
thread that stores commands would raise an event when it starts adding
commands again (maybe only on the first one). The DB issuing thread would
have a handler for that event that would reopen the connection and start
issuing commands again. This would totally eliminate the timer - and the 5
second delays - and keep the connection object in a single thread.

It's sort of a home-grown queue - but maybe you should be looking at a MSMQ
solution. Also, maybe you should be considering a real DBMS.

Good Luck
 
Hello Rich:

I was thinking in something like that but a litle bit different beacause of
the need of syncronism. The Idea is the same, but waiting until the query
ends in the auxiliar thread.

The thread creates the connection, open it, execute the commands and close
it if five seconds after the last query is executed, reseting this time
every time a query arrives.

This way, like in your solution, the connection object will be on the same
thread all the time, so the problem must dissapear.

Thank you very much..
 
Back
Top