Norm,

>>> And this on insertion of new support incidents:
>>>
>>> IF (NEW.ID IS NULL) THEN
>>> NEW.ID = GEN_ID(GEN_CUSTOMERINCIDENTS_ID,0);
>>>

...

>
> 1. There will be an ID present when the record is INSERTed.

That may be true, but the primary key index will reject a null primary key, so
a record with no ID will never be inserted.

>
> 2. The ID will be "in sequence" with the application, so no chance of a
> duplicate ID being used.

That depends entirely on how keys are generated in the normal case. Unless
they always use the same generator, that trigger could easily create duplicates.

>


>>> BTW, I use no_wait for all my transactions, so I usually get
>>> a deadlock exception rather than a hang.
> What does the application do when it gets a deadlock exception? Does it
> rollback and try again? Is it possible that more than two transactions
> are involved and the deadlock is actually, a deadly embrace?

The reason I recommend using WAIT is that if there is a conflicting
update, you change is not going to succeed until the transaction
that created the conflict commits and you start a new transaction.
With NO_WAIT, you can happily burn cycles by rolling back and
retrying over and over and over, but you'll just get the error until the
other transaction completes. With WAIT, you may have the appearance
of a hang, but at least you're not burning cycles and blocking other
useful work.

>
>>> If it was just a deadlock, surely I should be able to at least get to
> the
>>> monitoring tables? And into other tables in the database that are not
>>> locked up?
> I would assume this to be the case. But as you said earlier, you are on
> 2.5 and there are a couple of "complaints" about 2.5 on this list at the
> moment. If there is a 2.5 problem, maybe you are hitting it.
>

It may be a 2.5 issue, or it may be a huge number of unsuccessful retries.

Good luck,

Ann