Analyze Mysql transactions and data consistency processing issues

This article analyzes the consistency processing of MySQL transactions and data in detail through security, usage, concurrent processing, etc. The following is the full content:

At work, we often encounter such problems. We need to update inventory. When we query the available inventory and prepare to modify it, other users may have modified the inventory data. As a result, there will be problems with the data we queried. Let's take a look at the solution.

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

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, the data with id=3 in the 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 the quantity number read in 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 execution. 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.

More advanced usage

If we need to query first and then update the data, it is best to use the statement like this:

UPDATE products SET quantity = '1' WHERE id=3 AND quantity > 0;

This way, you can process it without adding things.

MySQL handles high concurrency to prevent inventory overselling

I saw a very good article and shared it here for study.

Today, Mr. Wang taught us another lesson. Actually, Mr. Wang mentioned last year how to handle high concurrency in MySQL and prevent overselling of inventory. But unfortunately, even though everyone understood it at that time, there is still no awareness of this in actual development. Today I would like to sort out this issue based on my understanding, and I hope there will be more courses like this in the future.

Let's first describe the problem of oversold inventory: Generally, e-commerce websites will encounter activities such as group buying, flash sales, and special offers. A common feature of such activities is a surge in visits, with thousands or even tens of thousands of people rushing to buy a product. However, as a promotional product, the inventory is definitely limited. How to control the inventory to prevent overbuying and prevent unnecessary losses is a headache for many e-commerce website programmers, and it is also the most basic problem.

From a technical analysis, many people will definitely think of transactions, but transactions are a necessary condition for controlling inventory overselling, but not a sufficient and necessary condition.

Example:

Total inventory: 4 items

Requester: a, 1 product b, 2 products c, 3 products

The procedure is as follows:

beginTranse (start a transaction)
try{
 $result = $dbca->query('select amount from s_store where postID = 12345');
 if(result->amount > 0){
  //quantity is the inventory quantity requested to be reduced$dbca->query('update s_store set amount = amount - quantity where postID = 12345');
 }
}catch($e Exception){
 rollBack
}
commit (commit transaction)

The above code is what we usually write to control inventory. Most people will write it this way. It seems that there is no big problem, but it actually hides huge loopholes. Accessing a database is actually accessing disk files. Tables in a database are actually files stored on disk, and one file may even contain multiple tables. For example, due to high concurrency, three users A, B, and C have entered this transaction. At this time, a shared lock will be generated. Therefore, when selecting, the inventory quantity found by these three users is 4. At the same time, it should be noted that the results found by MySQL InnoDB are version controlled. Before other users update and commit (that is, before a new version is generated), the result found by the current user is still the old version.

Then comes update. If the three users arrive at the update at the same time, the update statement will serialize the concurrency, that is, the three users who arrive here at the same time will be sorted, executed one by one, and an exclusive lock will be generated. Before the current update statement is committed, other users wait for execution. After commit, a new version is generated. After execution, the inventory will definitely be negative. However, according to the above description, we can modify the code to avoid overbought phenomenon. The code is as follows:

beginTranse (start a transaction)
try{
 //quantity is the inventory quantity requested to be reduced$dbca->query('update s_store set amount = amount - quantity where postID = 12345');
 $result = $dbca->query('select amount from s_store where postID = 12345');
 if(result->amount < 0){
  throw new Exception('Insufficient inventory');
 }
}catch($e Exception){
 rollBack
}
commit (commit transaction)

Alternatively, a more concise method:

beginTranse (start a transaction)
try{
 //quantity is the inventory quantity requested to be reduced$dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');
}catch($e Exception){
 rollBack
}
commit (commit transaction)

1. In the case of flash sales, you cannot read and write the database at such a high frequency, which will seriously cause performance problems.

You must use a cache to put the products that need to be flash sold into the cache, and use a lock to handle concurrency. When receiving a flash sale order from a user, the quantity of the product will be decremented (locked/unlocked) before other processing. If the processing fails, the data will be incremented by 1 (locked/unlocked). Otherwise, the transaction is successful.
When the number of products decreases to 0, it means that the product is sold out and other users' requests are rejected.

