Multiple users updating MySQL table at the same time

doc_me

Hi,

I'm new to this site and PHP, of course.

I want to make sure that when multiple users update the "same" database table at the same time, their changes are not lost/do not overlap. I don't seem to find any qualifier to enforce this while opening the database connection and the table itself.

Here's what I have:

$con = mysql_connect("localhost","root","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

$db_open = mysql_select_db("mysql", $con);
if (!$db_open)
{
echo "Error opening database" . mysql_error();
exit;
}
$result = mysql_query("SELECT * FROM um");
if (!$result)
{
echo "Error fetching results" . mysql_error();
exit;
}

laserlight

I want to make sure that when multiple users update the "same" database table at the same time, their changes are not lost/do not overlap. I don't seem to find any qualifier to enforce this while opening the database connection and the table itself.

If user A updates one record, then user B updates the same record less than a second later, user A's update will be "lost". There is nothing you can do about that other than by implementing some kind of archival system as used in version control systems and wikis. However, if user A updates 2 records, then user B updates the same two records at about the same time, it may be the case that record #1 is updated first by user A and then by user B, while record #2 is updated first by user B and then by user A, thus leading to an inconsistent state. This can be avoided by wrapping the updates in a transaction.

Here's what I have:

I do not see any updating being done in your example.

Sxooter

doc_me wrote:
Hi,

I'm new to this site and PHP, of course.

I want to make sure that when multiple users update the "same" database table at the same time, their changes are not lost/do not overlap. I don't seem to find any qualifier to enforce this while opening the database connection and the table itself.

I'm not entirely sure how you plan on solving this, or what method would work best.

Is this a problem where related data need to be updated in multiple tables at once, with coherence? Then you'll need transactions and innodb table types and foreign keys to ensure that.

In summary, create tables with FK refs. then update them all in transactions.