How do I handle people going into the same record at the same in web app.

[rich]

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.

[Jerry Stuckle]

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.

Keep the original values. Before updating, read the record in again and compare
the values to the old ones. If they don't match, put the window back up with
the new values and tell the user.

You can also be a little more creative. Check the columns which are being
updated. If they haven't changed (but perhaps others have), go ahead and allow
the update to the changed columns. However, if one or more columns have been
changed, send a message back to the user with the new values from the database.

A lot of hassle, I know. However, you wouldn't want to lock the row anyway.
What happens if someone displays the record then closes his browser? Now you
have a locked recored which you can't unlock (until perhaps some timeout value
expires).

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[Gordon Burditt]

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

One approach is to have the OLD values of (relevant fields of) the
record embedded in the form as hidden fields (or perhaps in a
session, but I think it works better on the form, as this makes the
values reflect the original values the person doing the editing saw
on the change form originally). If the OLD values in the form do
not match the values in the record, abort the update, saying that
someone has edited the record.

You could try to merge the changes. This has potential problems,
especially if someone used the old values in creating the new values.
If, for example, someone gets a 5% cost-of-living raise and a 5%
bonus for work performance, and two HR people input this at the
same time, you could lose one raise even though it *LOOKS* like two
people tried to make the same change.

You could try to merge unrelated changes only, and reject overlapping
changes. For example, if one change only changed the customer's
address, and the fields changed "behind the form's back" involved
adding services, that's OK. If there were two service changes,
that may not be OK.

This approach also prevents submitting the form, then several hours
later pressing BACK and resubmitting the form, undoing changes
made elsewhere. Always make sure that the person submitting the form
has the authority to make the changes being made *AT THE TIME THE
FORM IS SUBMITTED*. The fact that you checked when the update form
was generated is not an excuse. Don't let ex-employees with cached
forms in their browsers wreak havoc on people's accounts. Don't
let customers suspended for abuse un-suspend their own accounts.

Gordon L. Burditt

[Rik]

Jerry Stuckle wrote:

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.

Keep the original values. Before updating, read the record in again
and compare the values to the old ones. If they don't match, put the
window back up with the new values and tell the user.

You can also be a little more creative. Check the columns which are
being updated. If they haven't changed (but perhaps others have), go
ahead and allow the update to the changed columns. However, if one
or more columns have been changed, send a message back to the user
with the new values from the database.

A lot of hassle, I know. However, you wouldn't want to lock the row
anyway. What happens if someone displays the record then closes his
browser? Now you have a locked recored which you can't unlock (until
perhaps some timeout value expires).

And still, it's possible to check if the values are the same, someone
immediatlly thereafter changes the values, and only then your script has
reached the point of updating the values. So it's by no way fullproof. It
would have to happen in a very small timeframe though, and it't not very
likely to happen. Yet, given enough time and transactions, someday it might.

Then again, I haven't heard of a slim workable solution witthout that
weakness either.

Grtz,
--
Rik Wasmus

[Dikkie Dik]

The first question is: Can people access the same record in parallel?

When defining tables, I always recognize 3 categories:
- Definition tables (better known as lookup tables)
Contain only read only data that rarely changes.
No concurrency problems.
- Live data tables can be a problem
- Log tables are add-only. I never put transactions on them,
because I'd rather have an illogical order of the records
than missing records. Log tables should never be locked.

When records are only available for a single user, there's only the
possibility of the same user (or someone abusing an account) messing
with his own data (two different browsers, maybe?).
If your database brand (I am not familiar with postgress) does not
support transactions or locking, you can invent your own. With a
timestamp field and a user (or sessionId) field, you can see what user
has requested a lock. If it is locked too long ago, it is free. If it is
locked by another user or session, it is locked. As SQL commands are
usually atomic (I have yet to encounter a database brand that has unsafe
commands), an update command can be forged that takes the full locking
condition in its WHERE clause.

Look up the section about transactions in the database's documentation.
The fear of "hanging" open transactions is often solved by the fact that
connections are reset by PHP after executing the script, and the
database rolls back any open transactions in a connection when it is
reset/closed. Again, I do not know how Postgress handles transactions
and connections.

Best regards

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.

[Gordon Burditt]

