Subject Re: [ib-support] Deadlock and wait
Author Woody
From: "Ann W. Harrison" <aharrison@...>
Sent: Thursday, July 26, 2001 10:46 AM
> One of the primary properties of a transaction is consistency - ask
> the same question twice in the same transaction and you'll get the
> same answer.
> If you want a rolling view of data, you should commit between statements.
> Commit when the statement completes and start the next transaction when
> the next statement is invoked. I realize that Delphi doesn't work that
> way - once you've committed you've lost your data. (Or is that a property
> of BDE? IBX?) Their design decisions don't affect my absolute certainty
> that having a user mull over data in an open transaction is a bad thing.
> In a browsing application with think-time for the user, committing after
> every statement is not unreasonably expensive since the transaction start
> rate will be less than one per second. On the other hand, when inserting
> records in a loop, with no interaction, committing every statement IS a
> major source of overhead, potentially starting hundreds of transactions
> per second.

OK, let's see if I got this straight then. 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. Changing
data on a form doesn't necessarily 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? So, as long as I commit after posting changes, I should be OK.
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.

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.

Am I out in left field on this one, or what?