Does SELECT....FOR UPDATE actually delay the read?

Question

So here is my scenario, lets assume I am making an online shopping platform. And My User have a balance of 100 in the user_balance field or table.

Now, the user, open both the withdrawal page which let them withdraw money and a shopping page which let him to buy a watch of 100 dollar with one click

Let say the user withdraw 100 dollar and buy a watch for 100 dollar at the same time.

My question is will the SELECT user_balance FROM balances FOR UPDATE execute at the same time or it will wait other to finish select.

If both the SELECT...FOR UPDATE execute at the same time, the user_balance will show 100 for both page and thus, it will allow withdrawal of 100 and purchase a watch for 100 and hence, when we finally update the balance of the user it will show a negative balance

100(user balance) - 100(withdrawal amount) - 100(purchasing of watch) = -100

Here is concept of code of both pages:

Withdrawal Pages:

$withdrawal_amount = 100;

$user_balance = "SELECT user_balance FROM balances FOR UPDATE"; //actually return 100?(not sure about it, that is what my question about)

if($user_balance > $withdrawal_amount){
     //allow withdrawal
     $update_sql_query = "UPDATE balances SET user_balance = user_balance - " . $withdrawal_amount;
}

Purchase Watch Page:

   $product_subtotal = 100;

    $user_balance = "SELECT user_balance FROM balances FOR UPDATE"; //actually return 100?(not sure about it, that is what my question about)

    if($user_balance > $product_subtotal){
         //allow withdrawal
         $update_sql_query = "UPDATE balances SET user_balance = user_balance - " . $product_subtotal;
    }

Answer 1

The correct approach here would seem to be for each operation to run in a separate transaction using SELECT ... FOR UPDATE. In pseudo code, the process for the withdrawal (or the purchase) would look something like this:

start transaction

SELECT user_balance FROM balances FOR UPDATE;
UPDATE balances SET user_balance = user_balance - 100;

end transaction

This pattern works here as follows. The transaction obtains an exclusive lock on the user balance record being updated. This means that any other transaction which tries to read the user balance before it has been debited will block, and will have to wait. This avoids the situation of two transactions interleaving resulting in an incorrect balance.

Note that locking reads require the InnoDB engine. Check the MySQL documentation for more information.

Answer 2

Does SELECT FOR UPDATE delay the read?

Yes. You need to use transactions to get good results.

SELECT ... FOR UPDATE, when done inside a MySQL transaction in a InnoDB table, locks the row or rows selected. Let's assume your code actually selects just one row, by doing SELECT something FROM balances WHERE id=something FOR UPDATE.

Then if two different programs connected to MySQL try to do that SELECT on the same row at roughly the same time, one of them will win. That is, it will get there first, and the query will complete.

To get this to work properly, wrap all the work you need to do in START TRANSACTION and COMMIT. The first thing you should do after the START TRANSACTION should be your SELECT ... FOR UPDATE.

If, while you're doing your work you decide the user cannot do what she wants to do, you can issue ROLLBACK in place of COMMIT and all the changes in the transaction will be abandoned.

The second program's query will not complete until the first program does COMMIT to complete its transaction. Then it will read the whatever was stored into the table in during that transaction.

These are the things to keep in mind: SQL transactions look, to other programs connected to the table server, like they happened all at once. When one program has a transaction in progress, other programs wait. Most of the time transactions complete quickly so it's hard to observe the wait time.