Subject Re: lock conflict on no wait transaction
Author zachs78
--- In, Helen Borrie <helebor@t...> wrote:
> At 08:55 AM 5/10/2005 +0000, you wrote:
> >ISC ERROR CODE:335544345
> >
> >lock conflict on no wait transaction
> >deadlock
> >update conflicts with concurrent update
> >
> >TIB_Statement: "dmHost_2.DSModule.tblWebUser.<TIBOInternalDataset>.<T
> >IB_UpdateSQL>.<TIB_Statement>."
> >
> >
> >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.

Ok let me rephrase that. I used a critical section to serialize
transactions that perform 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). The only thing we know about your transactions is what you
> from the error message: that at least one of them is using the NO WAIT
> locking policy.

All of them are using the NO WAIT locking policy. First of all, there
is no outstanding transaction when I encounter the error. Every single
post() is followed by a commit().

> >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 should not be a conflict:
consider the following psuedo code:

enter critical section
leave critical section

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
> this transaction began, and the isolation level is CONCURRENCY; or
> COMMITTED with RECORD_VERSION and this transaction is newer than the
> that has an update pending, then you will get a lock conflict.

After each post, a commit is issued, before another cycle of edit() /
post() / commit() (hence the what I termed serialization in the
previous mail). There's no pending updates, nor is there any newer or
older transaction.

> I reiterate - a lock conflict is NOT a bug. You need to work through
> 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.

A lock conflict CAN be a bug, if it doesn't work as it should (i.e.
locking the record when it should be locked, for example, a
transaction has modified the record but has not been committed and
another one tries to modify it).

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

I never mentioned the app was writing to the same record -- in fact, I
specifically state that only one thread writes to the same record
everytime -- i.e.

thread 1 -- enter CS, insert record A, commit, leave CS, enter CS,
delete record A, commit, leave CS, loop
thread 2 -- enter CS, insert record B, commit, leave CS, enter CS,
delete record B, commit, leave CS, loop
... --> same goes for thread 3 and 4, but updating record C and D

I'm not seeing a conflict there, even if firebird doesn't support
optimistic locking. Every update transaction is performed in a
critical section -- i.e. only one thread performs update transaction
at one time.

Should I expect a conflict in such a case?