I understand what your saying but the important point is that I do
commit the changes (i would assume that if i didnt a new thread would
not see the data and it is running in read commited mode), and I dont
get errors on the new thread as you said I should but the old thread.
I am suspicious that the error message coming from the backend is
confusing and the problem is really that there is a snapshot on the
backend and If i could get A to see the most recent data instead of
its snapshot than the problem might go away.

Is there any way to turn off snapshots on the backend?

--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> clarion55sucks wrote:
> >
> > I am having problems with 'deadlock conficts with concurrent update'
> > errors.
>
> All database access is done in the context of a transaction. Clarion
> and the ODBC driver are doing you a bunch of "favors" by masking
> transaction boundaries and confusing the bejeezus out of everything.
> >
> > What happens is i open a (browse list) thread (A) update a particular
> > record, open another (browse list) thread(B) and update the same
> > record.
>
> That should cause "thread" B to stall on the update, or get an
> error. If you've updated a record in one transaction without
> committing it, no other transaction can update that record -
> the operation is called a dirty-write and is an error at the
> lowest level of transaction semantics.
>
> > Now if i go back to thread A, and do a get (select) on the
> > database, I will get the data that it saved back and not B.
>
> That's OK, though B should, as I said above, either have
> stalled or received an error - an error in your case because
> you've chosen 'no wait'. If all the application layers are using the
> same semantics, 'no wait' should mean that a error is returned
> immediately after a conflict is discovered.
>
> > If I then
> > try and update the record I get the 'deadlock conficts with
> > concurrent update' error. I can keep opening new threads and they are
> > all fine, but if they update a record any previous threads will have
> > the same problem.
>
> Which is the normal behavior of a transaction based system. You can't
> update a record if you can't see the most recent version and you can't
> see uncommitted data.
> >
> > However, if I open A then open B and update the record on B then i can
> > switch between A and B, updating as i please without problem (and
> > getting the data they update).
>
> This is where the "magic" is getting in the way - something is either
> doing an autocommit or caching an update.
> >
> > Closing the thread does not fix the lock that appears to be held on
> > the record ie. If I open A and update, open b than update and close
> > than try and update from a I still get an error.
>
> A is "snapshot" transaction and can not see changes made by concurrent
> transactions, even after they commit. That gives the transaction a
> property called "repeatable read" which many database theorists

consider

> desirable.
> >
> > I assume this is becuase the server appears to have what i'm calling
> > 'thread memory' where even if i do the above and then also shut down
> > thread A and then restart it, it 'remembers' the data giving it the
> > data it say before it shut down and it will still get the deadlock
> > error. (Is this some kind of snapshot 'functionality')
>
> It has nothing to do with thread memory and it's not a bug. A snapshot
> mode transaction gets consistent results - if it can see a row, it will
> continue to see it. If it can't, it never will. A will never see B's
> results.
> >
> > Any ideas on how this behaviour occurs and how I can fix it?
>
> If you don't wasn't read consistency, use only "read committed"
> transactions. If you don't have control over the transaction type,
> follow the rules for programming with consistent transactions - if you
> get a lock conflict, rollback and start a new transaction that will

have

> a consistent view that includes the committed state of the record you
> want to modify.
> >
>
> Regards,
>
>
> Ann
> >