problems with the SELECT * For Update lock table in MySQL
Since the InnoDB preset is row-level lock, MySQL will execute row lock only if the specified primary key is "clear" (only the selected data is locked), otherwise MySQL executes the table
Lock (locks 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 have this information, row Lock)
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)
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: (primary key ambiguous, table lock)
SELECT * FROM Products WHERE id<> ' 3 ' for UPDATE;
Example 4: (primary key ambiguous, table lock)
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.
Testing in MySQL 5.0 is exactly like this.
In addition: Myasim only support table-level lock, INNERDB support row-level lock added (row-level lock/table-level Lock) lock data can not be locked by other transactions, nor modified by other transactions (modify, delete)
is a table-level lock, the table is locked regardless of whether the record is queried
The range of the SELECT * For Update lock table in MySQL