Do i need to worry about effect of simultaneous request in the UPDATE operation in the given scenario
<p>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 </p>

<pre><code>users : id , username , credit 
         15 , alex     , 1000 
         16 , jack     , 1500 
</code></pre>

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

<pre><code>withdraws : 
id , user_id , amount 
1  ,  15      , 500 
2  ,  16      , 100 
</code></pre>

<p>i have to subtract withdraw amount from their credit ... i like to do this with trigger </p>

<pre><code>CREATE TRIGGER  withdraw
  BEFORE INSERT
  ON withdraws
  FOR EACH ROW
BEGIN

     UPDATE  users SET credit = credit-NEW.amount WHERE id  = NEW.user_id;
END $$
</code></pre>

<p>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 ) </p>

<pre><code>CREATE TRIGGER  update_user
  BEFORE UPDATE
  ON users
  FOR EACH ROW
BEGIN

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


END $$
</code></pre>

<p>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 ?</p>

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

<p><strong>update :</strong> 
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</p>