Subject Re: Performance lost with lots of connections
Author Adam
> Yes, defenitely it is possible for a long running transaction to
> lock a record. Still, how does the WAIT work? Does it wait for say
> 10 seconds, then try again, then wait another 10 seconds and so on
> forever? Or does it stop after a certain number of attempts? This is
> really important, it may be the reason the entire system sometimes
> completely stops :(

The wait remains in place until the conflicting transaction commits or
rolls back. If the conflicting transaction is say waiting for a user
to click something on a dialog and that user is out at lunch, then the
you will be waiting an awful long time.

There is development in the pipeline to allow you define a timeout for
the WAIT, but for now you need to either ensure your transactions that
hold those locks are not running for extended periods of time, or you
need to change to NO-WAIT transactions and put wait-then-retry logic
into your application.