Select * for update lock table in MySQL

I have previously introduced the usage of SELECT... FOR UPDATE, but the locking data is a distinction. Because InnoDB defaults to Row-Level Lock, MySQL will only execute Row lock (only Lock the selected data example) If a specified Primary Key is specified explicitly ), otherwise, MySQL will execute Table Lock (Lock the entire data form ).

For example:

Assume that there is a form products, which contains two columns: id and name. id is the primary key.

Example 1: (specify the primary key and use this document, row lock)

SELECT * FROM products WHERE id = '3' for update;

Example 2: (specify a primary key. If no data is found, no lock is required)

SELECT * FROM products WHERE id = '-1' for update;

Example 2: (no primary key, table lock)

SELECT * FROM products WHERE name = 'mouse 'for update;

Example 3: (the primary key is not clear, table lock)

SELECT * FROM products WHERE id <> '3' for update;

Example 4: (the primary key is not clear, table lock)

SELECT * FROM products WHERE id LIKE '3' for update;

NOTE 1: for update only applies to InnoDB and must be in the transaction block (BEGIN/COMMIT.

NOTE 2: To test the locking status, you can use the Command Mode of MySQL to open two windows for testing.