Another solution to mysql row lock

I saw an article online:
The original text is as follows:
************************************ *************************************************** *

Due to the needs of business logic when working on a project, row locks must be added to one or more rows of the data table. The simplest example is the book lending system. Assume that the inventory of this book with id=1 is 1, but there are 2 individuals coming to borrow this book at the same time, the logic here is

Select restnum from book where id =1; --If restnum is greater than 0, execute update Update book set restnum=restnum-1 where id=1;

The problem arises. When 2 people come to borrow at the same time, it is possible that when the first person executes the select statement, the second person intervenes before the first person has time. When updating the book table, the second person found the data, which was actually dirty data, because the first person would decrement the restnum value by 1, so the second person should have The book id=1 is found restnum is , so it will not execute update, but will be told that the book id=1 is out of stock, But the database doesn’t understand this. The database is only responsible for executing one SQL statement. It doesn’t care whether there are other sql statements inserted in between. It doesn’t know to insert a session. After the sql statement is executed, another session will be executed. Therefore, when concurrency occurs, the final result of restnum is -1, which is obviously unreasonable. Therefore, the concept of locks appears. Mysql can use the innodb engine. Lock data rows through index . The above sentence for borrowing books becomes:

Begin; Select restnum from book where id =1 for update ; -- Add an exclusive lock to the row with id=1 and the id has an index Update book set restnum=restnum-1 where id=1; Commit;

In this way, when the second person executes the select statement, he will be in a waiting state until the first person executes commit. This ensures that the second person will not read the data before the first person modified it.
************************************************* *************************************************** **
The purpose of the article is to talk about the application of row-level locks, but for the scenario in the example above (in fact, this scenario still occurs often, such as flash sale systems, etc.), you can simply modify the SQL statement.
update book set restnum=restnum-1 where id=1 and restnum>0;
Just add restnum>0 after the original update statement, the problem can be solved

Begin;

Select restnum from book where id =1 for update ;
Update book set restnum=restnum-1 where id=1 ;

Commit;

update book set restnum=restnum-1 where id= 1 and restnum>0;