At 10:35 AM 16/05/2007, you wrote:

>Hello Group,
>
>Can someone please explain to me the difference between the following
>exceptions raised by Firebird?
>
>---
>lock conflict on no wait transaction
>violation of FOREIGN KEY constraint "FK_BAR" on table "FOO"

This is two exceptions, probably related. The first comes from not
being able to insert or update due to another (older) transaction
having an update or insert pending on either the master record or a
dependent record of the two tables in the relationship. Updates
affect specific records; inserts to the master affect the table
itself. You'll see this exception when the transaction is in Read
Committed isolation with NO WAIT lock resolution.

The second exception is obvious...

>and
>
>---
>deadlock
>update conflicts with concurrent update

It's not necessarily a deadlock, since the higher level exception
reports deadlock for any lock conflict. Another transaction has
either an update or delete pending that conflicts with your request
or whose database snapshot would be made invalid by your insert.

>I know that the parent record exists for the first case (unless my
>problem is deeper than I suspect). Is it complaining rather about the
>fact that a different transaction may be simultaneously updating the
>parent record (although definately not touching the primary key field)?

The engine locks records to prevent database state becoming
inconsistent with the view and transaction settings of any active
transaction. There's not a simple one-liner that can cover every
possible scenario. I recall during the development of the Book
spending days trying to draw up a truth table for every possible
combination. When you take into account foreign key dependencies, it
is not a trivial exercise. I abandoned it eventually, as my brain
began to curdle. With Read Committed and [No] Record Version in the
picture, a person could go crazy trying to work out all of the
possibilities....

./heLen