Subject Re: [ib-support] Deadlock and wait
Author Ann W. Harrison
At 11:27 AM 7/26/2001 -0500, Woody wrote:

>I use IBX so anytime a commit is done it closes my view of the data
>(tables, queries, etc.). So, unless I switch and use some type of
>client-side dataset, I can't commit the transaction until the user
>moves to the next (or another) record.

That's my understanding. From a database point of view, that's
backward. The last thing one wants is to have readers holding
resources they aren't using.

Robert F. Tulloch wrote:

> CommitRetaining()

Commit retaining solves neither problem. It doesn't release resources
and, in the case of a consistent transaction, doesn't update the
transaction's view of data.

>Changing data on a form doesn't change the data on the server so it's
>really not in an inconsistent state as far as the server is concerned. It's
>only when I actually "post" the data that it becomes a new version of the
>data, right?

Right. In other systems, I've seen this problem handled like this:

Start transaction
Read data
User looks at data
User changes data
System saves old version of data
User posts changes
Start transaction
Compare old version with current database version of data
If equal
Perform update
Notify user of changes

>So, as long as I commit after posting changes, I should be OK.

More or less OK, except that you've been holding resources you're
not using.

>I rarely use update/delete queries to update data directly on the server
>except for certain mundane tasks that are quickly executed and committed. I
>always commit after any posting of data changes so the only "long" running
>transactions (generally no more than 10 or 15 minutes) are read states which
>shouldn't affect anything.

Yes. Read transactions, including read committed read transactions hold

>I guess my biggest confusion comes in because I use all data-aware controls
>and don't store the data in some form and then use an update query to change
>it. So, I assume that the biggest problem that I face is when user A reads a
>record and user B reads the same record. Then, user A changes the record and
>commits and user B changes the record and commits, right? In this case, user
>B gets a dealock error and rolls back their changes.

Right. Another of the characteristics of a transaction is that it
won't blindly overwrite changes from another transaction. So if B
hasn't read A's changes, B can't overwrite them.

Robert F. Tulloch wrote:

>Why would B get a deadlock if A has already committed?

If you're using a consistent view of data, a transaction can't
see changes made by concurrent transactions.


We have answers.