A recent test bug put it in me where I had previously provided an interface for bulk copies of products that produced duplicate product data.

After tracing the reason, it was found that this matter was not as simple as imagined, and it could be said that there were many twists and turns.

The product has a demand: the user selects some brands, clicks the OK button, and the system needs to copy a batch of new products based on a default brand product data.

When I got this demand, I thought it was too simple, and I would get it done in three strokes and five divided by two.

I provide a basic interface for copying products and calling the marketplace system.

The flowchart at that time was as follows: if the number of goods copied each time was not large, the scheme called by using the synchronization interface was not very problematic.

However, due to the large number of goods that need to be copied at a time, there may be several thousand.

If you copy the goods using the synchronous interface every time, there may be performance problems.

Therefore, later I changed the logic of copying goods to use mq asynchronous processing.

Flowchart after the renovation: The result of copying the product also needs to notify the mall system: this scheme looks very good.

But then something went wrong.

The test bug put it on us, saying that an interface I provided earlier to copy products in bulk produced duplicate product data.

After investigation, it was found that the mall system was also changed to asynchronous for performance considerations.

They do not call the underlying system’s duplicate commodity interface directly in the interface, but rather in the job.

From their point of view, the flowchart is like this: the user calls the interface of the mall, they will write a piece of data to the request record table, and then in another job, asynchronously call the interface of the underlying system to copy the goods.

But the reality is this: there is a bug inside the marketplace system, and in the request log table, the same request produces duplicate data. The result of this is that duplicate requests are sent when the underlying system copies the commodity interface is called in the job.

It just so happens that the underlying system is now using RocketMQ for asynchronous processing. Since the job of the mall will take a batch of data (for example: 20 records) at a time, and call the interface multiple times in a very short period of time (in fact, in a for loop), there may be a situation where the same request parameters continuously call the copy commodity interface. As a result, the problem of concurrently inserting duplicate data arises.

Why is this problem?

Consumers of RocketMQ, for performance reasons, default to multi-threaded concurrent consumption, with a maximum support of 64 threads.

For example:

That is, if duplicate messages are sent continuously in a very short period of time, they will be consumed by different threads.

Even in the code there is such a judgment:

Before inserting data, determine whether the data already exists, and only if it does not exist, it will be inserted.

However, in the case of concurrency, different threads judge that the product data does not exist, so the insertion operation is carried out at the same time, so duplicate data is generated.

This is shown in the following figure:

In order to solve the problem of concurrent consumption of duplicate messages above, we start from two aspects:

I thought about it carefully, if you only rely on the mall system to fix the bug, it is difficult to avoid similar duplicate product problems in the future, such as: if the user clicks the Create Product button multiple times in a very short period of time, or the mall system initiates a retry.

Therefore, the basic system needs to be further processed.

In fact, RocketMQ itself supports sequential consumption, which needs to be changed by the producer and the consumer of the message.

The producer read :

The point is to call the asyncSendOrderly method of the rocketMQTemplate object to send sequential messages.

Consumers have been replaced by:

The focus of receiving messages is the consumeMode parameter in the RocketMQMessageListener annotation, which is set to ConsumeMode.ORDERLY so that messages can be consumed sequentially.

The key flowchart after modification is as follows:

After both sides are modified, there is no longer a problem of duplicate products in the copy product.

But, after fixing the bug, I thought about it for a long time.

Copying a product is just one of the entrances to creating a product, if there are other portals, what about creating a new item at the same time as the Copy Item feature?

Won’t duplicate items also arise?

Although, this probability is very, very small.

However, once there is a problem of duplicate products, it is very troublesome to follow up with the data to be merged with the products.

After this lesson, we must guard against microaggressions.

Whether it is a user, or its own internal system, creating products from different entrances, it is necessary to solve the problem of duplicate product creation.

So, how to solve this problem?

The fastest and most effective way to solve the problem of duplicate commodity data is to build a unique index on the table.

The idea is good, but we have a specification on our side: the business tables must all be tombstoned.

And we all know that if you want to delete a record in a table, if you use the delete statement.

For example:

This delete operation is a physical deletion, that is, after the record is deleted, it is basically impossible to find out through the sql statement. (However, it can be recovered through other technical means, that is a later story)

There is another type of tombstoning, which is mainly operated through the update statement.

For example:

Tombstone requires the addition of an additional delete status field to the table to record whether the data was deleted or not. In all business queries, it is necessary to filter out the deleted data.

After the data is deleted in this way, the data is still in the table, but the data in the deleted state is logically filtered.

In fact, for such tombstone tables, it is not possible to add a unique index.


Suppose you added a unique index to the name and model in the commodity table, if the user deletes a record, the delete_status set to 1. Later, the user found out that it was not right and added the exact same product again.

Due to the unique index, the second time the user adds an item fails, even if the item has been deleted and cannot be added again.

This problem is obviously a bit serious.

Some people may say: Wouldn’t it be enough to make the name, model, and delete_status three fields into a unique index at the same time?

A: This does solve the problem that users can’t add when they delete a product and then add the same product again. However, if the product was added the second time, it was deleted again. The user added the same product for the third time, didn’t the problem also occur?

It can be seen that if the table has a tombstone function, it is not convenient to create a unique index.

