0

After having a suggestion from the previous question.

I am now having my visitors count using the database. On writing the script, I had a doubt so took helped of SO.

Suppose my database has a table named as Total and the value is 5.

Two different visitors visit the site at the same time, my script

$sqltc = "UPDATE visits SET Total='$ttl' WHERE Sr=1";
mysqli_query($link, $sqltc);

will try to update the database.

Now my doubt is what will be the new value in the table?

5 OR 6 OR 7 OR anything else?

I know its a rare case, but the site in which the script will be used is quiz site and there will be bulk of visitors visiting simultaneously.

Full code of the script:

$sql = "SELECT  * FROM visits WHERE Sr = '1'";
$result = mysqli_query($link, $sql);
$row = mysqli_fetch_array($result);

$date =  $row['Date'];
$ttcounter =  $row['Total'];

$ttcounter++;
$ttl = $ttcounter;

$sqltc = "UPDATE visits SET Total='$ttl' WHERE Sr=1";
mysqli_query($link, $sqltc);
Namo
  • 17
  • 2
  • 9
  • 1
    Whichever is the last one to update the row, that will be the value of the row. There really is no such thing as "at the same time". The two events are just "close enough" to each other that you don't know which one is first or second until you look at the result. Whichever is the second one's version of `$ttl` will be the result. – David Dec 19 '17 at 12:40
  • 4
    The value will be whatever `$ttl` is, and whichever of those queries ran last. If you want to increment by 1, do it in SQL: `SET Total = Total + 1`. That increments the value each and any time the query executes regardless of order. – deceze Dec 19 '17 at 12:40
  • @MarkBaker answered the question but keep in mind: 1. If you have highload of visitors, consider using something else rather MySQL. 2. Why you don't use (SET Total = Total + 1)? rather than doing the count outside the query. – Oras Dec 19 '17 at 12:40
  • Where does $ttl come from? Are you selecting Total into $ttl and incrementing it in PHP? Don't `"UPDATE visits SET Total=Total+1 WHERE Sr=1"` is better done on the database which can handle concurrent requests for update – Mark Baker Dec 19 '17 at 12:43
  • 1
    @Namo Welcome to the world of race conditions. See here for some ways to solve this: https://stackoverflow.com/a/2364320/154762 – solarc Dec 19 '17 at 12:44
  • @deceze I added some more scripts to the question in last? Should I then also go with this [answer](https://stackoverflow.com/a/47887380/8617951) – Namo Dec 19 '17 at 12:48

1 Answers1

0

A simpler solution would be to do the addition like this

$sqltc = "UPDATE visits SET Total=Total+1 WHERE Sr=1";

This will just add one to the existing value of Total and you no longer need to fetch the old row value and add something to it and then update the row again.

This also removes the likelyhood of 2 visitors both attempting to fetch the current value of Total and both visitors overwriting the current value of 5 with 6 for example. MySQL will look after locking the row so that only one visitor at a time can apply their update, so you get the correct value of 7.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
  • This only solves numerical increment races and not the wider concept of multiple accesses and updates to the same data at the same time – Martin Dec 19 '17 at 12:53
  • 1
    @Martin MySQL should look after serialising multiple updates to the same row. Thats what database engines do – RiggsFolly Dec 19 '17 at 12:54