The mysql SELECT FOR UPDATE statement USES examples

Taking InnoDB of MySQL as an example, the preset Tansaction isolation level is REPEATABLE READ, and the read and lock of SELECT is mainly divided into two ways:
SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE
Both approaches must wait for other transaction data to be committed (Commit) when SELECT is in the middle of a transaction (Transaction) to the same data table. The main difference is that LOCK IN SHARE MODE is prone to deadlock when there is one transaction to Update and one form.
Simply put, if you want SELECT followed by UPDATE on the same form, you'd better use SELECT... UPDATE.
For example, if there is an quantity in the commodity form products that stores the quantity of goods, you must first determine whether the quantity of goods in quantity is sufficient (quantity) before placing an order > 0), and then update the quantity to 1.
Unsafe practices:

SELECT quantity FROM products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;
 

Why not?
A small number of cases may not be a problem, but a large number of data access "iron" will be a problem.
If we need to be at quantity > If SELECT reads quantity is 2 in line 1, it seems that the number is correct. However, when MySQL is about to ask for UPDATE, someone may have deducted UPDATE's inventory to 0, but the program does not know it, so UPDATE goes down.
Therefore, the transaction mechanism must be used to ensure that the data read and submitted are correct.
So we can test it at MySQL like this:

SET AUTOCOMMIT=0; BEGIN WORK; SELECT quantity FROM products WHERE id=3 FOR UPDATE;  

At this point, the data in products data id=3 is locked, and other transactions must wait for this transaction to commit before executing
SELECT * FROM products WHERE id=3 FOR UPDATE this ensures that quantity reads the correct number in other transactions.

UPDATE products SET quantity = '1' WHERE id=3 ; COMMIT WORK;

Commit (Commit) writes to the database and products unlocks.
Note 1: BEGIN/COMMIT are the start and end points of a transaction. You can use more than two MySQL Command Windows to interactively observe the status of the lock.
Note 2: in the middle of a transaction, only SELECT... FOR UPDATE or LOCK IN SHARE MODE will wait for the completion of other transactions before executing the same data. They are not affected by it.
Note 3: since InnoDB is preset to Row-level Lock, the locking of data columns can be referenced in this article.
Note 4: InnoDB form should not use LOCK TABLES instruction as much as possible. If it is necessary to use InnoDB, please read the official instructions on LOCK TABLES for InnoDB, so as not to cause frequent deadlock in the system.

MySQL SELECT... FOR UPDATE Row Lock and Table Lock
As mentioned above, SELECT... The use of FOR UPDATE, but the fact that the locked (Lock) data is a discriminant is worth paying attention to 1. Since InnoDB is Row-Level Lock by default, MySQL will only execute Row lock (locking only the selected data) with the "explicitly" specified primary key, otherwise MySQL will execute Table Lock (locking the entire data form).
Here's an example:
Let's say I have a form, products, that has id and name, and id is the primary key.
Example 1: (specify primary key and have this data, row lock)

 
SELECT * FROM products WHERE id='3' FOR UPDATE;
 

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

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: (unclear primary key, 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) to take effect.
Note 2: to test the condition of the lock, open two Windows using MySQL's Command Mode.
case

MySQL update && select
CREATE TABLE `testupdate` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `val` bigint(20) 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;