Explanation of mysql transaction select for update and data consistency processing

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

But this can cause problems in some situations: for example:

If you want to insert 1000 records at one time, MySQL will commit 1000 times.

If we turn off autocommit [autocommit = 0] and control it through the program, only one commit is needed, which can better reflect the characteristics of the transaction!

For operations that require numerical values, such as amount, number, etc.!

Remember one principle: lock first, judge second, and update third

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

There are two main types of read locks in SELECT:

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

When selecting from the same data table during a transaction, both methods must wait until other transaction data is committed before execution.

The main difference is that LOCK IN SHARE MODE can easily cause deadlock when one transaction wants to update the same form.

Simply put, if you want to UPDATE the same table after SELECT, it is best to use SELECT ... UPDATE.

For example:

Assume that there is a quantity in the product form products to store the quantity of the product. Before an order is placed, it is necessary to first determine whether the quantity of the product is sufficient (quantity>0), and then update 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 is it unsafe?

There may not be any problem in a small amount of cases, but there will definitely be problems with large amounts of data access. If we need to deduct inventory only when quantity>0, suppose the program reads the quantity of 2 in the first SELECT line. It seems that the number is correct, but when MySQL is about to UPDATE, someone may have already deducted the inventory to 0, but the program is unaware of it and continues the UPDATE without any error. Therefore, a transaction mechanism must be used to ensure that the data read and submitted are correct.

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

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

At this time, SELECT * FROM products WHERE id=3 FOR UPDATE . This ensures that the quantity number read by other transactions is correct.

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

Commit writes to the database and unlocks products.

• Note 1: BEGIN/COMMIT are the start and end points of a transaction. You can use two or more 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 other transactions to end before executing. Normal SELECT ... will not be affected by this.
• Note 3: Since InnoDB is set to Row-level Lock by default, please refer to this article for locking data columns.
• Note 4: Try not to use the LOCK TABLES command for InnoDB tables. If you have to use it, please read the official instructions for using LOCK TABLES with InnoDB first to avoid frequent deadlocks in the system.

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

The usage of SELECT ... FOR UPDATE is introduced above, but you need to pay attention to the judgment of locked data. Since InnoDB defaults to Row-Level Lock, MySQL will only perform Row lock (lock only the selected data) if the primary key is "explicitly" specified. Otherwise, MySQL will perform Table Lock (lock the entire data table).

For example:

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

Example 1: (Specify the primary key explicitly, 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 unclear, table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

Example 4: (Unclear primary key, table lock)

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

Optimistic and pessimistic locking strategies

Pessimistic lock: locks the rows when reading data, and other updates to these rows must wait until the pessimistic lock ends before continuing.

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links