Subject Re: [firebird-support] Re: VB.Net select query hangs
Author Helen Borrie
At 09:52 AM 4/03/2009, you wrote:
>Let be totally clear, some of what are saying to me is absolutely Greek. I appreciate you trying to help, but if you could dumb down some of your descriptions, or at least tell me where to look/what to do, that would be great.

Being "dumb" is one thing, insisting on staying that way is quite another. Please understand the time limitations on volunteers - we have jobs to do, too. Some of us actually earn our living as consultants for customers for whom not knowing how our multi-user database engine works is a matter of policy, or learning about it is an imperative. In our answers, latch on to clues and don't ask us to repeat what we already said.

Since you've already missed some pointers to where to look/what to do, can I suggest you print out my earlier lengthy reply, highlight places where suggestions are made and go digging.

>The legacy app is constantly running. It is an automated dialer which retrieves data from remote locations and stores into database. It runs every day, all day long, with different times for dialing for different remote locations. There is no way of re-tooling the code for the legacy application as my company did not get all of the source code from the original developer, and what code we do have has little to no documentation in order to follow the flow (was written in Delphi in mid 90's). It doesn't make sense to me that the legacy application appears to be unphased by whatever is locking my new applications up!!! ??? Could it be that the old ODBC connectivity was more robust than the new .Net connectivity? Food for thought...

If the notion that the activity of one application executing operations on the database could affect the activity of other applications that operate on the same database doesn't make sense to you, then you do indeed have a knowledge gap that needs filling.

Fundamental to your understanding is that a connection between an attached client application and the database, via the database engine, is *all* about transactions. Everything happens in transactions. Applications start them and applications end them. Transactions that are configured never to end, or to cause lengthy blocking locks, are going to cause hang-ups and other undesirable effects such as garbage that never gets cleaned out.

Transactions are totally driven by client applications, so understanding the effects of the various transaction attributes you set in your application code is *everything*. Use the firebird-net-provider list to get answers about what you're doing in your .NET applications.

And using an extremely old ODBC driver in your legacy app, with recent versions of Firebird, could indeed cause great grief -- above and beyond the effects of the typical choice of soooo many Delphi developers over the years to totally avoid understanding the effects of blindly taking driver defaults.

OTOH, you should be able to upgrade the ODBC driver and configure it properly, without having to hit the application source code (touch wood!). Use the firebird-odbc-devel list and its resources to verify any driver configuration details that you don't understand.

>You say to disable Guardian. Do I do this by stopping the service, or is there another way?

Re-read the original posting. I told you how. The program is instsvc.exe, which is in your bin directory.

>Where do I find out if I am running Classic or SuperServer?

The executable name. Examine the service properties. If the executable is called fb_inet_server.exe then you are running Classic. SS is fbserver.exe.

>Yes, my patchwork apps are just as you described. They connect, do their business, and disconnect from the Firebird DB. Most of the time they are simply data retrieval applications for custom reports, spreadsheets, etc. Only one app actually inserts data into the DB.

But your legacy dialler app is inserting constantly. Under several transaction conditions, uncommitted inserts cause selects on the affected tables to be blocked. A WAIT transaction will just wait until conditions can be tested for "rightness" (resulting finally in a locking exception or success); a NO WAIT transaction will return a lock exception immediately.

>Page size shows 16384 in DB Properties. If needed, where do I change this setting?

You would have to back up the database with gbak -b and then create it all-new, using gbak -create_database with a smaller page size. Re-read that and write it on the back of your hand. Never use the - replace_database switch as it will overwrite your live database and your scenario very strongly counter-advises going there. Again, RTFM.

First understand why (whoever) created this database with the maximum possible page size and whether you really need it. Amongst other things, this will require the ability to estimate the record size of your most frequently accessed large record...and PAY ATTENTION to my comments in previous replies regarding the cache size (buffers * page_size) and how important it is to get it right for the server model (Classic/Superserver).

>Dimitry Sibiryakov: Thank you for participating in my little game of "Let's Find the Obscure DB Problem". Could you explain "changing transaction isolation" for me?

In the meantime, google "Firebird" "transaction isolation". There are dozens, if not hundreds of postings over the years on this subject. But also take yourself to the firebird-net-provider list and find out how the .NET driver implements all of the transaction attributes (of which isolation level is but one). Ask about WAIT/NOWAIT, Record Version/No Record Version as well.