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)?



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



Regards,



Mathias



________________________________

Van: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Namens Helen Borrie
Verzonden: dinsdag 4 december 2007 10:09
Aan: firebird-support@yahoogroups.com
Onderwerp: Re: [firebird-support] Strange Lock



At 07:39 PM 4/12/2007, you wrote:

>Greetings all,
>
>I have a strange issue where it seems that a simple INSERT statement is
>getting stuck behind locks. The statement in question is as follows:
>
>
>INSERT INTO ITEMS (GUID, LOADGUID, RFE, QTY, INCPALLET, CISTATUS,
>PRODUCTID)
>
>VALUES (:GUID, :LOADGUID, :RFE, :QTY, :INCPALLET, :STATUS, :PRODUCTID)
>
> There's one Before Insert trigger on the table, but the only thing

that

>does is:
>
>If (new.GUID is null) then new.GUID = gen_uuid();
>
> This is the only statement in the transaction and the transaction has
>concurrent mode without auto-commit or auto-undo.
>
>Could anybody tell me what might be locking this statement and what I
>could do to prevent this?

It would help if you would provide the actual error that is returned.

Any foreign keys involved?

./heLen


________________________________________________________________________
______________ This inbound message to KPN has been checked for all
known viruses by KPN MailScan, powered by MessageLabs. For further
information visit: http://www.kpn.com/kpn/show/id=1183952
________________________________________________________________________
______________



[Non-text portions of this message have been removed]