In-depth research on MySQL row locks

The following articles mainly introduce you to the in-depth study of MySQL row locks. Today, when we were working on a project, due to the needs of business logic, we should add MySQL row locks to one or more rows of a data table. The simplest example is the book lending system. Assume that the inventory of this book with id = 1 is 1, but two people borrow this book at the same time. The logic here is,

 
 

  
  
view plaincopy to clipboardprint?  
  
  
Select restnum from book where id =1 ;   
 
 

If the value of restnum is greater than 0, update

 
 

  
  
Update book set restnumrestnum=restnum-1 where id=1 ;  
 
 

The problem arises. When two people borrow the data at the same time, it is possible that the first person executes the select statement and the second person inserts the data. When the first person does not have time to update the book table, the second person found the data, which is actually dirty data, because the first person will reduce the restnum value by 1, so the second person should have found that the restnum value of id = 1 is 0, therefore, the database will not execute update, but will tell it that the book with id = 1 is out of stock, but the database will only execute one SQL statement, no matter whether other SQL statements are inserted in the middle, it does not know whether to execute the SQL statement of one session before executing the SQL statement of another session.

Therefore, the final result of restnum during concurrency is-1, which is obviously unreasonable. Therefore, the concept of lock appears, mysql row locks use the innodb engine to lock data rows through indexes. The above borrow statement becomes:

 
 

  
  
view plaincopy to clipboardprint?  
  
  
Begin;   
  
  
Select restnum from book where id =1 for update ;   
 
 

Apply an exclusive lock to the row with id = 1 and the id has an index.

 
 

  
  
Update book set restnumrestnum=restnum-1 where id=1 ;   
  
  
Commit;  
 
 

In this way, the second person will wait until the first person executes the commit statement. This ensures that the second person does not read the data before the modification of the first person.

Is this safe? The answer is no. Let's look at the example below.

Follow me step by step to create a table first

 
 

  
  
view plaincopy to clipboardprint?  
  
  
CREATE TABLE `book` (   
  
  
`id` int(11) NOT NULL auto_increment,   
  
  
`num` int(11) default NULL,   
  
  
`name` varchar(0) default NULL,   
  
  
PRIMARY KEY (`id`),   
  
  
KEY `asd` (`num`)   
  
  
) ENGINE=InnoDB DEFAULT CHARSET=gbk   
 
 

The num field is indexed.

Then insert data and run,

 
 

  
  
view plaincopy to clipboardprint?  
  
  
insert into book(num) values(11),(11),(11),(11),(11);   
  
  
insert into book(num) values(22),(22),(22),(22),(22);   
 
 

Then open two mysql row lock console windows, which is actually to create two sessions for concurrent operations.

Run in the first session:

 
 

  
  
begin;   
  
  
select * from book where num=11 for update;  
 
 

Result:

 
 

  
  
+----+-----+------+   
  
  
| id | num | name |   
  
  
+----+-----+------+   
  
  
| 11 | 11 | NULL |   
  
  
| 12 | 11 | NULL |   
  
  
| 13 | 11 | NULL |   
  
  
| 14 | 11 | NULL |   
  
  
| 15 | 11 | NULL |   
  
  
+----+-----+------+   
  
  
5 rows in set  
 
 

Then run in the second session:

 
 

  
  
begin;   
  
  
select * from book where num=22 for update;  
 
 

Result:

 
 

  
  
+----+-----+------+   
  
  
| id | num | name |   
  
  
+----+-----+------+   
  
  
| 16 | 22 | NULL |   
  
  
| 17 | 22 | NULL |   
  
  
| 18 | 22 | NULL |   
  
  
| 19 | 22 | NULL |   
  
  
| 20 | 22 | NULL |   
  
  
+----+-----+------+   
  
  
5 rows in set  
 
 

Okay, there are no problems here, right, but the problem is coming. Please refer:

Return to the first session and run:

 
 

  
  
update book set name='abc' where num=11;  
 
 

The problem is that the session is in the waiting state, but isn't the row num = 11 locked by the first session itself? Why can't it be updated? Okay. You may have your own answer here. Don't worry. Please take a look at the operation.

Close both sessions and run:

 
 

  
  
view plaincopy to clipboardprint?  
  
  
delete from book where num=11 limit 3;   
  
  
delete from book where num=22 limit 3;   
 
 

In fact, it is to delete three rows of records with num = 11 and 22 respectively,

Then, repeat the operations **********************.

It turns out that after you run update book set name = 'abc' where num = 11;, a result is displayed, indicating that it is not locked,

Why? Is there two rows of data and five rows of Data? For MySQL, there will be two cases: Lock row and lock table. After discussing with netizens and reading the materials, we found that:

When the above experimental data is used as the test data, because the repetition rate of the num field is too high, there are only two values, 11 and 12, respectively. the data size is relatively large compared with the two values, which is 10, 5 times the relationship.

Mysql ignores the index when interpreting the SQL statement, because its optimizer finds that even if an index is used, it still needs to perform a full table scan. Therefore, the index is discarded, no row lock is used, but table lock is used. To put it simply, MYSQL ignores your index. It feels that it is better to directly lock tables than to lock rows, after all, it thinks that table locks cost less than MySQL row locks. Even if you use force index to force the index, the results are the same and the table lock will always be applied.

Therefore, the row locks of mysql are not so free to use, and indexes must be considered. Let's look at the example below.

 
 

  
  
view plaincopy to clipboardprint?  
  
  
select id from items where id in (select id from items where id <6) for update;   
  
  
 
 
 

-- The id field is indexed.

 
 

  
  
select id from items where id in (1,2,3,4,5) for update;  
 
 

Most people will think that there is no difference between the same results. In fact, there is a big difference. The difference is that the first SQL statement will generate a table lock, and the second SQL statement is a MySQL row lock. Why? Because the first SQL statement uses the subquery peripheral query, the index is not used, resulting in the table lock.

Well, back to the example of borrowing books, because the id is unique, there is no problem, but if some tables have duplicate indexes, and mysql will force the use of table locks, what should we do? Generally, you only need to redesign the table structure and use new SQL statements to implement the business logic. However, there is still a way to use the above example. See the following code:

 
 

  
  
View plaincopy to clipboardprint?
  
  
Set SQL _mode =
  
  
'Strict _ TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION ';
  
  
Begin;
  
  
Select restnum from book where id = 1; -- cancel the exclusive lock and set restnum to unsigned
  
  
Update book set restnumrestnumnum = restnum-1 where id = 1;
  
  
If (update execution successful) commit;
  
  
Else rollback;
 
 

The above is a tip. By temporarily setting the database mode to the strict mode, when restnum is updated to-1, the update will fail because restnum is of the unsigned type, no matter what database operations the second session performs, it will be rolled back to ensure data correctness, this is only intended to prevent dirty Data Reading caused by nested SQL statements of two sessions with a low probability of concurrency.

Of course, the best way is to modify the table structure and SQL statements so that MYSQL can apply MySQL row locks through indexes. MySQL test versions are 5.0.75-log and 5.1.36-community.