3

I have a c# 3.5 framework Windows application that runs against an Oracle DB located on a server.

One of the forms of the application has eight tabs across the top. Within the tab content area of each tab is a combobox. The combobox displays the same information on each form. When the user changes the combobox value using the dropdown or keyboard arrows, then the eight tabbed areas are populated with the data as pulled from Oracle.

Based on the structure of the existing program, every time the combobox is changed, about 20 individual DB connections are opened. First, about 8 are called to save data from the different tabs to their correct table. Contents of each tab are passed to a DB class for saving that tab's data. Second, about 8 DB calls are made to load the tabs, from tables, based on the combobox.

To clarify, it would be like picking a combobox on any tab that changes the model of a car. Each tab then would be stuff like "interior options" "engine options" etc.

Then a couple of DB calls are made to lock the High-level record based on an ID so no one else can edit that particular record at the same time.

The process, overall, is pretty solid. The save/load time are blazing fast. I can switch back and forth between two different combobox values with almost instant data saves/loads.

THEN COMES THE PROBLEM

If I spin back and forth fast enough (which a couple of the users have done as well), the whole program hangs. No crash, just hangs.

Repeating this in the debug environ, I found it was always stopping on the same line of code (a simple recordset assignment ( for example CarModelInterior.Notes = Convert.ToString(myReader[6]);)

Then I found the Garbage Collector (GC) thread was running in the background but was also stopping at the same place each time.

Enter installation of the RED-Gate Memory/Performance monitors.

What I found was that the faster and faster I was switching the combobox values, the faster the GC Finalizer queue was filling up. Ultimately, it appears that the same SQL call was at the top of the list.

Enter my assumptions and guesswork.

My thought is that either there are too many connections opened and not being finalized fast enough or there is a lock going on somewhere.

What I can say is that ALL (each and every freaking one) of my DB calls in the whole program use the "USING" statement so all disposing is completed automatically. Also, ALL (as in yes I checked the whole application), ALL of the DB calls are on the primary thread. Therefore, all the 20-or-so DB calls made for each combobox value change are made in order. This has eliminated the locking possibility at least as far as being a possible single-thread issue.

What do I have left? At this point, so much googling that I've given up and posted here. Is it possible the finalize queue isn't processing fast enough? Any other ideas?

joe
  • 31
  • 2
  • 3
    question is why (and with what objects?) is the finalization queue filling up in the first place? If you are disposing your resources the Dispose call should do a `GC.SuppressFinalize()` which would avoid that problem. – BrokenGlass May 25 '11 at 18:09
  • Broken Glass has made an astute observation. Also, are there any locking primitives that might deadlock on a shared resource? Perhaps, it is in the data access layer (which you fail to mention; which is it?) – sehe May 25 '11 at 18:13
  • All Values listed in the Red Gate program are filtered by "Objects that are not disposed" and all are OracleCommand objects. The one at the top of the list when it hangs (with several Memory snapshots to confirm it's not clearing queue) is a Delete SQL. Because I can reproduce the error and it's single threaded, how could it be a locking error? (innocent question) – joe May 25 '11 at 18:24
  • Also, all statements use: using (myConnection = new OracleConnection(ConnectDB)). So not reason to roll my own dispose. The Connection string to the oracle DB is simply the data source, user id and password. – joe May 25 '11 at 18:26
  • 3
    are you disposing the OracleCommands as well or just the connection? If not, that might be your problem (easiest to use them in a using block). – BrokenGlass May 25 '11 at 18:29
  • I SHOULD CLARIFY: the queue is being cleared if I wait a second and then perform another action. It's those actions, if fast enough, are filling the queue. – joe May 25 '11 at 18:32
  • BrokenGlass : When I look at then, they are like this `OracleConnection myConnection; OracleCommand myCommand; OracleDataReader myReader; string strSQL = ""; try { using (myConnection = new OracleConnection(ConnectDB))` – joe May 25 '11 at 18:33
  • @joe you should try and dispose the command as well when you use it: `using(OracleCommand myCommand=new OracleCommand ()) { }` – BrokenGlass May 25 '11 at 18:36
  • oHHHHHHHH. Head-slap. I was thinking as the commands were associated with the conenction that it would do the associated clean up as part of the connection cleanup. :( Off to make a ton os code changes - glad they are all in one class. – joe May 25 '11 at 18:39
  • The good news - doesn't lock up as quickly and the red gate memory instance list no longer shows a bunch of finalizer queued OracleCommand's. Now only show's the one open object at the time of the hang. One problem...more than one thing that needs fixing. I still consider the using Command solution as a huge help. Hmmmm – joe May 25 '11 at 20:16
  • Are the users using the down arrows to flip between values, maybe provide a different UI method to allow them to select a value. Also if your still having issues after disposing of the command object, see if you can reduce your memory usage, or usage of large objects. If you're burning through memory you might also be fragmenting it and thus your string can't ever find a place to go without more memory. – Josh Jun 03 '11 at 20:21

1 Answers1

1

OracleCommand must be Disposed before the resources can be reclaimed. DbCommand, often the base class for Command objects, implements IDisposable. By comparison, System.Data.SqlClient.SqlCommand does not seem to require disposing, so it can lead developers to forget that many DbCommand implementations do require disposal. If the commands are not disposed, then the garbage collector will eventually release the unmanaged resources by invoking the Finalize method (assuming your Oracle Client's implementation of OracleCommand overrides object.Finalize).

agent-j
  • 27,335
  • 5
  • 52
  • 79