>>>>> "Mailing" == Mailing Lists <mlist@stripped> writes:
Mailing> I have an interesting challenge.
Mailing> I have to develop a mysql database on a linux server that will be used in
Mailing> an intranet (query on httpd server, in perl/mysql cgibin). That database
Mailing> can be read by as many people as possible, but cannot be modified at the
Mailing> same time by more than one person. (the lock should be set as a user reads
Mailing> the info, allow a reasonable time to change the data (timeout) and release
Mailing> the lock once either the timeout was reached or the database was update).
Mailing> Since all the queries will be made in a html page, the real challenge
Mailing> really for me is to prevent more than one person to query the modif page
Mailing> and track the progress of that user until the changes are done, then
Mailing> release the exclusive lock.
Mailing> One of the way I have now, is to have a http server running for that page
Mailing> only, with a maximum of 1 connexion. But it doesn't solve the problem
Mailing> that, once the page is retrieve, other could still retreive the page and
Mailing> both person would be changing the same data.
Mailing> Any of you have done something like this before? If so, how did you solve
Mailing> the problem?
Why do you have to lock the table? Can't you instead save the old row
information somewhere and then update the table only if no related
information has changed?
From the MySQL manual:
You can also use functions to update records in a single operation.
You can get a very efficient application by using the following
* Modify fields relative to their current value
* Update only those fields that actually have changed
For example, when we are doing updates to some customer information, we
update only the customer data that have changed and test only that none
of the changed data, or data that depend on the changed data, have
changed compared to the original row. The test for changed data is done
with the `WHERE' clause in the `UPDATE' statement. If the record wasn't
updated, we give the client a message: "Some of the data you have
changed have been changed by another user". Then we show the old row
versus the new row in a window, so the user can decide which version of
the customer record he should use.
This gives us something that is similar to "column locking" but is
actually even better, because we only update some of the columns, using
values that are relative to their current values. This means that
typical `UPDATE' statements look something like these:
UPDATE tablename SET pay_back=pay_back+'relative change';
customer_id=id AND address='old address' AND phone='old phone';
As you can see, this is very efficient and works even if another client
has changed the values in the `pay_back' or `money_he_owes_us' columns.