I need to worry about the impact of a concurrent query on an UPDATE operation in this scenario

Question

imagine this scenario, I have a website on which users get credit from activity, for example, selling their products or clicking on ads or so that my users table would be something like this

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

so now users can request a loan withdrawal in some form, my review table will be something like this

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

I need to subtract the amount from their loan ... I like to do this with a 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 that users cannot get negative credit during the withdrawal process, I have this trigger (from the curse I will also check this in the code logic)

 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 that someone, intentionally or through some server error, will send several withdrawal requests at the same time and withdraw more than his credit .... and if so, how can I prevent this?

Do I need to lock the user table before each insert to insert, or something like this?

update: if I am going to lock the user line and use the transactional wrapper for the whole operation ... it seems reasonable, but how does the trigger introduce? I mean, obviously, I need to start the transaction and end / commit it in the code (since I hate stored procedures and never use them) does this mean that I have to give up the trigger and do a subtraction in the code, as well as somehow run inside a transaction, despite being launched / executed on another platform / location

Answer 1

I would solve this through Intention Locks. Check out the example here that I wrote. There is a story about what happens between line 1 and line 5 of the transaction block, as well as other relevant information.

You naturally do not get an increment, as in this example. Most likely, your code will perform your update and Commit as quickly as possible.

This code can belong anywhere, and not just in a stored procedure. It can be an event and a trigger. The DB mechanism (for example, INNODB) is your mechanism to ensure that two people cannot get inside this BLOCK 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 really subject to this. It is interesting, however, that if nothing happens in the triggers, this is not a vulnerability that can be used, you can only make mistakes in the safe (it is possible that a valid output leads to an error, but I think that it is impossible, an invalid output does not lead to error).

You still should not do this, the race condition should be removed, for example, by adding locks (due to some performance).