how to manage multiple database users

[fatkatie]

I have never had to worry about concurrent users given the size of my audience.  But, things have changed on one particular input webpage.

I have a webpage that is used by project managers.  It is now likely that two managers will concurrently access and modify the same page.

For example; Both userA and userB are on this page.  userA changes dateInstall and userB changes dateRun.   After userA saves his form, userB saves his.  userB's unchanged dateInstall overwrites userA's update.  And so it goes.

I can think of a few ways to stop this but what a mess.  How do you professionals deal with this?

Thanks.

[requinix]

There are many methods.

A simple one is to store a last modified time with the data. It should be updated when the data changes, and it would be best if it was handled automatically by the database (and all database software can do that) so you don't have to worry about it. Include the current modification time in the edit form as a hidden input so that when your script processes the form, it can check if the modification time from the form still matches the one in the database. If the times don't match then you show the form back to the user with some appropriate message.

An improvement is to have the page periodically check with the server to see if there were changes. You can then present that to the user when it happens and the user can do what they want. Keep in mind if you are periodically checking that it might be user A makes their changes just before user B tries, so your form code still has to check for modifications before it saves; the checks on the page are to help make the user's life a little easier, not yours.

Another method is to detect the actual changes made by the user, which means you have to remember what the data was - probably with hidden inputs. When user B changes the dateRun, you can tell from the original data that the dateRun in the database hasn't changed so it's safe to update that field. This is more complicated, so better suited if you expect multiple users to be editing the same object at the same time somewhat frequently, but don't expect them to be editing the same fields in the object.

The most professional solution is first to understand why you have multiple users editing the same thing. You have both a technical problem of overwriting changes but also a human problem of two people probably not knowing what the other is doing. Which means you can't truly fix this until you can resolve both problems.

[fatkatie]

Yes, the best fix is answering why are two messing about with the same data.  The page is a projects 'encompassing' calendar that shows all project schedules.  The fix would be to restrict scheduling to the job owner or a master scheduler.  But I can't do that now.  I'll need to think about this

The 1st, the time stamp, is great.  I didn't cross my mind; and that last update value is already there. The second could be a lot of work, and given I should really address the access, it would be a waste of time as a temporary fix.

Thanks for the quick comeback.  Time stamp it is.

[Barand]

Another simple method is to add a version number column to the table ...

CREATE TABLE `test_product` (
  `prod_id` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `version` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from test_product;
+---------+-------------+-------+---------+
| prod_id | description | price | version |
+---------+-------------+-------+---------+
|       1 | Product A   | 49.99 |       2 |
+---------+-------------+-------+---------+

... then, when updating, make sure the record still has the same version number that you retrieved. If the number is the same you can update and increment the version. Below is a sample page which demonstrates this.

• Open in two browser pages - both will show the same data
• change one field in the first page and update then
• go to second page and change second field and update

<?php
include 'db_inc.php';       // Substitute your own
$db = pdoConnect('test');   // database connection code

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $stmt = $db->prepare("UPDATE test_product
                  SET description = ?,
                      price = ?,
                      version = version+1
                  WHERE prod_id = 1
                        AND version = ?    
                 ");
    $stmt->execute([ $_POST['descrip'], $_POST['price'], $_POST['version'] ] );
    if ($stmt->rowCount()==0) {
             // update didn't happen
        echo "Concurrent update occurred, try again<br>" ;
    }
}

$res = $db->query("SELECT description
                        , price
                        , version
                   FROM test_product
                   WHERE prod_id = 1
                  ");
$r = $res->fetch();
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Example</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript">
</script>

<style type="text/css">
</style>
</head>
<body>
    <form method="POST">
        <input type="text" name="descrip" value="<?=$r['description']?>"><br>
        <input type="text" name="price" value="<?=$r['price']?>"><br>
        <input type="hidden" name="version" value="<?=$r['version']?>"><br>         <!-- hidden field -->
        <input type="submit" name="btnSub" value="Submit">
    </form>
</body>
</html>