I have a statistics system running on a web site achieving over 4000 unique visitors per day. This stats system records numerous things and uses the following MySQL query structure to do so:
$result=mysql_query("UPDATE my_table SET count=count+1 WHERE day='$my_day'");
This stats system had been live for a few days and some spurious results are appearing. Testing proved that the above query was stamping the exact same value twice.
running query x1
running query x2
I assume this is only happening when two visitors to the site trigger the script at exactly the same time.
I could not test the script as it is above so changed it slightly to 'prove' that this was happening.
$row=mysql_fetch_row(mysql_query("SELECT count FROM my_table");
$query_string="UPDATE my_table SET count='".$new_count."' WHERE day='$my_day'";
This did give me duplicate values but was not conclusive proof as it introduced other factors which could cause the fault in themselves.
There is no way that I can categorically prove this is happening, but other stats taken by the system should tally to this example and they don't so I am convinced that it is.
I can't change the value of the field being updated to an auto_increment field as there are more than one of these fields per table. I am therefore thinking that this is a MySQL record-locking issue but can find nothing about this subject and have always thought that the intrinsic database handled this processing itself, queing each query. Does anyone know of any reason why I may be wrong in my thinking here or other factors I have not accounted for?
Thanks in advance.