Mysql select for update statement example

Taking InnoDB of MySQL as an example, the preset Tansaction isolation level is repeatable read, and the READ locks in SELECT are mainly divided into two methods:
SELECT... lock in share mode select... FOR UPDATE
These two methods will be executed only after the SELECT statement is executed in the Transaction to the same data table. The major difference is that lock in share mode can easily cause deadlocks when a transaction wants to Update the same form.
To put it simply, if you want to UPDATE the same form after SELECT, you 'd better use SELECT... UPDATE.
For example, assume that the product form products contains a quantity that stores the quantity of commodities. before the order is established, you must first determine whether the quantity of the quantity commodities is sufficient (quantity> 0 ), then update the quantity 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 is it insecure?
In a few cases, there may be no problem, but a large amount of data access may be wrong.
If we need to deduct the inventory only when the quantity is greater than 0, assuming that the quantity read by the program in the first line of SELECT is 2, it seems that the number is correct, however, when MySQL is preparing to UPDATE, some people may have deducted the inventory to 0, but the program did not know how to UPDATE the wrong one.
Therefore, the transaction mechanism must be used to ensure that the data read and submitted is correct.
So we can test in MySQL as follows:
Copy codeThe Code is as follows:
Set autocommit = 0; begin work; SELECT quantity FROM products WHERE id = 3 for update;

In this case, the data with id = 3 in products data is locked (note 3), and other transactions must wait for the transaction to be committed before execution.
SELECT * FROM products WHERE id = 3 for update so that the number read by quantity in other transactions is correct.
Copy codeThe Code is as follows:
UPDATE products SET quantity = '1' WHERE id = 3; commit work;

Commit is written to the database, and products is unlocked.
Note 1: BEGIN/COMMIT is the start and end point of the transaction. You can use more than two MySQL Command windows to observe the locking status.
NOTE 2: During the transaction, only SELECT... for update or lock in share mode, the same data will be executed only after other transactions are completed. Generally, SELECT... this is not affected.
NOTE 3: Because InnoDB defaults to Row-level Lock, you can refer to this article for data column locking.
Note 4: Do not use the lock tables command for InnoDB forms. If you have to use the lock tables command, read the official instructions on InnoDB to avoid frequent system deadlocks.

MySQL SELECT... for update Row Lock and Table Lock
The usage of SELECT... for update has been introduced above, but the data of Lock must be distinguished. Because InnoDB defaults to Row-Level Lock, MySQL will only execute Row lock (only Lock selected data) If a specified Primary Key is specified explicitly ), otherwise, MySQL will execute Table Lock (Lock the entire data form ).
For example:
Assume that there is a form products, which contains two columns: id and name. id is the primary key.
Example 1: (specify the primary key with this data, row lock)
Copy codeThe Code is as follows:
SELECT * FROM products WHERE id = '3' for update;
 
Example 2: (specify a primary key. If no data is found, no lock is available)
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: (the primary key is not clear, table lock)
Copy codeThe Code is as follows:
SELECT * FROM products WHERE id <> '3' for update;
 
Example 4: (the primary key is not clear, table lock)
Copy codeThe Code is as follows:
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.
NOTE 2: To test the locking status, you can use the Command Mode of MySQL to open two windows for testing.
Example
Copy codeThe Code is as follows:
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;