Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of mysql transaction select for update and data consistency processing, I believe that most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Transactions in MySQL are committed automatically by default, that is, autocommit = 1

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

If you want to insert 1000 pieces of data at once, mysql will commit1000 it.

If we turn off the autocommit [autocommit = 0] and control it through the program, we only need to commit once, so that we can better reflect the characteristics of the transaction!

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

Remember one principle: one lock, two judgments, three updates.

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

There are two main ways to read locks in SELECT:

SELECT... LOCK IN SHARE MODE

SELECT... FOR UPDATE

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

The main difference is that LOCK IN SHARE MODE can easily cause deadlocks when one of the transactions 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:

Suppose there is a quantity in the merchandise form products that stores the quantity of goods. Before the order is established, you must determine whether the quantity of the quantity item is sufficient (quantity > 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 situations may not be a problem, but a large amount of data access will definitely be a problem. If we need to deduct inventory in the case of quantity > 0, suppose the quantity read by the program in the first line of SELECT is 2, it looks like the number is not wrong, but when MySQL is preparing to UPDATE, someone may have deducted the inventory into 0, but the program has no idea that it will be wrong on the wrong UPDATE. Therefore, the transaction mechanism must be used to ensure that the data read and committed are correct.

So we can test it like this in MySQL, with the following code:

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

At this point, the id=3 data in the products data is locked (Note 3), and other transactions must wait for this transaction to commit before performing SELECT * FROM products WHERE id=3 FOR UPDATE, which ensures that the numbers read by quantity in other transactions are correct.

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

The Commit is written to the database and the 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 lock status interactively.

Note 2: in the middle of a transaction, only SELECT... FOR UPDATE or LOCK IN SHARE MODE with the same data will wait for the end of other transactions before execution, usually SELECT. Will not be affected by this.

Note 3: since InnoDB is preset to Row-level Lock, the locking of data columns can be referenced in this article.

Note 4: try not to use the LOCK TABLES instruction in the InnoDB form. If you have to use it, please read the official instructions for the use of LOCK TABLES in InnoDB, so as to avoid frequent deadlocks in the system.

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

It introduces SELECT. The use of FOR UPDATE, but you should pay attention to the discrimination of Lock data. Because the InnoDB preset is Row-Level Lock, MySQL will only execute Row lock (lock only the selected data) with an "explicit" primary key, otherwise MySQL will execute Table Lock (lock 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: (specify the primary key clearly and have 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 is not clear, table lock)

SELECT * FROM products WHERE id'3' FOR UPDATE

Example 4: (primary key is not clear, table lock)

SELECT * FROM products WHERE id LIKE'3' FOR UPDATE

Optimistic and pessimistic lock strategy

Pessimistic lock: lock those rows when reading data, and other updates to these rows need to wait until the pessimistic lock ends.

Optimistic: do not lock the data when reading, check whether the data has been updated when updating, if so, cancel the current update. Generally speaking, we will choose optimistic lock when the waiting time of pessimistic lock is too long to accept.

These are all the contents of the article "sample Analysis of mysql transaction select for update and data consistency processing". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.