transaction safe database writing

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jaymoore_299
Forum Contributor
Posts: 128
Joined: Wed May 11, 2005 6:40 pm
Contact:

transaction safe database writing

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
Post Reply