--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> 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.

I will check this, I am pretty sure I am using a SNAPSHOT with NO WAIT
when inserting the child record. Most of the time we use SNAPSHOT
transactions, although I can't completely rule out that another
Application or Service working with the same data is not in RC mode.

I know there are some triggers on some tables that set a flag field in
the parent table (FOO in the example) which I will work to remove
(only required for some legacy application).

>
> 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....

I can imagine. My plan at this stage is to attempt to reduce
unnecessary changes to parent tables (about 80% of the time it is
unnecessary), and to develop some retry logic to handle the lock
conflicts and deadlock scenarios.

Thanks.

Adam