Next, the second solution you think of to solve the data duplication problem might be to add distributed locks.

The most commonly used distributed lock with the highest performance may be the redis distributed lock.

The pseudocode for using redis distributed locks is as follows:

However, locks need to be released in the finally code block.

The lockKey is a combination of the name and model in the commodity table, and the requestId is the unique identifier of each request, so that it can release the lock correctly each time. You also need to set an expiration time expireTime to prevent the release of the lock from failing, and the lock has always existed, causing subsequent requests to fail to acquire the lock.

If it is only a single product, or a small number of products need to be copied and added, there is no problem in adding a distributed lock.

The main process is as follows: you can try to lock it before copying and adding items. If the lock is successful, the product is queried for existence, and if it does not, the item is added. In addition, if the lock fails in the process, or if it does not exist when the item is queried, it is returned directly.

The purpose of adding a distributed lock is to ensure that the two operations of querying and adding goods are atomic.

But the problem now is that we need to copy and add a lot of goods this time, and if we add a distributed lock for each added product, it will affect the performance very much.

Obviously, for bulk interfaces, adding redis distributed locks is not an ideal solution.

As we have already talked about, in the interface of batch copy of goods, we are adding goods through the sequence message of RocketMQ, single-threaded asynchronous copying, which can temporarily solve the problem of product duplication.

But that only changed the entrance to add goods, and there are other entrances to add products.

Can you unify the underlying logic of adding goods, and finally call the same piece of code. Items are then added asynchronously via RocketMQ’s sequential messages.

The main flow is shown in the following figure: this really solves the problem of duplicate products.

But it also raises two other questions:

Therefore, after considering all aspects of the factors, this plan was finally rejected.

In fact, there is such a syntax in mysql, that is, insert on duplicate key update.

When adding data, mysql finds that the data does not exist, then directly inserts. If the data is found to already exist, update the operation.

However, a unique index or PRIMARY KEY is required in the table so that when the two values are the same, the update operation is triggered, otherwise it is an insert.

The problem now is that PRIMARY KEY is the primary key of the commodity table, which is generated in advance according to the snowflake algorithm, and it is impossible to produce duplicate data.

However, due to the tombstoning function of the commodity table, the unique index cannot be created in the commodity table.

Therefore, insert on duplicate key update this set of solutions, temporarily can not be used.

In addition, insert on duplicate key update in the case of high concurrency, may produce deadlock problems, need to pay special attention to.

There is also such a syntax in mysql, i.e., insert … ignore。

During the execution of the insert statement: mysql finds that if the data is duplicated, it is ignored, otherwise it will be inserted.

It is mainly used to ignore the Duplicate entry ‘XXX’ for key ‘XXXX’ exception resulting from inserting duplicate data.

However, a unique index or PRIMARY KEY is also required in the table.

However, due to the tombstoning function of the commodity table, the unique index cannot be created in the commodity table.

It can be seen that this scheme does not work either.

As a warm reminder, use insert … ignore may also cause deadlocks.

I talked about it before, because there is a tombstone function, adding a unique index to the commodity table does not work.

Later, it is said that adding distributed locks, or adding goods asynchronously through mq single thread, affects the performance of creating goods.

So, how to solve the problem?

Can we think differently, add an anti-weight table, and add the name and model fields of the commodity table as unique indexes in the anti-weight table.

For example:

The id in the table can use the id of the commodity table, and the name and model in the table are the name and model of the commodity table, but the unique indexes of these two fields are added to this anti-weight table.

The field of vision was suddenly opened.

Before you add product data, add a weight sheet. If the addition is successful, the product can be added normally, and if the addition fails, there is duplicate data.

If the anti-repetition table fails to be added, subsequent business processing should be based on actual business requirements.

If a duplicate is found when a batch of products is allowed to be added on the business, and the exception is directly thrown, the user can be prompted to refresh the page and try again.

For example:

When inserting data in batches, if duplicate data occurs, the DuplicateKeyException exception is caught and converted into a business exception when running such as BusinessException.

There is also a business scenario that requires that even if there is a duplicate product, no abnormality is thrown, so that the business process can go on normally.

For example:

When inserting data, if duplicate data occurs, the DuplicateKeyException is captured, and the product data is queried again in the catch code block, and the existing products in the database are returned directly.

If the interface of adding goods synchronously is called, the key point here is to return the id of the existing data, and the business system does the follow-up operation, and the id operation must be taken.

Of course, before execute, you still need to check whether the product data exists, if it already exists, directly return the existing data, and if it does not exist, the execute method is executed. This step must not be less.

For example:

Be sure to note: The reload table and the operation of adding goods must be in the same transaction, otherwise there will be problems.

By the way, it is also necessary to do a special treatment for the deletion function of the product, and while the item table is logically deleted, the anti-weight table is physically deleted. Use the product table id as a query criterion.

To be honest, there are many solutions to solve the problem of duplicate data, there is no best solution, only the most suitable business scenario, the best solution.

If this article is helpful to you, or inspired, help scan the QR code to pay attention to it, your support is the biggest motivation for me to keep writing.

Ask for one click and three links: like, forward, watching.

Pay attention to the public number: [code brother byte], reply in the public account: information, Redis has excellent fan benefits, and reply: add group, you can communicate and learn with many BAT manufacturers.