Subject Re: wait/nowait transaction settings
Author Svein Erling Tysvær
--- In, "Nick Upson" wrote:
> > Nick Upson wrote:
> > > I have 2 processes which may both try to update the same row in
> > > a table, currently when this happens I get "update conflict...",
> > > so I though I would change them both to having a wait
> > > transaction, which would do what I need.
> >
> > On 18/06/07, Ann W. Harrison wrote:
> > Unfortunately, no. Waiting doesn't change the fact that another
> > concurrent transaction changed a record that you're planning to
> > change. In theory, read-committed transactions could refetch
> > the record, getting the new version and allow you to change it,
> > but that's not how Firebird works now. And, of course, if the
> > application has previously read the record and made decisions
> > about the change based on old values, you wouldn't want to refetch
> > the record and apply changes based on bad information.
> >
> > The purpose of 'wait' is to avoid live-lock, a situation in
> > which two transactions modify the same two records in reverse
> > order, each fails and immediately retries, fails again, and
> > so on. It's not intended to allow a transaction to modify
> > a record when it cannot read the most recent changes.
> so I can't let A wait until B commits and then A will proceed?
> There isn't actually a conflict as they are updating different
> fields in a single-row table

If you want to allow these fields to update independantly of each
other and still not allow simultaneous transactions to update the same
field concurrently, I would consider storing them in separate records
or even separate tables.