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

>
> At 11:45 1/10/2008, Adam wrote:

Helen,

Please excuse me here as this has potential ramifications for some of
my development as well.

> >I was on the understanding that a lock conflict was a conflict between
> >two statements attempting to modify a single record,
>
> No, two or more *transactions* attempting to modify a single record

Of course two transactions (that was meant to be implied), but not
just any two transactions. I mean if you start a transaction and only
perform a simple select on a table, there is 0% chance of a lock
conflict with me.

My point is that it is the statements or DML that causes the lock
conflict rather than the transaction starting or ending (obviously a
transaction commit can end the wait of another conflicting transaction
with a lock conflict exception).

>
> > something only
> >possible during an update or delete (or by indirectly causing an
> >update or delete inside a stored procedure or trigger).
>
> ..or an insert.

Yes, an insert can cause a lock conflict if the target of a foreign
key constraint is updated. In fact Firebird has to be even more
pessimistic here because while it can see whether the target has been
updated, it currently has no way of knowing whether it was only the
other fields that have changed.

>
>
> >I can't otherwise imagine why a commit would ever be waiting, unless
> >the OP is using a TRANSACTION COMMIT trigger that is attempting to
> >perform an update or delete (either directly or indirectly via
> >triggers or stored procedures). In this case, the reported scenario
> >makes perfect sense, the transaction has a lock conflict to contend
> >with and the OP has asked it to wait for the other transaction to
> >commit or rollback first.
>
> If the OP is using a WAIT transaction with Autocommit then any

potential conflict will neither commit nor except when the COMMIT
request is dispatched. Exactly what happens once the conflicting
transaction commits (= posts and commits in IBX Autocommit terms)
depends on other transaction settings (isolation and also RecVersion
isoation is ReadCommitted).

But again, IIRC, Firebird does not have autocommit. In other words,
the client layer (IBX) would need to emulate autocommit by wrapping
every call with something like


procedure RunSQL(SQL : String);
begin
StartTransaction;
try
InternalExecuteSQL(SQL);
CommitTransaction;
except
RollbackTransaction;
raise;
end
end;

Obviously it would be more intelligent than the above, but the idea is
that the transactional nature of the DBMS is dumbed down (hence it
should be discouraged).

I would expect that a lock conflict can only occur inside
InternalExecuteSQL, and the delay being reported is coming from
CommitTransaction.

If you have multiple ad hoc threads all hitting the same records all
using WAIT you have a pretty high potential for deadlock conditions.
With No Wait, a deadlock would except immediately and the application
could resolve it. With WAIT, a deadlock readily becomes a
livelock/deadly embrace: competing threads are all waiting and none
can extricate itself.

Yes. WAIT transactions are more vulnerable to deadlocks. I don't use
them too often because (up until recently) there was no timeout
parameter, and I don't enjoy putting my applications' stability in the
hands of other development I have no control over.

Adam