MySQL InnoDB lock table with lock line

Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data sample) only if the specified primary key is "clear", otherwise MySQL will execute table lock (lock the entire data form).

For example: Suppose there is a form products with ID and name two fields, ID is the primary key.

Example 1: (explicitly specify the primary key, and there is this information, row lock)

Copy the Code code as follows:

SELECT * FROM Products WHERE id= ' 3 ' for UPDATE;
SELECT * FROM Products WHERE id= ' 3 ' and type=1 for UPDATE;

Example 2: (explicitly specify the primary key, if the information is not found, no lock)

Copy the Code code as follows:

SELECT * FROM Products WHERE id= '-1 ' for UPDATE;

Example 3: (No primary key, table lock)

Copy the Code code as follows:

SELECT * FROM Products WHERE name= ' Mouse ' for UPDATE;

Example 4: (primary key ambiguous, table lock)

Copy the Code code as follows:

SELECT * FROM Products WHERE id<> ' 3 ' for UPDATE;

Example 5: (primary key ambiguous, table lock)

Copy the Code code as follows:

SELECT * from the products WHERE ID like ' 3 ' for UPDATE;

Note 1:for Update applies only to InnoDB and must be in the transaction block (Begin/commit) to take effect.
NOTE 2: To test the condition of the lock, you can use the command Mode of MySQL and open two windows to do the test.

MySQL InnoDB lock table with lock line