Mysql high concurrency locking transaction processing

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 MODE
SELECT … FOR UPDATE

These two methods must wait for other transaction data to be submitted (Commit) before executing when SELECTing to the same data table is in progress. 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 product form products to store 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 practices:

1    SELECT quantity FROM products WHERE id=3;    
1    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 access will "definitely" cause problems.

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 deducted the inventory to 0, but the program didn't know it and made the wrong UPDATE.

Therefore, the 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)

1    SET AUTOCOMMIT=0;    
2    BEGIN WORK;    
3    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), other transactions 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.

1    UPDATE products SET quantity = '1' WHERE id=3 ;    
2    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 the 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 data columns.

Note 4: Try not to use the LOCK TABLES instruction in InnoDB forms. If you have to use it as a last resort, please read the official instructions for using LOCK TABLES in InnoDB to avoid frequent deadlocks in the system.

The above is the content of Mysql high-concurrency locking transaction processing. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!