Another solution to the line lock for MySQL

Read an article on the Web: The original text reads as follows: *************************************************************************************** when doing the project due to business logic needs, the data table must be one row or more rows to join the row lock, for the simplest example, the book borrowing system. Suppose id=1 's book is in stock at 1, but there are 2 people at the same time borrowing the book, the logic here is Select restnum from book where ID =1; --if Restnum is greater than 0, perform update Update Book set restnum=restnum-1 where id=1; The question came, when2when individuals come to borrow at the same time, it is possible for the first person to performSelectstatement, the second person inserted in, in the first person did not have time to update Booktable, the second person found the data, is actually dirty data, because the first person willRestnumvalue minus1, so the second man was supposed to have foundid=1the bookRestnumto be, and therefore will not be executed.Update, and will tell itid=1The book is not in stock, but the database knows this, the database is only responsible for the implementation of theSQLstatement, it does not matter whether the middle there is no otherSQLThe statement was inserted, and it did not know to put aSessionof theSQLafter the statement executes anotherSessionthe. So it can cause concurrency.RestnumThe final result is-1and obviously it is unreasonable, so that only the concept of the lock appears,MysqlUseInnoDBthe engine can lock data rows through an index . The above-borrowed statements become: Begin; Select restnum from book where ID =1 for update; --Add an exclusive lock to the id=1 line and an ID index Update Book set restnum=restnum-1 where id=1; Commit; in this way, the second person executes to a Select statement, it waits until the first person executes Commit . This ensures that the second person does not read the data before the first person changes. ***************************************************************************************************** The article means to talk about the application of the row-level lock, but for the example above the scene (in fact, this scenario is often present, such as the second kill system, etc.) can be modified under the SQL statement. update Book set restnum=restnum-1 where Id=1 and restnum>0; As long as you add a restnum>0 after the original UPDATE statement, the problem can be resolved 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; Copy Code

