Subject Re: Performance lost with lots of connections
Author Adam
> The replication rarely does anything more complicated than INSERTs
> or Updates.

Hmmm, do you update the SAME record from different transactions,
either directly or indirectly by triggers? If so, do you use WAIT

This would certainly exhibit the behaviour you report. If you have a
wait transaction, then if your transaction attempts an update that is
conflicting with another transaction, your transaction will wait to
hope the other transaction rolls back. If the other transaction takes
a while to commit / rollback, then your transaction will be idle. This
idle time takes next to no CPU, so it is quite possible you have a
queue waiting for a resource.

While I can't suggest an immediate fix, if you are able to set the
transactions to NO WAIT transactions, these will raise their
exceptions immediately. Obviously, long runnings transactions are to
be avoided where possible, especially those that lock updates on
records, and really especially those that lock updates on records
which are frequently updated by other transactions.