#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    29
    Rep Power
    0

    Question PHP MySQL Negative Value (Balance) Issue


    Greetings..
    I am developing a desktop software where it charge user per execution the main action. For example say it will charge user 0.1$ for per PDF print.

    and my software provide multithreading. .

    so, if it run single thread it works fine

    but the problem is if user run multiple thread at one (say 10/20 threads)

    it (php) also continues user to allow the server/execution even balance get below zero..

    though my php script check whether balance is positive ..

    but after user run multiple threads balance become like -5.95$ or -25.75$ etc

    and that is a big security/financial issue..

    here is the code I am using:
    Code:
    <?php
    	
    	$strSQL = "Select * from users where Email = '$strUser'";
    	$return = mysql_query($strSQL, $strDBConn);
    	$strDBData = mysql_fetch_array($return, MYSQL_ASSOC);
    	//checking balance
    	$strBalance = $strDBData['Balance'];
    	if($strBalance < 0)
    	{
    		// if balance 0 then exit so, my software/thread will not process further
    		mysql_close($strDBConn);
    		exit('Balance Exceed');	
    	}
    
    	//rest of the codes that realted to service executaion
    	
    	// code that substract the balnce
    		$dblCost = 0.25;
    		$strSQL = "Update users set Balance = Balance - '$dblCost' where Email = '$strUser'";
    		$return = mysql_query($strSQL, $strDBConn);
    
    	//rest finising codes 
    ?>
    any help/suggestion would be highly appreciated..

    thanks in advance.
    best regards
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    The easiest trick is to do your check and update at the exact same time. One query.

    Code:
    UPDATE users SET Balance = Balance - 0.25 WHERE Email = 'user' AND Balance >= 0.25
    Then check the number of affected rows to see if the query did anything: assuming the user exists, the only way it could not update is if they didn't have a high enough balance.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    29
    Rep Power
    0
    Originally Posted by requinix
    The easiest trick is to do your check and update at the exact same time. One query.

    Code:
    UPDATE users SET Balance = Balance - 0.25 WHERE Email = 'user' AND Balance >= 0.25
    Then check the number of affected rows to see if the query did anything: assuming the user exists, the only way it could not update is if they didn't have a high enough balance.
    thanks for your fast reply sir..

    but the issue is.. my main code/function run before the update.. ..

    so, whether it updates or not.. threads continue executing because balance is >0 .. i hope i am clear..

    thanks again...
  6. #4
  7. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397
    Why are you taking actions based on something that may or not be true? Update the balance and, if successful, continue executing. Don't execute your code that assumes the person paid for the thing when you haven't tried to "bill" them for it yet.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    29
    Rep Power
    0
    Originally Posted by requinix
    Why are you taking actions based on something that may or not be true? Update the balance and, if successful, continue executing. Don't execute your code that assumes the person paid for the thing when you haven't tried to "bill" them for it yet.
    thanks again for your reply..

    sir, after a bit researching it seems mysql Transaction feature (begin,commit etc) could solve my issue?

    can you hep me on that?

    thanks in advance

    best regards
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    29
    Rep Power
    0
    Originally Posted by requinix
    Why are you taking actions based on something that may or not be true? Update the balance and, if successful, continue executing. Don't execute your code that assumes the person paid for the thing when you haven't tried to "bill" them for it yet.
    and also sir, i need to update the valance after main action, because main action could be failed so, i only want to charge user for successful action..

    hope i am clear..
  12. #7
  13. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,997
    Rep Power
    9397

    Moved from PHP


    What you're thinking of is a semaphore: a way of making a resource available to one process at a time. Transactions are not semaphores, and while they do end up accomplishing some of what you need, they will not solve the problem of race conditions (which you may or may not have discovered yet).

    If you must go this route then table locks may be the way to go: lock the table against writing, do your processing, make your changes, and unlock.
    This could be a bad idea if (1) your processing code is not very fast or (2) there are many other processes that need write access to the users table.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,972
    Rep Power
    375
    Originally Posted by zakir_szh
    and also sir, i need to update the valance after main action, because main action could be failed so, i only want to charge user for successful action..

    hope i am clear..
    if the main action fails, you do another db query to add back the charge for the user

IMN logo majestic logo threadwatch logo seochat tools logo