>If your database brand (I am not familiar with postgress) does not

support transactions or locking, you can invent your own. With a
Web-based transactions that require user interaction in the middle
of them are dangerous and pretty much useless. Part of the problem
is that there is no timeout value which is not too short, too long,
or both at the same time. In a tech support or customer service
environment, where techs edit customer records, consider the
possibilities for getting fired or murdered by co-workers if your
computer crashes in the middle of an edit and delays the daily
billing run.
timestamp field and a user (or sessionId) field, you can see what user
has requested a lock. If it is locked too long ago, it is free. If it is
locked by another user or session, it is locked. As SQL commands are
usually atomic (I have yet to encounter a database brand that has unsafe
commands), an update command can be forged that takes the full locking
condition in its WHERE clause. Look up the section about transactions in the database's documentation.
The fear of "hanging" open transactions is often solved by the fact that
connections are reset by PHP after executing the script, and the
In other words, in a PHP-based setup, the transaction is reset
before the user has a chance to answer the "are you sure?" prompt.
(Note to self: never permit creation of a user by the name of
"sure"). Net effect: they're useless since the transaction is
always rolled back.
database rolls back any open transactions in a connection when it is
reset/closed. Again, I do not know how Postgress handles transactions
and connections.

Gordon L. Burditt

[Dikkie Dik]

>> If your database brand (I am not familiar with postgress) does not

support transactions or locking, you can invent your own. With a

Web-based transactions that require user interaction in the middle
of them are dangerous and pretty much useless. Part of the problem
is that there is no timeout value which is not too short, too long,
or both at the same time. In a tech support or customer service
environment, where techs edit customer records, consider the
possibilities for getting fired or murdered by co-workers if your
computer crashes in the middle of an edit and delays the daily
billing run.

If only one "finance" user can edit the record, there's no problem. Tech
support is a separate problem altogether, as they can access the
database without your application. So whatever, you come up with, an
administrator can always circumvene it.

Apart from that, I think that getting a message "this message is locked
by ..." is by far more acceptable than randomly incorrect bills. That is
why I started my previous mail with: The first question is: Can two
users access the same record at all? For many systems, this is not the case.
A good second question is: what is the impact of concurrency errors? For
a billing system, I'd take my time to investigate all consequences. For
a hitcounter, I don't mind missing a count once in a while.

timestamp field and a user (or sessionId) field, you can see what user
has requested a lock. If it is locked too long ago, it is free. If it is
locked by another user or session, it is locked. As SQL commands are
usually atomic (I have yet to encounter a database brand that has unsafe
commands), an update command can be forged that takes the full locking
condition in its WHERE clause.

Look up the section about transactions in the database's documentation.
The fear of "hanging" open transactions is often solved by the fact that
connections are reset by PHP after executing the script, and the

In other words, in a PHP-based setup, the transaction is reset
before the user has a chance to answer the "are you sure?" prompt.
(Note to self: never permit creation of a user by the name of
"sure"). Net effect: they're useless since the transaction is
always rolled back.

No. Like a previous poster wrote, you can check the state of the record
before updating. You do _that_ in the same transaction as the update. If
you detect a collision, it is up to you what you do with it: not
updating and getting back to the user is just one option. Only if the
script crashes during the collision check, the transaction would be
rolled back automatically instead of keeping a lock that is never needed
anymore.
I would never keep a lock between two calls to the webserver, as nobody
can guarantee that the second call will ever be made.

Best regards

[Chung Leong]

Jerry Stuckle wrote:

Keep the original values. Before updating, read the record in again and compare
the values to the old ones. If they don't match, put the window back up with
the new values and tell the user.

Or just add the original value to the where clause of the update
statement. It's easier, probably fast, and avoids a race condition.

[Kenneth Downs]

Chung Leong wrote:

Jerry Stuckle wrote:

Keep the original values. Before updating, read the record in again and
compare
the values to the old ones. If they don't match, put the window back up
with the new values and tell the user.

Or just add the original value to the where clause of the update
statement. It's easier, probably fast, and avoids a race condition.

A nice touch.

I would add that you do need to then check for affected rows, so you can
notify the user if the update did not go through.

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

[Kenneth Downs]

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 )