Multiple DB users updating at the same time

Question

I am curious what path I should take to accomplish the following. I want multiple computers at one location to be able to view and make changes to data inside a mysql DB with a web browser. I dont have extensive knowledge in this area, but from what I do remember this was very difficult if not impossible.

Example: Lets say I have a record for John and I want 2 computers to be able to edit Johns record. Please note that the computers will not be editing the same portion of Johns record. Lets say one record is changing a status from need to be called to called and the other computer is changing the status of need to be ordered to ordered.

I want a solution that could natively handle this.

My current knowledge is building web interfaces with PHP and SQL. I would like to use these languages as I have some prior knowledge.

So my question: Is this possible? If, so exactly how would it work(flow of info)?

Answer 1

Use Transactions. Updating a single record at the exact same time isn't really supported, but applying one transaction followed immediately by another certainly is. This is native to MySQL.

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Answer 2

One other thing to do is the old desktop approach. Wich is almost mannualy control the flow of modifications. I will show:

Say that you have a client table with the fields id, firstname, lastname, age. In order to control multiple users updates you will add the version integer default 0 field to this table.

When you populate the object on the form to an user you will also store the actual version that the user has selected.

So lets assume that your client table is like this:

id   firstname   lastname   age   version
 1     Tomas       Luv       20      0
 2     Lucas       Duh       22      0
 3     Christian   Bah       30      0

When the user select the client with the id=1 the version of this row is, in this moment, 0. Then the user update the lastname of this client to Bob and submit it.

Here comes the magic:

Create a trigger (before update) that will check the current version of that registry with the version that the user previously selected, something like this (this is just pseudo code, as I'm doing it from my head):

create trigger check_client_version on client before update as

  begin
      if new.version != old.version then
          throw some error saying that a modification already was done;
      else
         new.version = old.version + 1;
      end if;
  end;

On the application you check if the update has this error and inform to user that someone else made change on the registry he try to change.

So with the given example it would be like:

1 - The user A selected the row 1 and start editing it

2 - At the same time the user B selected the row 1 and save it before the user A

3 - The user A try to save his modifications and get the error from the application

On this context the user A has the version field pointed to 0 also is the user B but when the user B save the registry it now is 1 and when the user A try to save it it will fail because of the check trigger.

The problem with this approch is that you will have to have a before update trigger to every table in your model or at least the one you are concerned with.

Answer 3

There are several ways that you can accomplish this. There's already some great PHP database editing software packages out there (phpMyAdmin).

To handle this in code though you can either use Transactions (depending on what flavor of SQL you're using this would be done differently)

One of the easier ways to ensure that you don't have people's data clashing with one another is just by adding additional where clauses to your statement.

Lets say you have a user record and you want to update the last name from Smith to Bill, and the user ID is 4.

Instead of writing

   UPDATE users SET lastName='Bill' WHERE id='4'

You would add in:

   UPDATE users SET lastName='Bill' WHERE id='4' AND lastName='Smith'

That way if someone else updates the last name field while you're working on it, your query will fail and you'll have to re-enter the data, thus faking a transaction