Database advanced Path (v)-MySQL row lock in-depth study

Because of the need for business logic, row locks must be added to one or more rows of the data table, for the simplest example, the book borrowing system: Suppose Id=1 's book is in stock at 1, but there are 2 people who come to borrow the book at the same time, the logic here is:

SELECT   from WHERE = 1  ;  -- if Restnum is greater than 0, perform an update UPDATE  SET restnum=restnum-1WHERE id=1;

The problem comes, when 2 people borrow at the same time, it is possible that the first person to execute the SELECT statement, the second person inserted in, the first person did not have time to update the Book table, the second person to find the data, but this is a dirty data, because the first person will be the Restnum value minus 1, So the second person should have been found id=1 book Restnum is 0, so will not perform the update, but will tell it id=1 book is not in stock, but the database knows this, the database is only responsible for executing a number of SQL statements, it does not matter whether there are other SQL statements in the middle of the inserted, It does not know that you want to execute another session after executing the SQL statement of the session. This results in concurrency when the restnum final result is-1, which is obviously unreasonable, so there is a lock concept, and MySQL uses the InnoDB engine to lock data rows through an index. The above-borrowed statement becomes:

BEGIN ; SELECT  from WHERE = 1  for UPDATE -- add an exclusive lock to id=1 rows and an ID index UPDATE  SET restnum=restnum-1WHERE  id=1; Commit;

This way, the second person executes to the SELECT statement and waits until the first person executes the commit. This ensures that the second person does not read the data before the first person changes. That's not foolproof, the answer is no. Look at the following example.

Follow me one step at a table, where the NUM field is indexed

CREATE TABLE ' Book' (  'ID' INT( One) not NULLAuto_increment,'Num' INT( One)DEFAULT NULL,  'name' VARCHAR(0)DEFAULT NULL,  PRIMARY KEY('ID'),  KEY 'ASD'('Num') ) ENGINE=InnoDBDEFAULTCHARSET=Gbk

Then insert the data, run

INSERT  into VALUES (one), (one), (one), (one), (one);  INSERT  into VALUES (a), (a), (a), (a), (a);

Then open 2 MySQL console windows, in fact, the establishment of 2 sessions to do concurrent operations

--------------------------------------------------------------------------

Run in the first session:

BEGIN ; SELECT *  from WHERE num=one forUPDATE;

Results appear:

 |Id|Num|Name|    |  One |  One   | NULL |    |  A |  One   | NULL |    |  - |  One   | NULL |    |  - |  One   | NULL |   |  the |  One   | NULL |   5Rowsinch Set

Then run in the second session:

BEGIN ; SELECT *  from WHERE num=forUPDATE;

Results appear:

|Id|Num|Name| |  - |  A | NULL | |  - |  A | NULL |  |  - |  A | NULL |  |  + |  A | NULL |  |  - |  A | NULL | 5Rowsinch Set

Okay, there's nothing wrong here, right, but the next question is coming, everybody, look: Back to the first session, run:

UPDATE SET Name='abc'WHERE num=one;

--------------------------------------------------------------------------

The question came, the session unexpectedly in wait state, but Num=11 's line is not the first session oneself lock, why can't update? OK, here you may have their own answers, don't worry, please look at the operation.
Close all 2 sessions and run:

 delete  from  book where  num=  11  LIMIT 3   delete  from  book =  22  LIMIT 3 ; 

In fact, num=11 and 22 of the records are deleted to 3 lines, and then repeat the operation between the split line unexpectedly found, run update book set name= ' abc ' where num=11, after the results appear, the explanation is not locked, this is why, 2 rows of data and 5 rows of data, for MySQL, will produce a lock row and lock table two cases. After discussing and flipping through the data with netizens, after careful analysis, it is found that: in the case of the above experimental data as test data, because NUM field repetition rate is too high, there are only 2 values, respectively 11 and 12. And the amount of data relative to these two values is relatively large, is 10, 5 times times the relationship. Then MySQL ignores the index when interpreting SQL, because its optimizer finds that, even if the index is used, the full table scan is done, the index is discarded, the row lock is not used, and the table lock is used. Simply put, MySQL ignores your index, it feels that it is not as good as a direct table lock, after all, it feels that the cost of table lock is smaller than the row lock. The above problem even if you use the force index to enforce indexing, the result is the same, always a table lock. So the MySQL row lock is not so arbitrary and must be indexed. Let's look at the following example:

SELECTId fromItemsWHEREIdinch(SELECTId fromItemsWHEREId&Lt6) for UPDATE;--ID field IndexedSELECTId fromItemsWHEREIdinch(1,2,3,4,5) for UPDATE;

Most will think the result is no different, in fact the difference is big, the difference is that the first SQL statement will produce a table lock, and the second SQL statement is a row lock, why? Because the first SQL statement uses the subquery perimeter query, the index is not used, resulting in a table lock.

Well, back to the borrowing example, because the ID is unique, so there is no problem, but if some of the table has a duplicate index, and MySQL will force the use of table locks, what to do? In general, only the redesign of the table structure and the use of new SQL statements to implement business logic, but in fact, the above examples of borrowing is another way. Take a look at the following code:

SETSql_mode='strict_trans_tables,no_auto_create_user,no_engine_substitution';BEGIN;SELECTRestnum fromBookWHEREId=1;--Cancel exclusive lock, set Restnum to unsignedUPDATEBookSETRestnum=Restnum-1 WHERE  ;IF(Update execution succeeded)Commit;ELSE  ROLLBACK;

This is a trick, by temporarily setting the database schema to strict mode, when Restnum is updated to-1, because Restnum is a unsigned type, the update fails, no matter what database operation the second session does, it will be rolled back. This ensures the correctness of the data, which is only to prevent concurrency when the minimum probability of the occurrence of the 2 session SQL statement nested execution results in data dirty read. Of course, the best way is to modify the table structure and SQL statements, so that MySQL through the index to add a row of locks. The MySQL beta version is 5.0.75-log and 5.1.36-community.

Database advanced Path (v)-MySQL row lock in-depth study