Is it possible to execute two or more queries at the same time?

Question

Well, let's say the bank_account table. There is a line called money . It has 2000 meanings. And we have two people trying to withdraw this money, let them do it. So is it possible that they will do this at the same time? For instance. there is a ready code:

  $all_money = get_value('money', 'bank_account); //lets suppose thats a function how we get value money from bank_account table if($all_money > 0) { //here is a code where those money are being withdrawed from bank_account and inserting into the person account } 

If these two people get the value 2000 at the same time, this means that this if($all_money > 0) item will be completed and both players will receive 2000, and bank_account will have -2000 money. So is this possible? If so, how can I protect him? Thanks.

Answer 1

Perform this before accessing the table:

 LOCK TABLE table_name READ; 

This will lock the table. When you finish work, you call:

 UNLOCK TABLES; 

Answer 2

Contrary to belief, SQL statements are accepted and queued 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 be successful (provided that the overdraft is large enough). Otherwise, the second request will not be executed.

Answer 3

I think one approach would be to use transactions . When transactions begin, the table will be locked. In fact, this is similar to what Christian suggested.

Answer 4

Transactions in innodb will help you with this. Remember to read the mysql version because they used to be tapped.

Answer 5

A very stereotypical example:

 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;// 

Answer 6

I think that you are worried about two separate threads connecting to the database, one with money, while the other executes code based on the previous balance.

In this situation, you should apply a read lock (as Christian says) until you request a balance and release the lock once [in the code snippet] if the if statement ends.

You may run into problems if something falls while your thread still has a lock, so you need some canny bash scripts to kill things;)