MySQL SELECT for UPDATE statement usage detailed

In the case of MySQL InnoDB, the preset tansaction isolation level is repeatable read, and there are two main ways to read locks in a select:

SELECT ... LOCK in SHARE MODE SELECT ... For UPDATE

Both of these methods must wait for the other transaction data to be committed (commit) before it is executed when the select to the same data table in a transaction (Transaction). The main difference is that lock in SHARE MODE can easily cause deadlocks when one transaction is to update the same form.

Simply put, if you want to update the same form after the Select, it's best to use Select ... UPDATE.

For example: Suppose there is a quantity of the quantity of merchandise in the product form products, before the order is set up, it must determine whether the quantity quantity is sufficient (quantity>0) before the quantity is updated to 1.

Unsafe practices:

The code is as follows	Copy Code
SELECT quantity from the products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;

Why isn't it safe?

A small amount of the situation may not be a problem, but a large number of data access "ironclad" will be problematic.

If we need to quantity>0 in order to withhold inventory, assuming that the program in the first line of select Read the quantity is 2, it appears that the number is not wrong, but when MySQL is ready to update, may have someone to the inventory into 0, But the program is unaware, mistake update went down.

Therefore, the transaction mechanism must be through to ensure that the data read and submitted are correct.

So we can test this in MySQL: (note 1)

The code is as follows	Copy Code
SET autocommit=0; BEGIN WORK; SELECT quantity from the products WHERE id=3 for UPDATE;

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

The id=3 data in the products data is locked (note 3), and other transactions must wait for the transaction to be committed before it can be executed

SELECT * from the products WHERE id=3 A for UPDATE (note 2) ensures that the number read by quantity is correct in other transactions. ===========================================

The code is as follows	Copy Code
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 for the transaction, and you can use more than two MySQL Command windows to interactively observe the locked condition.

Note 2: In the course of the transaction, only select ... For UPDATE or lock in SHARE MODE the same data will wait for the other transaction 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 has to be used, please see the official for InnoDB use lock tables, so as not to cause the system often deadlock.

MySQL SELECT ... Row lock and table lock for UPDATE

The above Introduction Select ... For UPDATE usage, but locking (lock) data is discriminant and must be noted. Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data is locked), otherwise MySQL will execute table lock (lock the entire data form) with a "clear" specified primary key.

As an example:

Suppose you have a form products with IDs and name two fields, and ID is the primary key.

Example 1: (explicitly specifying the primary key and having this data, row lock)

The code is as follows	Copy Code
SELECT * from the products WHERE id= ' 3 ' for UPDATE;

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

The code is as follows	Copy Code
SELECT * from the products WHERE id= '-1 ' for UPDATE;

Example 2: (No primary key, table lock)

The code is as follows	Copy Code
SELECT * from the products WHERE name= ' Mouse ' for UPDATE;

Example 3: (primary key ambiguous, table lock)

The code is as follows	Copy Code
SELECT * from the products WHERE id<> ' 3 ' for UPDATE;

Example 4: (primary key ambiguous, table lock)

The code is as follows	Copy Code
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 status of the lock, you can use MySQL's command Mode to open two windows for testing.

Cases

code is as follows	copy code
MySQL Update && Select   CREATE TABLE ' testupdate ' (   ' id ' bigint () not NULL AUTO_INCREMENT,   ' Val ' bigint 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: = VA l+1;   Select @value;