At 09:37 25/09/2008, you wrote:

>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>> >As far as I can tell, there are no open transactions when the hang
>> >occurs. isc_start_transaction is called with this TPB:
>> >
>> > static char transParamBuf[] =
>> > {
>> > isc_tpb_version3, // Version, always 3
>> > isc_tpb_write, // Read-write access
>> > isc_tpb_concurrency, // Concurrency-mode transaction
>> > isc_tpb_read_committed, // High throughput, high
>concurrency transaction
>> > isc_tpb_wait // Wait on lock
>> > };
>> >
>> >The "wait on lock" looks suspicious, but where could a lock be held
>> >except in another transaction?
>>
>> Well, at least one has to wonder why a wait transaction is used in a
>single-user scenario....
>
>Multiple threads? Altho in this particular situation there is only
>one. In which case, the wait shouldn't matter, since it won't happen.
>
>
>> but I'd be more dubious about a TPB that tries to start with two
>isolation levels!!
>
>What values would be more appropriate here?

Well, "appropriate" depends on your restrictions. Use just ONE isolation level, since the alternatives are mutually exclusive. I'm not sure whether you are getting the first or the second one in this case. IMO it's a bug if you are actually allowed to pass such a TPB without getting an exception.

Don't specify WAIT if work has to be serialised. Actually, WAIT is really only applicable to very specific circumstances, generally concerning an INSERT operation. Waiting updates and deletes will ultimately throw a lock conflict in nearly all cases and it's impossible to predict livelocks or the deadly embrace.

Note that WAIT has variable effects with READ COMMITTED isolation, depending on [NO_]RECORD_VERSION.

Basically if you don't understand the effects of WAIT, don't use it. People sometimes use it in the false belief that it's a way to avoid having to handle lock conflicts. It rarely wins anything other than to allow an INSERT to proceed eventually under specific conditions. NO WAIT (the default) allows lock conflict exceptions to occur immediately and be managed in whatever way your requirements demand. Don't overlook the possibility that you have some code around there that is "swallowing" lock conflict exceptions instead of handling them...

It seems from your postings that you are working with code you inherited, rather than code you wrote yourself. Direct API programming is rarely used these days but the resident expert on it is Ivan Prenosil. Right now Ivan is in Italy for the Firebird Conference, so you might like to wait a few days to get hold of a clear approach to take.

I think that having a single application that is connecting and disconnecting from the same two databases constantly in short order seems bizarre. I could see it throwing the lock manager into a state of chaos...is this really production code or is it some kind of rigour testing?

./heLen