Which sql query will run faster?

Question

MySQL

Hello. There is an application, we are trying to increase the speed of work in some sections. At the moment, we use locks to reduce the balance of people, but I read that you can do this without using a lock by shifting everything to mysql by writing this query:

update `users` set `balance` = `balance` - 150 where `id` = 54 and `balance` >= 150

With blocking, we first block the record, reduce the balance without `balance` >= 150, with such a request:

update `users` set `balance` = `balance` - 150 where `id` = 54

After decreasing, we remove the blocking.
Entries in table 481k.
Which query will work faster? Or does it not make much sense to change one to the other?

Answer 1

START TRANSACTION;
select * from users where id = 54 FOR UPDATE
//проверка бизнес логики на наличие денег
update `users` set `balance` = `balance` - 150 where `id` = 54;
COMMIT;

The query "where `id` = 54 and `balance` >= 150" is dangerous.
Innodb does not lock specific records, but index ranges - with such a request, all records with a balance >= 150 will be locked, with all the consequences.

Answer 2

That one request, that the other is atomic (with AUTOCOMMIT = 1) and their execution does not intersect with other requests. No additional blocking is required for them, the speed of queries with a unique `id` will be almost the same.
Locking is required if you first receive the balance (SELECT), then check the possibility of debiting funds, and only then write them off (UPDATE). In this case, you need to lock the table or row so that during the check there is no write-off of funds by another thread.