im trying to execute the following logic: add a row to table A and substruct X from a column on table B, only if its >= than X.
the score is important and sensitive to the logic of the application, multiple statements can be executed on the same user at the same time with different values and its important that only 1 "set" will be executed as a whole or not.
should i use transaction for this kind of action? will it lock the relevant rows?
can it somehow be executed as one statement without transaction with multiple sub queries? if so does it grantees that it will be executed as a whole and will lock the row from other queries?
currently i split it to 2 queries, if the first one did bot fail, the second one is executed, the problem is that hypothetically the second can fail as well and the user will have a lower score.
update table_b set score = score - 100 where userid = "a" and score >= 100
insert into table_a (a) values ("a")