Jump to content

how to manage multiple database users


fatkatie

Recommended Posts

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.