Locked DataBase

[Limor Janah]

Hi
I am using this code for getting data from database

mCN = New OleDb.OleDbConnection(sConnStr)
mCN.Open()
DtSet = New DataSet()
Cmd = New OleDb.OleDbDataAdapter(SQLStr, mCN)
Cmd.Fill(DtSet, TableName)

But I have a problem.
After a very few queries the data base gets locked
and no one can access the site. All the queries/functions raises errors.
On the machine that has the IIS I get a message:
this data base is opened exclusively by… (the user that logged on this computer (the IIS))
If I wait, then it's releases and I can continue working for another 2-4 queries.

Any suggestions?

Limor

[hellomadhu]

check if u have closed all the connections u have opened with the database.

[Limor Janah]

Yes, I closed all the opened connections.

[Brian Pregler]

this almost sounds like a hardware problem to me. It is taking time for the machine to close the connections maybe??

regards BP

[hellomadhu]

i faced the same problem once when working with jsp & mysql.
when i checked the processes running on the machine, i realised lot of active connections.

but when i checked my app i found a connection remaining unclosed and that resolved the conflict.

i suggest u to examine the processes running at the system when the problem occurs.

[Limor Janah]

Thanks I will

[Limor Janah]

How do i check the processes that running on my machine?
with Task Manager?
and how does a connection looks there?

[hellomadhu]

sorry ya, i too dont know how a connection looks like in the list of processes. in my case,it was linux os and i was easily able to list out the processes.

i'll check out ur case (i guess it's NT or 2000) and get back to u.

[V. Lorenzo]

Hi:

The .Net framework uses a method called "connection pooling" to speed up the process for opening connections. As a matter or fact, what it does is to keep several connections open, even if you explicitely tell "MyConnectionObject.Close()", so when you call the Connection.Open() method, if the connection string given to the new ConnectionObject matches the connection string for a connection that is already open, it just returns that connection, otherwise opens a new connection. That's whay it takes so long to open the first connection but the second, third, and so on are so fast to open.

There is something else here to take into account, the non-deterministic behaviour of the garbage collector. If you are finished with a connectionobject (e.g. it gets out of scope) and you don't explicitly call the Close() method (which is automatically called when the object is collected), it will take some time before the GC makes it's cleaning job and the connection is closed.

[hellomadhu]

the process aspnet_wp.exe is found in the task manager when the connection is opened. yes, as lorenzo had said i found it automaticaly gets closed even when the user doesnt explicitly close the connection.

lets come back to ur issue, did u test it on another machine ?

[Limor Janah]

what i did was an object that handles all the work with the database.
so in the code, i define an instance of that object, i call his init func
that opens the connection to the database..
on the distractor i put the myObj.close()
and i thought i cover it.
after all the problems, i wrote for this class a close function
that closes the connection.
i went by all the code and called the close function of the object.
and now it's ok. The problem solved.
i wanted to be sure by checking the processes for any other active connections.

I want to thank you very much for all the help
you have helped me a lot.
you are the best

limor

[V. Lorenzo]

Hi:

The concept of "Desctructor" may not be applied for a manage class. Managed classes don't have destructors, but "finalizers". In facts, we are not allowed to call directly the "Finalize()" method for a managed class object.

What is a little bit confusing about this is that we define a custom finalizer with the same sintax that is used in C++ for destructors. Finalizers a meant (most of the time) for freeing unmanaged resources.

If your DB provider supports only a limited number of open connections, you should consider using some other, more deterministic, method for closing the open connections after done with them.