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]

Re: How do I handle people going into the same record at the samein web app.

rich wrote:[color=blue]
> 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.
>[/color]

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.
jstucklex@attgl obal.net
=============== ===

[Gordon Burditt]

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

>I am building an app using php and postgresql. My questionis this.[color=blue]
>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.[/color]

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]

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

Jerry Stuckle wrote:[color=blue]
> rich wrote:[color=green]
>> 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.
>>[/color]
>
> 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).[/color]

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]

Re: How do I handle people going into the same record at the samein web app.

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:[color=blue]
> 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.
>[/color]

[Gordon Burditt]

Re: How do I handle people going into the same record at the samein web app.

>If your database brand (I am not familiar with postgress) does not[color=blue]
>support transactions or locking, you can invent your own. With a[/color]

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.
[color=blue]
>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.[/color]
[color=blue]
>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[/color]

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.
[color=blue]
>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.[/color]

Gordon L. Burditt

[Dikkie Dik]

Re: How do I handle people going into the same record at the samein web app.

>> If your database brand (I am not familiar with postgress) does not[color=blue][color=green]
>> support transactions or locking, you can invent your own. With a[/color]
>
> 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.[/color]

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.
[color=blue][color=green]
>> 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.[/color]
>[color=green]
>> 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[/color]
>
> 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.[/color]

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]

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

Jerry Stuckle wrote:[color=blue]
> 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.[/color]

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]

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

Chung Leong wrote:
[color=blue]
> Jerry Stuckle wrote:[color=green]
>> 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.[/color]
>
> Or just add the original value to the where clause of the update
> statement. It's easier, probably fast, and avoids a race condition.[/color]

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]

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

rich wrote:
[color=blue]
> 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.[/color]

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 )

[Jerry Stuckle]

Re: How do I handle people going into the same record at the samein web app.

Rik wrote:[color=blue]
> Jerry Stuckle wrote:
>[color=green]
>>rich wrote:
>>[color=darkred]
>>>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.
>>>[/color]
>>
>>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).[/color]
>
>
> 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,[/color]

Sure you can guarantee it.

$result = mysql_query("UP DATE myTable SET col1='$col1' AND col2='$col2']
"WHERE col1='$oldcol1' AND col2='$oldcol2' ");
if (mysql_affected _rows != 1)
echo "Data has changed!<br>";

If either col1 or col2 has been changed the update will fail and
mysql_affected_ rows() will return zero.

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

[Rik]

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

Jerry Stuckle wrote:[color=blue]
> Sure you can guarantee it.
>
> $result = mysql_query("UP DATE myTable SET col1='$col1' AND
> col2='$col2'] "WHERE col1='$oldcol1' AND
> col2='$oldcol2' "); if (mysql_affected _rows != 1)
> echo "Data has changed!<br>";
>
> If either col1 or col2 has been changed the update will fail and
> mysql_affected_ rows() will return zero.[/color]

This doesn't guarantee it, I admit we're talking mili-, no, microseconds
here. The problem is queries aren't instantanious, they take time.

2 Queries run:
USER1 UPDATE WHERE is searched
USER2 UPDATE WHERE is searched
USER1 SETS
USER2 SETS

So, user 2 has overwritten changes made bu user 1.

Likely no, possible yes.

Grtz,
--
Rik Wasmus

[Jerry Stuckle]

Re: How do I handle people going into the same record at the samein web app.

Rik wrote:[color=blue]
> Jerry Stuckle wrote:
>[color=green]
>>Sure you can guarantee it.
>>
>> $result = mysql_query("UP DATE myTable SET col1='$col1' AND
>> col2='$col2'] "WHERE col1='$oldcol1' AND
>> col2='$oldcol2' "); if (mysql_affected _rows != 1)
>> echo "Data has changed!<br>";
>>
>>If either col1 or col2 has been changed the update will fail and
>>mysql_affecte d_rows() will return zero.[/color]
>
>
> This doesn't guarantee it, I admit we're talking mili-, no, microseconds
> here. The problem is queries aren't instantanious, they take time.
>
> 2 Queries run:
> USER1 UPDATE WHERE is searched
> USER2 UPDATE WHERE is searched
> USER1 SETS
> USER2 SETS
>
> So, user 2 has overwritten changes made bu user 1.
>
> Likely no, possible yes.
>
> Grtz,[/color]

Actually, it does guarantee it.

During the update the row is locked so it can't be changed. This has been true
in MySQL for many releases.

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

[rich]

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

Someone gave me even a better idea. Use sessions in a table. Add to
each user session a field for the Key field of each table in your
database that is important. Example use the key field for a customer
record. When you query the record for a change, write to that customer
field in your seesion table the index of the record you are on.
Whenever anyone opens that record, the ffirst thingyou do is query for
the record number you are going to change and if it is found you cannot
select the record because someone else has it open. Set your session
max life time low enough so that a user can't sit on a record too long.
This saves a person alot of work entering data only to be told someone
else was using the record. I think this is a nice way of doing it.

[Jerry Stuckle]

Re: How do I handle people going into the same record at the samein web app.

rich wrote:[color=blue]
> Someone gave me even a better idea. Use sessions in a table. Add to
> each user session a field for the Key field of each table in your
> database that is important. Example use the key field for a customer
> record. When you query the record for a change, write to that customer
> field in your seesion table the index of the record you are on.
> Whenever anyone opens that record, the ffirst thingyou do is query for
> the record number you are going to change and if it is found you cannot
> select the record because someone else has it open. Set your session
> max life time low enough so that a user can't sit on a record too long.
> This saves a person alot of work entering data only to be told someone
> else was using the record. I think this is a nice way of doing it.
>[/color]

First of all, one client cannot generally access the session data of another
client. You'd have to have some special handling in there.

Second, session data is buffered. It isn't necessarily written to the session
when you save it in the $_SESSION variable. So even if you do have a special
session handler, it may not have received the data from the first client when
the second client searches sessions.

Next, if the first client retrieves a record then shuts down his browser, the
second client will find the data in the session until the session times out -
which may be minutes or days (or even years) later. You should avoid locking
rows while waiting for user input.

All kinds of problems here - one of the worst solutions.

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