Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

MySQL record-locking problem

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

MySQL record-locking problem

Postby Hadleigh » Wed May 14, 2003 1:25 am

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.

For-instance:

count=10
running query x1
count=11
running query x2
count=11

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");
$new_count=$row[0]++;
$query_string="UPDATE my_table SET count='".$new_count."' WHERE day='$my_day'";
echo"$query_string";
$result=mysql_query($query_string);

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.

Hadleigh
Hadleigh
New php-forum User
New php-forum User
 
Posts: 32
Joined: Tue Feb 04, 2003 10:32 am
Location: UK

Postby Godiwa » Sat May 17, 2003 4:14 am

the problem is not the locking of MYSQL but more likely to be that the script will READ from the DB and get the value 11 and THEN they will add 1 = 12 but if TWO people READ 11 at the same time then they will both submit the value 12 to the DB

it is a tiny inaccuracy and shouldn't be a problem to you, it happens with all countes, the only thing precise is reading hte log

OR

you could make a system that just adds row with a column with a value of 1 (or just count the amount of rows) and that way when you WANT the result then it just counts rows, and since the DB wont be accessed to get the COUNT value all the time then there is no chance of 2 people getting the same value.

but try it out there is many ways to do it

Godiwa
Godiwa
New php-forum User
New php-forum User
 
Posts: 40
Joined: Wed Mar 05, 2003 5:55 am


Return to PHP coding => General

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron