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.
Lag in php causing mysql to freak out
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
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.
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.