Re: SELECT FOR UPDATE

Jan Wieck wrote:

>Oliver Elphick wrote:
>
>>Jan Wieck wrote:
>> > But the question itself tells that you're about to implement
>> > a major design error in your application. Holding database
>> > locks during user interaction IS A BAD THING. Never, never
>> > ever do it that way. And anybody telling you something
>> > different is an overpaid idiot.
>>
>>I can see arguments to support this view, but consider this classic
>>scenario:
>>
>>User1: Read data into an interactive program
>>User1: Start to make changes
>>User2: Read data into an interactive program
>>User2: Start to make changes
>>User1: Save changes
>>User2: Save changes
>>
>
> 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?

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.
>
Jose Soares