MySQL architecture design for ordering function in e-commerce system

The basic model of a simple order business is designed to include users, goods (inventory), orders, and payments. Only goods and orders are considered here. The process is to place an order -> reduce inventory. These two steps must be completed at the same time. You cannot place an order without reducing it. Inventory (oversold), or reduced inventory without generating an order (undersold). Overselling merchants have insufficient inventory, and consumers cannot buy items when placing orders, resulting in a bad experience; underselling merchants have overstocked inventory or need to repeatedly modify product information, which is troublesome and the experience is not good.

In the early days of the system, the traffic received was small, and many entrepreneurial teams adopted a single-repository model (yes, everyone was together...). This model brings great convenience. It does not need to cross databases, let alone cross nodes. It can easily use the transactions provided by the database to implement atomic operations of placing orders and reducing inventory, and can also perform various joint tables and subqueries (operations No matter how abnormal MM’s needs are, what can I do if I don’t know SQL?) But it is precisely these advantages that will become a stumbling block to expanding the system after the traffic increases. Joint tables, subqueries, and transactions all bind multiple tables together, making it troublesome to dismantle databases and tables.

In the later period, the system traffic will gradually increase, and the read and write performance of a single database is not enough. At this time, the database will be considered to be dismantled and divided into tables. For example, products and orders are divided into two clusters, and the clusters are divided into databases and tables according to their respective business dimensions. Products can be divided according to the seller dimension, and orders are generally divided according to the buyer dimension, and redundancy is made according to the seller dimension. The problem that arises at this time is still a classic problem - data consistency. After the data is split, the products and orders are not in the same database, how to ensure consistency; how to ensure consistency between the order data in the buyer dimension and the order data in the seller dimension. There are two solutions:

(1) Distributed transactions, the classic implementation is based on 2PC. The advantage is that after being packaged well enough, although there are differences between using it and a single library (mainly complex query statements), overall the changes to the business will not be great. The disadvantage is that the performance is too poor. Originally, the introduction of distributed databases was mainly to improve performance exponentially, but because of the introduction of distributed transactions, this performance improvement was greatly reduced. In many cases, this performance is unacceptable.

(2) Message middleware, the protagonist of this article, one of the functions of message middleware is to be responsible for communication between various systems, which is very suitable for the synchronization problem of goods and order systems here. The ordering process after introducing the message middleware is: after user A places an order, he sends a message to the message middleware. The product system subscribes to the order message and deducts the corresponding inventory.

There are a few points to note here:

a. The transmission of messages takes time. Check the inventory before placing an order. However, under concurrent conditions, the inventory may not be enough when the inventory is actually reduced, so it must be done after the inventory reduction is successful. To show that the order is successful, that is, after placing the order, it is marked as placed, but the status is not visible to the user. After the product system successfully reduces the inventory, the order system is notified to update the status (it is still the use of message middleware);

b , The reliability of the message is very high. When sending a message, if you return successfully, you must ensure that the message will be delivered. If the message fails, you need to roll back the order by yourself;

c. High reliability of the message means that there will be duplicate messages. , here the product system needs to be idempotent by itself, and can use the message ID to remove duplication, otherwise it will be sold less;

d. Front-end users want to be notified as soon as possible if the order is successful or unsuccessful, so a timer needs to be set after the order is successfully placed. message, if the order inventory has not been deducted successfully after a period of time, the user should be notified of the order failure at this time, and the excess deducted inventory should be replenished regularly.

The introduction of message middleware can effectively solve the problem of distributed database data synchronization and avoid distributed transactions. And the additional benefit is that it reduces concurrent lock contention when reducing inventory, which improves performance.