MySQL uses Select ... The for UPDATE confirms before the transaction is written (go)

Select ... The syntax for the FOR UPDATE statement is the same as the SELECT statement, except that the for UPDATE [NOWAIT] clause is appended to the SELECT statement.

The statement is used to lock a specific row, if there is a WHERE clause, which satisfies the where condition. When these rows are locked, other sessions can select the rows, but the rows cannot be changed or deleted until the statement's transaction is terminated by a commit statement or a ROLLBACK statement.

MySQL uses Select ... The for UPDATE confirms before the transaction is written

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:

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: (note 1)

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 id=3 for UPDATE (note 2) This ensures that the numbers quantity read in other transactions are correct. ===========================================

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)

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

Example 2: (explicitly specify the primary key, if the data 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.

MySQL uses Select ... The for UPDATE confirms before the transaction is written (go)