MySQL lock mechanism--index failure leads to row lock variable table lock

Mysql Series Articles Home

===============

Tips: Before reading this article, it is best to read this article (MySQL lock mechanism-line lock) articles ~

In the previous article, we saw that the InnoDB default row lock makes it possible to operate on different rows without interacting and blocking, which solves the problem of multiple transactions and concurrency. However, it is based on the premise that the index is used in the Where condition, whereas if the index is not used, the full table is scanned and all blocks are blocked. This article illustrates this scenario with practical examples.

1 Preparing data

1.1 Build Table

 drop  table  if  exists   employee;  create  table  if  not  exists   employee (ID  int  primary  key   auto_increment, name /span>varchar  (40  Span style= "color: #000000"),  money  int
     

Note: ENGINE is INNODB (because INNODB only supports row locks)

1.2 Inserting data

INSERT  into  Money VALUES ('1001'10000); INSERT  into  Money VALUES ('1002'10000);

Tip: ' 1001 ' & ' 1002 ' is both a string and a number (used later)

2 Non-indexed scenarios

Up to now, the Employee table has not been indexed in the display.

2.1 Preparation

Still the same, two sessions (terminal), white background on the left, black background on the right, and all set autocommit = 0

2.2 Testing

2.2.1 Performing updates in the left-hand session

SQL statements:

UPDATE SET  Money =  Money + 10000 WHERE = ' 1001 ';

Results:

2.2.2 Performing updates in the right-side session

SQL statements:

UPDATE SET  Money =  Money +  the WHERE = ' 1002 ';

Result: As shown, the update operation is blocked!

2.2.3 Execute the COMMIT command on the left

Tip: When executing, look for changes to the right SQL statement execution

The SQL statement on the right side of the 2.2.4 executes normally, taking 18.19 seconds

2.2.5 the right side also executes the commit

2.2.6 to the left to see the data

2.2.7 to the right to see the data

Both sides have the same result and are correct.

But the problem is, the left side of the operation is the name = ' 1001 ' record and the right side of the operation is the name = ' 1002 ' record, the right or blocked, description, the left side of the update operation locked the full table! The reason for this is because there is no index on the name field!

2.3 Conclusion

When a field in the Where query condition has no index, the update operation locks the full table!

3 Condition of Index invalidation

Now to demonstrate the failure of the index, if the index fails, the premise must be indexed Ah, so, first build the index.

3.1 Building an Index

CREATE INDEX  on employee (name);

3.2 Row lock demo with indexed case

Hint: the same as the usual, two sessions (terminal), the left side is a white background, the right is a black background, and are set autocommit = 0

3.2.1 Performing updates in the left-hand session

SQL statements:

UPDATE SET  Money =  Money + 10000 WHERE = ' 1001 ';

Results:

3.2.2 Performing updates in the right-side session

SQL statements:

UPDATE SET  Money =  Money +  the WHERE = ' 1002 ';

Results:

Execute immediately and not be blocked!

3.2.3 the left and right sides to commit each

3.2.4 Left View Results

3.2.5 Right View results

Both sides have the same result and are correct.

3.2.6 Conclusion

As you can see, when there are indexes, different rows are updated, and the InnoDB default row locks are not blocked.

3.3 Row Lock demo in case of index failure

Hint: the same as the usual, two sessions (terminal), the left side is a white background, the right is a black background, and are set autocommit = 0

3.3.1 performing updates in the left-hand session

SQL statement: (Note: The 10,012 sides of name = 1001 do not have single quotes)

UPDATE SET  Money =  Money + 10000 WHERE = 1001;

Results:

3.3.2 performing updates in the right-side session

SQL statements:

UPDATE SET  Money =  Money +  the WHERE = ' 1002 ';

Results:

Be blocked! Note the left-hand session has locked the entire table!

3.3.3 to the left of execution (note the changes in SQL execution in the right session)

3.3.4 viewing the right session

The update operation in the right-side session is executed for 20.68 seconds

3.3.5 Right also submitted

3.3.6 Left View Results

3.3.7 Right View results

Both sides have the same result and are correct.

3.3.8 Conclusion

the query field in the Where condition has an index, but when the index fails (in this case the string does not have a single quotation mark), the InnoDB default row lock update operation becomes a table lock.

4 Conclusion

InnoDB row Lock table lock when no index or index is invalidated

Cause: Therow lock of Mysql is implemented by the index!

MySQL lock mechanism--index failure leads to row lock variable table lock