Explanation of mysql transaction select for update and data consistency processing

Transactions in MySQL are committed automatically by default, i.e. autocommit = 1;

But in this case, there will be problems in some situations: for example:

If you want to insert 1000 pieces of data once, mysql will commit 1000 times.

If we turn off autocommit [autocommit = 0], through the program to control, as long as one commit is OK, so we can better reflect the characteristics of transactions!

For the need to operate values, such as amount, number and so on!

Remember one principle: 1 lock, 2 judgments and 3 updates

In InnoDB of MySQL, the default Tansaction isolation level is REPEATABLE READ (rereadable)

There are two main ways to read locking in SELECT:

SELECT... LOCK IN SHARE MODE SELECT ... FOR UPDATE

Both methods must wait for other transaction data to be committed (Commit) before executing when SELECT goes to the same data table during the transaction (Transaction).

The main difference is that LOCK IN SHARE MODE is prone to deadlock when there is a transaction that requires Update to be the same form.

Simply put, if you want UPDATE to be the same form after SELECT, you'd better use SELECT... UPDATE.

For example:

Assuming that there is an quantity in the item form products for storing the item quantity, you must first determine whether the item quantity of quantity is sufficient (quantity) before the order is established > 0) before updating the quantity to 1. The code is as follows:

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

Why isn't it safe?

A small number of cases may not be a problem, but a large amount of data access will definitely be a problem. If we need quantity > The inventory can only be deducted under the condition of 0. Assuming that the quantity read by the program in line 1 SELECT is 2, it seems that there is nothing wrong with the number, but when MySQL is preparing for UPDATE, someone may have deducted the inventory to 0, but the program is unaware of it and will go down the wrong UPDATE. Therefore, the transaction mechanism must be adopted to ensure that the read and submitted data are correct.

So we can test this in MySQL, and the code is as follows:

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

In this case, the data of id=3 in products data is locked (Note 3), and other transactions must wait for this transaction to be committed before they can be executed SELECT * FROM products WHERE id=3 FOR UPDATE This ensures that quantity reads the correct numbers in other transactions.

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

Commit (Commit) to the database, and products unlocks.

Note 1: BEGIN/COMMIT are the start and end points of the transaction, and more than two MySQL Command windows can be used to interactively observe the locking status. Note 2: During a transaction, only SELECT... FOR UPDATE or LOCK IN SHARE MODE will wait for other transactions to finish before executing, while SELECT... will not be affected by this. Note 3: Since InnoDB is default to Row-level Lock, the data column locking can be referred to in this article. Note 4: Try not to use LOCK TABLES instruction in InnoDB form. If you have to use it, please read the official instructions for InnoDB to use LOCK TABLES first, so as to avoid frequent deadlock in the system.

MySQL SELECT... FOR UPDATE Row Lock and Table Lock

As described above SELECT ... FOR UPDATE The usage of, but lock (Lock) data is discriminant have to pay attention to 1 below. Since InnoDB is Row-Level Lock by default, MySQL will execute Row lock (locking only the selected data) only if the "explicit" primary key is specified, otherwise MySQL will execute Table Lock (locking the entire data form).

For example:

Suppose you have a form products with two fields, id and name, and id is the primary key.

Example 1: (The primary key is explicitly specified and there is this data, row lock)

SELECT * FROM products WHERE id='3' 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 products WHERE id LIKE '3' FOR UPDATE;

Optimism and pessimism lock strategy

Pessimistic lock: Lock those rows when reading data, and other updates to these rows cannot continue until the pessimistic lock ends.

Optimism: Do not lock when reading data, check whether the data has been updated when updating, and cancel the current update if so. Generally, when the waiting time of pessimistic lock is too long and unacceptable, we will choose optimistic lock.

Summarize