xiosen Posted March 21, 2007 Share Posted March 21, 2007 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. Link to comment https://forums.phpfreaks.com/topic/43687-lag-in-php-causing-mysql-to-freak-out/ Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 http://us2.php.net/manual/en/ref.mysql.php http://us2.php.net/manual/en/function.mysql-stat.php Might be able to help you through. Link to comment https://forums.phpfreaks.com/topic/43687-lag-in-php-causing-mysql-to-freak-out/#findComment-212073 Share on other sites More sharing options...
xiosen Posted March 21, 2007 Author Share Posted March 21, 2007 Thank you for your links. Ive setup a page that shows me some information like on the page you showed me. Unfortunately I have no idea what I am looking for that might cause this. Link to comment https://forums.phpfreaks.com/topic/43687-lag-in-php-causing-mysql-to-freak-out/#findComment-212179 Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 The stat page can check to see if a query is currently running, if it is you can sleep the script until it is freed up. Link to comment https://forums.phpfreaks.com/topic/43687-lag-in-php-causing-mysql-to-freak-out/#findComment-212188 Share on other sites More sharing options...
xiosen Posted March 21, 2007 Author Share Posted March 21, 2007 Is this the best possible solution considering there could be hundreds online executing these same scripts. How do other sites do it when they have thousands of users online updating tables im sure with opposite users such as set usersonline=usersonline+1 and usersonline=usersonline-1 for example without having this same issue? I thought mysql queues up queries, which in theory my queries should never conflict as they would do them 1 at a time. Is there a way I can somehow log and see everything executing at the same time to determine what the actual queries are that are making the field set to the maximum? Link to comment https://forums.phpfreaks.com/topic/43687-lag-in-php-causing-mysql-to-freak-out/#findComment-212249 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.