Can two or more queries be executed at the very same time?

Answer 1

Execute this before accessing the table:

LOCK TABLE table_name READ;

That will lock the table. When you have finished doing the work, you call:

UNLOCK TABLES;

Answer 2

I think one approach would be to use transactions. When the transactions starts the table will get locked. Its actually similar to what Cristian proposed.

Answer 3

Transactions in innodb will help you with that. Remember to read up on your mysql version, because they were bugged earlier.

Answer 4

Contrary to belief, SQL statements are received and placed in a queue for processing. They are not executed at the same time.

What happens depends on business rules - if a bank account allows overdraft, the second request will succeed (assuming large enough overdraft). Otherwise, the second request will fail.

Answer 5

I think you are worried about 2 separate threads connecting to the database and one with-drawing money while the other is executing code that based on a balance read previously.

In this situation you should apply a read lock (as Christian says) prior to querying the balance and releasing the lock once [in your code snippet] the if statement has ended.

You may face problems if anything falls over while your thread still has the lock so you need some canny bash scripts to kill stuff ;)

Answer 6

A very stereotypical example would be:

DELIMITER //
CREATE PROCEDURE do_banktransfer(
  IN transfer_amount INT,
  IN from_account    INT,
  IN to_account      INT,
  OUT success        INT)
BEGIN
START TRANSACTION;
UPDATE account SET balance = balance - transfer_amount WHERE id = from_account;
UPDATE account SET balance = balance + transfer_amount WHERE id = to_account;
SELECT balance INTO cur_balance FROM account WHERE id = from_account;
IF cur_balance < 0 THEN
    SET success = 0;
    ROLLBACK;
ELSE 
    SET success = 1;
    COMMIT;
END IF;
END;//