Lag in php causing mysql to freak out

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
xiosen
Forum Newbie
Posts: 1
Joined: Wed Mar 21, 2007 3:46 am

Lag in php causing mysql to freak out

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

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