Page 1 of 1

transaction safe database writing

Posted: Fri Dec 23, 2005 9:43 pm
by jaymoore_299
If I were to receive a high volume of traffic and for each incoming surfer to a certain page I add a row to a database regarding where that surfer came from and when he came, is there is a possibility that the data might be corrupted what type of corruption might occur?

Posted: Fri Dec 23, 2005 11:00 pm
by josh
when you are just inserting rows you do not have to worry about this kind of thing, if any of your queries depend on data not changing from previous queries in the same page call then you need to implement locking or transactions...

let's look at this for example

Code: Select all

$result = mysql_query("SELECT `count` from table where `userip` = '127.0.0.1'");
$count = mysql_result($result, 0, 0);
mysql_free_result($result);
$count++;
mysql_query("update table set `count` = '$count' where `userip` = 127.0.0.1");
If for some reason something were to modify that row between the two queries your data would "corrupt"


the solution is to

Code: Select all

update table set `count`= `count` + 1 where `userip` = '127.0.0.1'
(knock it in one query)

OR

lock the table before the second query and release your lock after the second query..


without knowing the specifics of your application I cannot advise on what methods to use