Posted by Kenneth Downs on 05/20/06 03:10

rich wrote:

> I am building an app using php and postgresql. My questionis this.
> How do you handle people wanting to make parallel changes to a record.
> Since in web apps you are doing a select, bring over a record then
> updating it, there is no lock on that record while you are making the
> changes in your browser window. Does transactions handle that? Do you
> make your selection and update all part of the same transaction? This
> whole scenario has me stumped.

Rich,

You have more or less asked *the* *question* of multiple user n-tier
software development.

One big school of thought is the so called "last save wins" school, which as
the name implies, suggests that the last person to update wins. The most
common variant is to track old values, as has been suggested, and to update
only changed values. The argument for this method is that the user is most
likely to change only those values that are relevant to their task, and if
they are changing them, the probably need to be changed. If somebody else
is trying to change the same values, there may be other issues in the
database design and in the procedures of the company.

Chung's solution is your best bet if you want to prevent all possibility of
overwrites. This would be the "no dirty writes" school, that says you can
only write to the row if it is in the same condition you found it in when
you started.

It is very important to realize that the nature of the problem precludes a
perfect answer. It is a trade-off, and somebody will object to whichever
choice you make. I go for the last-save-wins school, with changed values
only.

It also just so happens that the scenario does not happen that often (all
flames to /dev/null).

As for the transaction, Postgres will block all writes to any row you update
until the transaction is finished. After that, it is as I said before,
last write wins. If you do not use explicit transactions, then each update
statement will be a transaction. And always remember the first rule of
transactions: the shorter the better.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

[Back to original message]