MySql, Internet application and PHP

Hi,

My company developed an Intranet Application with MySql and PHP. It works
rather fine, but multi users are not really supported.
User 1 reads data
User 2 reads same data
User 2 updates his data
User 1 updates his data ... and of course scratch the changes made by the
previous user.

In a few weeks, I'll rewrite a part of this code to fix this problem.
I was told it's not easy to lock records and use transactions with an
Internet Application (using just PHP language).
I'm sure I'm not alone with this problem. As we don't want to use an other
language, such as Java or Delphi, which solutions do you see ?

Thanks for your idea !

On Tue, 25 Dec 2007 14:04:40 +0100, Marc Mendez wrote:
> Hi,
>
> My company developed an Intranet Application with MySql and PHP. It works
> rather fine, but multi users are not really supported.
> User 1 reads data
> User 2 reads same data
> User 2 updates his data
> User 1 updates his data ... and of course scratch the changes made by the
> previous user.

One of the common problems of working with databases. Transactions and
locking to the rescue! (Though you can fake it out a bit by being Very
Very Careful with your updates and specifying ALL the old values in your
UPDATE ... WHERE clause, so that if someone DOES change something under
you, you don't update the row. Player 1 above is kind of stuck in limbo
with regard to what to do and you have to make her reconsile changes
by fetching in Player 2's changed record by your real key, but it ALWAYS
works.)

> In a few weeks, I'll rewrite a part of this code to fix this problem.
> I was told it's not easy to lock records and use transactions with an
> Internet Application (using just PHP language).

No harder to do those things with PHP than any other host language. You
DO have to account for the fact that browsers don't keep state between
steps though. But that kind of only increases the transactive nature of
designing your application. It kind of makes it a lot like writing
programs for green-screen terminals. You won't be able to hold a row
locked indefinately while someone types in changes to be made, but
that's not always the best way to lock a row anyway. What if someone
goes to lunch sitting on an update screen?

> I'm sure I'm not alone with this problem. As we don't want to use an other
> language, such as Java or Delphi, which solutions do you see ?
>
> Thanks for your idea !

Honestly, I don't think the choice of language makes a huge difference
in programming approaches with regard to these issues. It may make it a
little easier to keep track of which fields were changed in state, and
you can poll the database while you have a record open looking for
updates, but that won't do much except allow you to update Player 1's
screen when the record changes under her feet.

--
52. I will hire a team of board-certified architects and surveyors to
examine my castle and inform me of any secret passages and abandoned
tunnels that I might not know about.
--Peter Anspach's list of things to do as an Evil Overlord

Marc Mendez wrote:
> I was told it's not easy to lock records and use transactions with an
> Internet Application (using just PHP language).
> I'm sure I'm not alone with this problem. As we don't want to use an other
> language, such as Java or Delphi, which solutions do you see ?
>
> Thanks for your idea !

I have never worked with transactions and such things but I hate
rewriting code and therefore rely most of the times on the PEAR MDB2
class for my database connections and queries, as I dislike rewriting
(badly written and designed) code.

AFAIK they also support commit (for mysql database engines). More
general information can be found here [1] and some specific information
on transactions [2].

Good luck!

Jonathan

[1] <http://pear.php.net/manual/en/package.database.mdb2.php>
[2]
<http://pear.php.net/manual/en/package.database.mdb2.intro-transaction.php>

On Tue, 25 Dec 2007 14:04:40 +0100, "Marc Mendez"
<marc.mendez_SPAM_NO@bigfoot.com> wrote:

>Hi,
>
>My company developed an Intranet Application with MySql and PHP. It works
>rather fine, but multi users are not really supported.
>User 1 reads data
>User 2 reads same data
>User 2 updates his data
>User 1 updates his data ... and of course scratch the changes made by the
>previous user.
>
>In a few weeks, I'll rewrite a part of this code to fix this problem.
>I was told it's not easy to lock records and use transactions with an
>Internet Application (using just PHP language).

That's true. It would be a bad idea to try to lock rows or
tables while waiting for user input. It may never
arrive...

>I'm sure I'm not alone with this problem. As we don't want to use an other
>language, such as Java or Delphi, which solutions do you see ?

The folowwing scheme is a common solution.
It involves an extra column to mark a row is being held,
let's call it stamp.

User 1
begin trans
reads data
update table set stamp = some unique value1
end trans

User 2
begin trans
reads same data
update table set stamp = some unique value2
end trans

User 2
begin trans
read data
is stamp still some unique value2?
yes
updates his data
end trans
no
errormessage to user 2

User 1
begin trans
read data
is stamp still some unique value1?
yes
updates his data
end trans
no
errormessage to user 1 "changed by other"

Sometimes recovery is possible without bothering the user,
when and how is left as an exercise to you :)

>Thanks for your idea !

HTH
--
( Kees
)
c[_] Extreme Programming - Redundant Array of Inexpensive Developers (#484)

Hi Jonathan ,

Thanks for your advices : I will have a look to your link. I have already
think using a timestamp column : I read it with the SELECT, and I update
data with it as unique key. Of course, if update is successful, I update the
timestamp and so on.

But, what about several requests at one time. I deal with the concept if
transaction : I may have several update requests. If one of them fails, I
must "rollback" all the other... And without a transaction , I don't know
how to do... Mind you, I may have the answer in the web page you sent me.

I don't plan to lock rows during the select request (I never did it, even
with other languages). I can solve the problem of data changed between the
select and the update request with a timestamp for instance. I will have to
solve as well the problem of an update request that may changes several rows
at once (and of course, I can't know how many before .... I don't know if
I'm very clear about this point)

I will read, and I'll tell you what I understood .
Jonathan wrote:
> Marc Mendez wrote:
>> I was told it's not easy to lock records and use transactions with an
>> Internet Application (using just PHP language).
>> I'm sure I'm not alone with this problem. As we don't want to use an
>> other language, such as Java or Delphi, which solutions do you see ?
>>
>> Thanks for your idea !
>
> I have never worked with transactions and such things but I hate
> rewriting code and therefore rely most of the times on the PEAR MDB2
> class for my database connections and queries, as I dislike rewriting
> (badly written and designed) code.
>
> AFAIK they also support commit (for mysql database engines). More
> general information can be found here [1] and some specific
> information on transactions [2].
>
> Good luck!
>
> Jonathan
>
> [1] <http://pear.php.net/manual/en/package.database.mdb2.php>
> [2]
> <http://pear.php.net/manual/en/package.database.mdb2.intro-transaction.php>