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

>