Sql cue execution

[dotkpay]

Hello,

Suppose you have about 10 users logged into your members' area and they are performing actions that send queries to the database and 10 scripts all query the one database record at the same time. Does MySQL perform a query one by one or they are all executed at once?

 

Thanks in advance.

[AbraCadaver]

They are executed as soon as the user requests it, so if there are 10 users requesting the query at the exact same time, then there will be 10 queries executed at the exact same time.

[Psycho]

Well, nothing happens at the exact same time and a CPU does not do everything at once. It does things one at a time (per core and/or thread). If 10 users are accessing pages which run queries they will "hit" the server in a specific order and be processed in that order (although that order my be in milliseconds). However, if there are multiple queries on a page you can't be sure that all the queries on one user's page will complete before the queries on another user's page are started. This can lead to concurrency problems. Is that what your concern is?

 

So what potential problem are you trying to solve.

[xyph]

MySQLi's multi_query method will help with race conditions, from what I understand.

 

Locking tables is another option, but slow and only recommended as a last resort

[dotkpay]

Yes, am trying to avoid concurrency problems. They could lead to inaccuracy. I need just one page to query the database at a particular time. Its queries should complete first before another page steps in.

 

Thanks

[fenway]

What, exactly, the query you're worried about?

[Psycho]

I need just one page to query the database at a particular time. Its queries should complete first before another page steps in.

 

Well, you can lock the database at the beginning of the script and then unlock it when the page completes. I've never done it so I don't know how to do it, but a Google search would probably find some resources. But, there are better solutions in most cases (which is why I suspect fenway asked to see some examples). In may instances there are ways to handle concurrency problems by "how" you do your queries.

 

As an easy example, let's say I have two users (User a & User B) who both have a list of records on their screens. Then both try to delete the same record. This can happen at the same time or some time apart since once User A deletes the record, it is still being displayed on User B's screen. Anyway, I would handle the potential concurrency issue by running the delete query and THEN checking the affected rows. If it is 0, then you know someone else deleted the record previously.

$query = "DELETE FROM table WHERE id = $delete_id";
$result = mysql_query($query);
if(!$result)
{
    echo "There was a problem running the query";
}
elseif(mysql_affected_rows()==0)
{
    echo "The record was previously delete.";
}
else
{
    echo "The record was successfully deleted.";
}

[dotkpay]

My issue with concurrency is extremely big. Imagine an administrator of a paid-to-click site is subtracting credits from a certain user.

 

Before subtraction the user has 1000 credits. The admin is to deduct 300 credits. Balance = 700

But at that exact point the user is earning some other points from lets say hits or clicks on his banners. He earns 200 credits.

The system has to credit the user with the credits.

So the final balance is supposed to be 900.

 

The admin has to make 2 queries:

1.  $sql_1 = ("SELECT credits FROM accounts WHERE user='$user'");

 

After subtracting the 300 from 1000 he then makes a second query:

2.  $sql_2 = ("UPDATE accounts SET credits='700' WHERE user='$user'");

 

The system's first query is:

3.  $sql_3 = ("SELECT credits FROM accounts WHERE user='$user'");

 

After the addition a second query is sent:

4.  $sql_4 = ("UPDATE accounts SET credits='$new_credits' WHERE user='$user'");

 

If we had query 1 first then 2,3,4 in order it would be ok. (Final credits would be 900)

if we also had query 3 first then 4,1,2 in order it would still be fine because we would still end up with 900.

 

But imagine if we had query 1 first then 3,2,4. The users credits would end up as 1200.

Order 1,3,4,2 would create a final credits balance of 700.

Order 3,1,2,4 = 1200

 

You can come with 2 or more combinations, the rules are: 2 can't come before 1 and 4 can't come before 3.

This is how terrible concurrency can be.

Locking the database has already been suggested for every page. Am trying to find out if there are better ways of avoiding concurrency.

 

Thanks in advance.

[Psycho]

The scenario you described is not one that is a problem, the problem is your logic. What WOULD be a problem is if there was a scenario where the balance could potentially drop below 0, which I assume shouldn't be allowed.

 

Let's start with the problem in your current logic:

 

OK, it seems you are doing a SELECT query to get the current amount, changing the amount and then running an UPDATE query to set the new amount. That's not necessary. IF you are simply adding/subtracting a value all you need is an update query. Example:

$addCredits = 200;
$query = "UPDATE accounts SET credits=credits+$addCredits WHERE user='$user'";

 

So, there is no reason to do a select query first. In your example above, steps #1 and #3 are not needed, just do the update query. However, if you are deducting credits I would assume you don't want to allow the balance to drop below 0. Again, all you need is a properly crafted UPDATE query. Taking your example above, let's say that the admin needs to deduct 300 credits - but you don't want to make the deduction if the user doesn't have 300 credits. You could use the following query:

$subtractCredits = 200;
$query = "UPDATE accounts SET credits=credits-$subtractCredits WHERE user='$user' AND credits>=$subtractCredits";

 

Now, that query will ONLY subtract the credits if the user has enough in their balance. But, you may be thinking "how do I know if the credits were subtracted or not?" Easy, just check if any records were updated with the last query. IF yes, then you know the user had the available balance and the amount was deducted. If not, then you know that none of the records met the WHERE conditions: either the user did not exist or they did not have the available balance.

if(mysql_affected_rows()>0)
{
    echo "The amount was deducted";
}
else
{
    echo "No records were updated. The amount was NOT deducted";
    //Do validation
}

 

If no records are updated, you could then do a SELECT query to get the available balance using on the $user in the WHERE clause. If no records are returned, then you know the user does not exist and you can provide an appropriate error condition. Otherwise, you can display that the deduction failed and what the current available balance is.