Page 1 of 1

Lag in php causing mysql to freak out

Posted: Wed Mar 21, 2007 11:04 am
by xiosen
Hello Everyone, I seem to be having a major problem and I cannot seem to find the answer.

The problem I am having is due to the lag in my server is causing the mysql queries to conflict or mess up each other or something. This problem all started when the lag became aparent on my server. Unfortunately that is not something I can fix yet, so I would liek to fix up the coding error.


I have a field in a table that looks like this:

money | bigint | 40 | Default: 0 | unsigned


The problem I am having is that when 2 users call 2 separate scripts with 2 separate queries relating to the same user and updating the same field, mysql freaks out and puts the absolutely maximum value into that field.

Heres an example:

One person goes to the bank.php and deposits say 200,000. At the same time another user goes to battle.php and attacks the first person and takes away 100,000. So the remaining value in the field should be 100,000. Because of the lag in the scripts all the sudden the first person has 1.8446744069415E+19 for there money field. As you know, this is exactly the maximum integer for a bigint field that is unsigned.

Both of the scripts when the but occurs are doing different math operations like so:

bank.php - Update table set money=money+$deposit where playerid=$id
battle.php - Update table set money=money-$battleloss where playerid=$id

So, in conclusion. It seems that calling these 2 queries at the same time results in mysql locking up and then inserting the maximum bigint value into the versus the result of the simple math operations.

Thank you for any help you can provide.

Posted: Wed Mar 21, 2007 11:20 am
by feyd
Why are two different users updating the same record?

You have a race condition. The deduction is happening first thereby reducing the value to a negative number. Since it's unsigned that wraps around to the maximum value. It may be better to first verify they can perform the action before making the deduction.

Posted: Wed Mar 21, 2007 11:21 am
by RobertGonzalez
You may also want to look at table locking so that only one query can happen at the same time. Not necessarily the best fix, but it could be helpful.

Posted: Wed Mar 21, 2007 12:20 pm
by AKA Panama Jack
That doesn't sound like PHP lag but an overloaded database server. In otherwords the database is taking too long to process the request. PHP lagging out will not cause anything like this but an overloaded database server will cause queries to stackup.

Oh, you should never use unsigned integer fields necause of what feyd mentioned. If you ever try to update the field with a negative value it will not automatically enter it as 0 but will store it as the MAXIMUM value the integer field can hold. Unsigned integer fields are just plain BAD practice because of this.