How to use MySQL database select for update

Mysql SELECT FOR UPDATE
MySQL uses SELECT... FOR UPDATE to confirm before transaction writing
Take MySQL's InnoDB as an example. The default Tansaction isolation level is REPEATABLE READ. There are two main types of read locks in SELECT Method:
SELECT ... LOCK IN SHARE MODESELECT ... FOR UPDATE
These two methods must wait for other transaction data to be submitted (Commit) when SELECTing to the same data table is in progress. implement. The main difference is that LOCK IN SHARE MODE can easily cause deadlock when one party wants to update the same form.
Simply put, if you want to UPDATE the same form after SELECT, it is best to use SELECT ... UPDATE.
For example: Suppose there is a quantity in the products form that stores the quantity of the product. Before the order is established, it must be determined whether the quantity of the product is sufficient (quantity>0), and then the quantity is updated to 1.
Unsafe practice:
SELECT quantity FROM products WHERE id=3;
UPDATE products SET quantity = 1 WHERE id=3;
Why is it unsafe?
There may not be a problem in a small amount of cases, but a large amount of data is stored There will be problems if you choose "ironclad".
If we need to deduct inventory when quantity>0, assuming that the quantity read by the program in the first line of SELECT is 2, it seems that the number is correct, but when MySQL is preparing to UPDATE, someone may have already moved the inventory It was deducted to 0, but the program didn't know it and continued to update the error.
Therefore, a transaction mechanism must be used to ensure that the data read and submitted are correct.
So we can test like this in MySQL: (Note 1)
SET AUTOCOMMIT=0;
BEGIN WORK;
SELECT quantity FROM products WHERE id=3 FOR UPDATE;
============== ==============================
At this time, the data with id=3 in the products data is locked (Note 3), and the other The transaction must wait for this transaction to be committed before executing SELECT * FROM products WHERE id=3 FOR UPDATE (Note 2) This ensures that the number read by quantity in other transactions is correct. ===========================================
UPDATE products SET quantity = ' 1' WHERE id=3;
COMMIT WORK;
======================================== =====
Commit is written to the database and products are unlocked.
Note 1: BEGIN/COMMIT is the starting and ending point of the transaction. You can use more than two MySQL Command windows to interactively observe the locking status.
Note 2: During a transaction, only SELECT... FOR UPDATE or LOCK IN SHARE MODE for the same data will wait for the completion of other transactions before executing. Generally, SELECT... will not be affected by this.
Note 3: Since InnoDB defaults to Row-level Lock, please refer to this article for locking of data columns.
Note 4: Try not to use the LOCK TABLES command in InnoDB forms. If you have to use it, please read the official instructions on using LOCK TABLES in InnoDB first to avoid frequent deadlocks in the system.
Row Lock and Table Lock of MySQL SELECT ... FOR UPDATE
The usage of SELECT ... FOR UPDATE has been introduced above, but you have to pay attention to whether the locked (Lock) data is judged. Since InnoDB defaults to Row-Level Lock, MySQL will execute Row lock (only lock the selected data) only if the primary key is "clearly" specified. Otherwise, MySQL will execute Table Lock (lock the entire data form). ).
For example:
Suppose there is a form products, which has two fields: id and name, and id is the primary key.
Example 1: (Explicitly specify the primary key, and there is this data, row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;
Example 2: (Explicitly specify the primary key, if there is no such data, there is 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: (Unclear primary key, table lock )
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
Example 4: (Unclear primary key, table lock)
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
Note 1: FOR UPDATE only Applicable to InnoDB and must be in the transaction block (BEGIN/COMMIT) to take effect.
Note 2: To test the locking situation, you can use MySQL's Command Mode to open two windows for testing.

The above is the content of how to use MySQL database select for update. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!