Subject Re: [ib-support] Deadlock and wait
Author Woody
From: "Marcos Vinicius Dufloth" <dufloth@...>
Sent: Thursday, July 26, 2001 11:57 AM
> Why user B don't wait, for predefined time (would be DEADLOCK_TIMEOUT),
> until user A commit/rollback and then change the record?

Because user B doesn't know whether user A is changing the record or just
looking at it. Both need a transaction open in order to view/modify the
information. You can't close the transaction without closing the query even
if you're just looking at the data. If user A and B open a transaction and
have the same record displayed, then user A changes something and posts the
changes and commits, user B is still viewing the record before the changes
made by A. Then, when user B goes to change something and post, the record
must be refreshed first before making the changes, right?

> In some circustances I need that one user wait by a record to change-it.
> Updating a inventory quantity for a product, for example, is one.
> The first guy locks the record, the second waits until de first free-it.
> What the problem with this?

There are no record locks in Interbase so you don't know that another user
is updating it. In Paradox (ugh!) you can lock a record and test for that
lock whenever someone else tries to access it, but that isn't the case in

> Of course if I'm setting a value like "update table set field = value
> could be a problem, but if I make "udpate table set field = field +
> where's trouble?

I don't do these types of updates so I can't respond to it. My updates
consist of data-aware controls responsible for posting the data through
IBX's TIBDataset which has it's own update SQL command.

> Other: I need read commited to update records in sequence, where one
> needs the result of previous update, like "update table set field = field
+ value...".
> So, what is the other option, unlike read only changes

I would assume that the only way is to start the transaction just before
executing the update query and committing it just after. You wouldn't want
to do it this way if you have a transaction that's been open for a while
already. You would want to commit any pending transactions, start a new one,
then execute the update, right? That would guarantee that you have the
correct information for the update.