MySQL SELECT for UPDATE statement using the example

With MySQL InnoDB as an example, the preset tansaction isolation level is repeatable read and the read lock in select is divided into two main ways:
SELECT ... LOCK in SHARE MODE SELECT ... For UPDATE
Both of these methods must wait for the other transaction data to be committed (commit) when the transaction (Transaction) is in between select and the same data table. The main difference is that lock in SHARE MODE is prone to deadlock when one of the transactions is to update the same form.
Simply put, if you want to update the same form after select, it's best to use Select ... UPDATE.
For example, suppose that the product list contains a quantity of the number of items in the product, before the order is established, it is necessary to determine whether the quantity is sufficient (quantity>0) before the quantity is updated to 1.
Unsafe practices:

Copy CodeThe code is as follows:
SELECT Quantity from Products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;

Why isn't it safe?
A small number of situations may not be a problem, but a lot of data access "definitely" will be problematic.
If we need to quantity>0 in the case of inventory, assuming that the program in the first select read to the quantity is 2, it seems that the number is not wrong, but when MySQL is ready to update, there may have been the inventory has been deducted 0, But the program was unaware that will wrong's update went on.
Therefore, the transaction mechanism must be through to ensure that the data read and submitted are correct.
So we can test it in MySQL:

Copy CodeThe code is as follows:
SET autocommit=0; BEGIN work; SELECT Quantity from Products WHERE id=3 for UPDATE;

Id=3 data in the products data is locked (note 3), other transactions must wait for the transaction to be committed before execution
SELECT * FROM Products where the id=3 for UPDATE is so sure that the numbers quantity read in other transactions are correct.

Copy CodeThe code is as follows:
UPDATE products SET quantity = ' 1 ' WHERE id=3; COMMIT work;

Commit (Commit) write to database, products unlock.
Note 1:begin/commit is the starting and ending point of a transaction, you can use more than two MySQL Command windows to interactively observe the condition of the lock.
Note 2: In the midst of a transaction, only select ... For UPDATE or lock in SHARE MODE the same pen data will wait for other transactions to finish before executing, general Select ... is not affected by this.
Note 3: Because the InnoDB preset is row-level lock, data column locking can refer to this article.
Note 4:innodb form try not to use the lock TABLES instructions, if the situation must be used, please first look at the official InnoDB use lock TABLES instructions, so as not to cause the system often deadlock.

MySQL SELECT ... Row lock with table lock for UPDATE
The above describes the select ... The use of for UPDATE, but the lock data is a discriminant, you have to pay attention to. Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data is locked) only if the specified primary key is "clear", otherwise MySQL will execute table lock (lock the entire data form).
As an example:
Suppose there is a form of products with ID and name two fields, ID is the primary key.
Example 1: (explicitly specifying a primary key and having this data, row lock)

Copy CodeThe code is as follows:
SELECT * FROM Products WHERE id= ' 3 ' for UPDATE;

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

Copy CodeThe code is as follows:
SELECT * FROM Products WHERE id= '-1 ' for UPDATE;

Example 2: (No primary key, table lock)

Copy CodeThe code is as follows:
SELECT * FROM Products WHERE name= ' Mouse ' for UPDATE;

Example 3: (primary key ambiguous, table lock)

Copy CodeThe code is as follows:
SELECT * FROM Products WHERE id<> ' 3 ' for UPDATE;

Example 4: (primary key ambiguous, table lock)

Copy CodeThe code is 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.
Cases

Copy CodeThe code is as follows:

MySQL Update && Select
CREATE TABLE ' Testupdate ' (
' ID ' bigint (a) not NULL auto_increment,
' Val ' bigint (a) not NULL DEFAULT ' 0 ',
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=2 DEFAULT Charset=utf8
Update Testupdate
Set val = val+1
where id = 1 and @value: = val+1;
Select @value;

Original: http://www.jb51.net/article/42778.htm

MySQL SELECT for UPDATE statement using the example