At 08:36 PM 4/12/2007, you wrote:

>There's no error returned. Everything just freezes until the lock
>disappears. What happens in practice is that my application itself
>freezes (because this part is single-threaded) and remains unresponsive
>for anything between a few seconds and multiple minutes. Of course,
>users being rather impatient they occasionally hard-reset the
>application and then restart it and get the same problem after which I
>suspect they are waiting on their previous transaction. This only
>happens occasionally and normally the action takes less than a second.
>
>I should have mentioned the foreign keys indeed. There's two, to two
>different tables linked by the LOADGUID (to table LOADS) and INCPALLET
>(to table INCPALLETS) fields. The first one is set to CASCADE and the
>second one to SET NULL. The Loads table is quite heavily read and
>written to. Do you think this is what causes the locks (and if so, how
>can I prevent write actions to LOADS from locking INSERTS to ITEMS)?

You can't - it's intentionally the nature of the beast. Remember, all updates are row-level so, if you have the "parent" of a FK relationship tied up in an uncommitted update, the INSERT is disallowed, in case the change to the parent record alters the PK.

To avoid the long delays, don't use WAIT as the lock resolution setting for transactions in which these inserts are performed. Instead, let the exception be thrown, intercept it and ask the user to try again later. (Or, if you know how, queue the data and implement a retry loop.)

>Is it possible to find out what transaction/statement is blocking this
>one?

No. It wouldn't help, anyway, since you don't know who owns the transactions. And, if you need to have master table accesses causing bottlenecks like this, make sure you don't allow applications to hold on to uncommitted work on these master tables for long periods.

./heLen