Re: SELECT FOR UPDATE

> > All ERP systems I know deal with that issue by inserting and
> > deleting some advisory lock information in another table.
> > Let's say you want to change customers 4711 address. Before
> > letting you do so on the edit screen, the application tries
> > to insert "CUST.4711" into a central lock table. Now this
> > thing has a unique index on that field, so if someone else is
> > already editing 4711, it'll fail and the application can tell
> > you so and won't let you do the same.
> >
> Unfortunatelly this aproach have a problem.
> What about if the backend or the application crashes in the middle of
> editing?

Hooray for persistent locks! :-) It's a good thing, or I like it anyway.

Backend crashes don't stop a user from doing his/her work. They continue
on, and if the post fails because the backend is down, just try again when
the backend is back up, no loss of data.

In case the application crashes, you just have to have a job run
periodically to clean out stale locks (means locks must be timestamped which
is one reason I don't like to add lock fields to the main data tables), and
give someone a tool for removing abandoned locks if needed.

Glen

> This could also be done by adding a field in the record itself and set it
> every time you edit it and unset it after the edit time.
> In this case you need to update the record every time you read it :(
> This job should be done by the DB itself, perhaps this is the way it
> works right now!
>
> > AFAIK it's the only way to deal with that problem. Think
> > about scaling as well. No enterprise class software has a DB
> > connection per interactive user. They all have some sort of
> > DB-middletear-presentation model where many users share a few
> > DB connections.
> >