Q:

web transactions having sql performance or accuracy issue

This is the story:
When there are 2 concurrent withdrawal $10 in the same account and the account has total only $10.
Result: both withdrawal success, account balance is -$10.
If I make the query like below:
UPDATE table
SET amount = amount - 10
WHERE (amount-10 > 0) AND id = 123;

it will be safe and accurate, only one of the withdraws will success.
Will there be performance issues, since the amount can't be indexed?

A:

Write the query as:
 UPDATE table
    SET amount = amount - 10
    WHERE amount > 10;

Then an index can be used.  I do think you should limit the update only to the given account:
 UPDATE table
    SET amount = amount - 10
    WHERE account = @account AND amount > 10;

Then an index on account can always be used.

