Re: SELECT FOR UPDATE - Mailing list pgsql-general

>   >    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
>
> With no locks, both users will have the same original data, but User1's
> changes will not be seen by User2 and will therefore be lost.

This is an unavoidable consequence of any multi-user system. If two (or
more) users can modify the same record, they have to understand that someone
else may be modifying that record. This shouldn't be a problem though
because UPDATE statements only affect the fields that the user wants to
update. User2 will only overwrite changes made by User1 if they both wanted
those changes made.

There are a few instances where changes might affect how User2 might edit
the record, but the only ones I can think of are cumulative changes (for
instance, an account balance). Those *do* need to be updated in some sort of
critical section, such as that provided by SELECT FOR UPDATE, but I don't
think they require attention from User2 unless some special situation occurs
(the account would now be overdrawn).

> Alternatively, if transactions are used, User2's changes will be
> rolled back and lost.

Why are they lost? The client should check to see if the transaction
succeeds. If not, then it handles the situation in whatever manner makes the
best sense. I would think handling an error on UPDATE is much more graceful
for both the users and the system than locking the record while a user (who
could simply step away from his computer for more coffee) holds the lock
indefinitely.

> One way out is to do SELECT when reading and a SELECT FOR UPDATE just
> before saving; if the row has changed, the user is warned and must
> redo his changes -- but this could lead to the loss of a lot of editing.

Also, a lengthy WHERE clause in the UPDATE can do the same. In other words,
don't just use the primary key but all the fields. If the record doesn't
EXACTLY match the WHERE statement, it can't be updated.

But a failure to update doesn't mean a loss to editing. It is the client's
responsibility to keep that data for as long as the user wants it. Let the
client say "Record couldn't be updated, some information has changed. Here
is a summary of the changes: xxx. Press Ok to continue, Cancel to modify
your changes."

> My ideal would be for SELECT FOR UPDATE to timeout with a message:
>    "table t primary key k locked by backend with PID ppppp"
> (using oid if there is no primary key).

Personally as a user I'd rather handle the conflict resolution than be
locked out of records entirely "Another user is modifying this record.
Please twiddle your thumbs and try again in a few minutes" Ugh. I can only
imagine how badly my users would badmouth me if they got a message like that
above...

Greg