2. This certainly cannot operate the database directly, it will fail. Directly reading and writing to the database puts too much pressure on the database, so cache is required.

Put the products you want to sell, for example 10 products, into the cache; then set a counter in memcache to record the number of requests. You can use the number of products you want to sell as the base number for this request book. For example, if you want to sell 10 products, only 100 requests are allowed to come in. Then when the counter reaches 100, the subsequent orders will be shown as ending the flash sale, which can reduce the pressure on your server. Then, based on these 100 requests, first come first served and last paid reminder products are sold out in seconds.

3. First of all, when multiple users modify the same record concurrently, the user who submits later will definitely overwrite the result submitted by the former.

This can be solved directly by using a locking mechanism, optimistic locking or pessimistic locking.

Optimistic locking: Design a version number field in the database, and increase it by 1 each time it is modified. In this way, when submitting, you can compare the version number before submission to know whether it is a concurrent submission. However, there is a disadvantage that it can only be controlled in the application. If the same data is modified across applications, optimistic locking will not work. At this time, you can consider pessimistic locking.

Pessimistic locking: It is to lock the data directly at the database level, similar to using select xxxxx from xxxx where xx=xx for update in oralce, so that other threads will not be able to submit data.

In addition to the locking method, you can also use the receiving locking method. The idea is to design a status flag in the database. Before the user modifies the data, the status flag is marked as being edited. In this way, when other users want to edit this record, the system will find that other users are editing it and reject their editing requests. It is similar to when a file is being executed in the operating system and you want to modify the file, the system will remind you that the file cannot be edited or deleted.

4. It is not recommended to lock at the database level. It is recommended to use the server-side memory lock (lock the primary key). When a user wants to modify the data of a certain id, the id to be modified is stored in memcache. If other users trigger the modification of the data of this id and read the value of this id in memcache, the user will be prevented from modifying it.

5. In actual applications, MySQL is not required to directly deal with large concurrent read and write operations. Instead, it uses "external forces" such as caching, using master-slave libraries to separate reads and writes, partitioning tables, and using queue writing to reduce concurrent read and write operations.

Pessimistic locking and optimistic locking

First of all, when multiple users modify the same record concurrently, the result submitted by the user who submitted it later will definitely overwrite the result submitted by the user who submitted it earlier. This can be solved directly by using a locking mechanism, optimistic locking or pessimistic locking.

Pessimistic Lock, as the name suggests, is very pessimistic. Every time you get data, you think that others will modify it, so you will lock it every time you get data. In this way, others who want to get the data will be blocked until they get the lock. Many such lock mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, write locks, etc., which are all locked before performing operations.

Optimistic Lock, as the name suggests, is very optimistic. Every time you get data, you assume that others will not modify it, so you will not lock it. However, when updating, you will determine whether others have updated the data during this period. You can use mechanisms such as version numbers. Optimistic locking is suitable for multi-read application types, which can improve throughput. For example, if the database provides a mechanism similar to write_condition, it actually provides optimistic locking.

Both locks have their own advantages and disadvantages, and it is impossible to simply define which one is better than the other. Optimistic locking is more suitable for scenarios where data is rarely modified but read frequently. Even if a small number of conflicts occur, a large amount of lock overhead is saved, thereby improving the system throughput. However, if conflicts occur frequently (when writing a lot of data), the upper-layer application will keep retrying, which will reduce performance. In this case, it is more appropriate to use pessimistic locking.

Actual Combat

Modify the amount of this table and open two command line windows

The first window is A;

SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;

Second window B:

# Update the inventory quantity of order ID 124 UPDATE `order_tbl` SET amount = 1 WHERE order_id = 124;

We can see that window A adds a transaction and locks the data. When window B is executed, the following problem occurs:

The first window completes the submission transaction:

SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;
UPDATE `order_tbl` SET amount = 10 WHERE order_id = 124;
COMMIT WORK;

MySQL handles high concurrency and prevents inventory overselling. The above is the full content of this article. If you still have any questions, you can leave a message below for discussion.