Subject Re: [IBO] lock conflict on no wait transaction
Author Helen Borrie
At 08:55 AM 5/10/2005 +0000, you wrote:
>ISC ERROR CODE:335544345
>lock conflict on no wait transaction
>update conflicts with concurrent update
>TIB_Statement: "dmHost_2.DSModule.tblWebUser.<TIBOInternalDataset>.<T
>What triggers this error? I did not do any concurrent updates, in
>fact, I have a critical section that ensures all the threads that
>perform updates are serialized.

Nope. A critical section can't "serialize" updates.

>My transaction component is set to autocommit = false, but I call
>Commit explicitly every time after an update is posted.
>The weird part is, I have 4 threads running, all doing the same
>thing but updating different records in the same table (again, they
>are serialized when they perform updates) and only one thread
>faulted with this error, the rest continued without any problems.

Firebird does not support serialized updates. Anything like "priority" is
determined by the relative ages of the transactions and a mix of other
parameters: transaction isolation, the RECORD_VERSION setting (for
transactions in ReadCommitted isolation) and the locking policy (WAIT or NO
WAIT). The only thing we know about your transactions is what you report
from the error message: that at least one of them is using the NO WAIT
locking policy.

>Is this a firebird bug, or an IBObject bug?

It's neither. It just needs a bit more understanding on your part of how
transactions work. With a NO WAIT transaction, an update request will
return a lock conflict immediately if conflict conditions exist. There are
several conditions that could cause conflicts (and NOTE WELL, a lock
conflict is never a bug!!). For example, if a NO WAIT transaction goes to
update a record that has already been updated by another transaction since
this transaction began, and the isolation level is CONCURRENCY; or is READ
COMMITTED with RECORD_VERSION and this transaction is newer than the one
that has an update pending, then you will get a lock conflict.

I reiterate - a lock conflict is NOT a bug. You need to work through your
requirements and the various transaction parameters and sort out what you
need to set up to fit them. A truth table would be useful for this.

Of course, there will be bugs if you write application code that doesn't
catch and deal with lock conflicts...another thing to consider is whether
multi-threading a task that targets the same records repeatedly for writes
actually makes sense, since the failure level will be high with NO WAIT and
hung and deadlocked threads will be common with WAIT.