Do i need to worry about effect of simultaneous request in the UPDATE operation in the given scenario

Question

imagine this scenario , i have a website that users get credit with activity like selling their items or clicking on ads or whatever my users table would be something like this

users : id , username , credit 
         15 , alex     , 1000 
         16 , jack     , 1500 

so now users can request to withdraw their credit in some sort of payment my withdraws table would be something like this

withdraws : 
id , user_id , amount 
1  ,  15      , 500 
2  ,  16      , 100 

i have to subtract withdraw amount from their credit ... i like to do this with trigger

CREATE TRIGGER  withdraw
  BEFORE INSERT
  ON withdraws
  FOR EACH ROW
BEGIN

     UPDATE  users SET credit = credit-NEW.amount WHERE id  = NEW.user_id;
END $$

and to make sure users cant end up with negative credit in the withdraw process i have this trigger ( of curse i'll check for this in the code logic as well )

CREATE TRIGGER  update_user
  BEFORE UPDATE
  ON users
  FOR EACH ROW
BEGIN

    IF NEW.credit < 0 THEN 
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid credit error'; 
    END IF;


END $$

now my question is , is it possible someone intentionally or trough some sort of server error send multiple withdraw request at the same time , and withdraw more than his credit .... and if so how can i prevent this ?

do i need to lock users table before each withdraw insert or anything like this?

update : if im going to lock the user row and use transaction wrapper for the whole operation ... seems reasonable but how does the trigger fits in ? i mean obviously i have to start the transaction and finish/commit it in the code (since i hate stored procedures and never use them ) does it mean i have to give up trigger and do the subtraction in the code as well or somehow trigger happens inside transaction despite starting/executing in the different platform/place

Answer 1

I would solve this through Intention Locks. Look at an example here that I wrote. There is a narrative there of what happens between the Line 1 to Line 5 of a Transaction chunk as well as other pertinent info.

You naturally are not getting an incrementor like in that example. Rather, your code would as fast as possible perform your update and do a Commit.

That code can belong anywhere, not just in a stored proc. It can be and Event or a Trigger. The DB Engine (such as INNODB) is your mechanism to ensure two people can't get inside that LOCK at the same time. How you write your code related to this is up to you.

Answer 2

This is called a race condition, and the code in the question is indeed subject to that. Interestingly though, I think if nothing else happens in the triggers, it is not an exploitable vulnerability, you may be erring on the safe side only (it is possible that a valid withdraw results in an error, but I think it is not possible that an invalid withdraw does not result in an error).

You should still not have it this way, the race condition should be removed by for example adding locks (at the cost of some performance).