How to increment a Mysql Integer Field on Highload using PHP

Question

I got the following problem: When I try to increment one column of my mysql table the value get out of sync on high traffic loads.

The task is splited into two things incrementing the column and add a new row to another table. So if I add 100 rows the counter should stay at 100.

On high loads e.g. 100 request/s it comes to problems with the counter, I got 80 on the counter value but 120 rows are added.

//This is the current increment routine build with an Yii ActiveRecord Class
$id = 123;
$dataRow = ActiveRecordModel::model()->findByPK($id);
$dataRow->counter +=1;
$dataRow->save();

//Add the row 
$row = new ActiveRecordModelRow();
$row->operatingSystem = 1;
$row->save();

I think the problem is that some request are handled faster then others and maybe override the values. Hope someone can help me with pointing me in the right direction or had a suggestion how to solve this problem.

Best, Nils

Maybe a direct query will fix it UPDATE table SET counter=counter+1 WHERE id = $id — funktioneer, Feb 27 '15 at 12:57
Either that or LOCK the table(s) do the update then UNLOCK the table(s) — RiggsFolly, Feb 27 '15 at 18:13

score 0 · Answer 1 · answered Feb 26 '15 at 12:31

0

Can't you use an auto increment field in mysql so that the database handles the increment rather than the PHP. It wouldn't matter how long the processing took as the integer would be set when the record was inserted.

answered Feb 26 '15 at 12:31

Horaland

857
8
14

How to increment a Mysql Integer Field on Highload using PHP

1 Answers1