Shulou(Shulou.com)06/01 Report--

Two coquettish operations for updating hot lines with large concurrency

Tag: insert_slot select..for_update rand

For the performance of db operations to be high enough, ingenious design is important, and the scope of transactions should be as small as possible. In general, we use a certain orm framework to operate db. Most of these frameworks are implemented by boasting that the network interacts multiple times to open the transaction context and perform sql operations. It is a black box, including some differences in the timing of setting autocommit. If you don't pay attention to it, you will step in the hole.

In the case of large concurrency and boasting network interaction for many times, it is inevitable that the execution time of the transaction is too long due to network delay, packet loss and other reasons, and the avalanche probability will be greatly increased. It is recommended to use orm as little as possible in scenarios with high performance and concurrency requirements, and if necessary, try to control the scope and execution time of transactions.

The principle of large concurrent db operation is to minimize the cross-network interaction of transaction operations. Once cross-network transactions are used, optimistic locks are used as much as possible, pessimistic locks are used less, and the current time of holding locks in session is shortened as much as possible.

Here are two coquettish operations for large concurrent update rows on mysql innodb engine, both of which are designed to minimize the scope and time of db locks.

Convert update to insert

One of the more common large concurrency scenarios is the update of hot data, such as inventory, accounts with budget, and so on.

In principle, update requires innodb engine to get row data first, then convert row format to mysql service layer, then modify the data through mysql server layer, and finally write it back through innodb engine.

Every step of the process has some overhead, first an innodb query, then a row format (if the row is wide, the performance loss is still large), and finally an update and a write, which takes about four small stages.

A update requires the cost of the above four processes. At this point, if the qps is very large, there must be some performance overhead (peak trimming such as cache and mq are not considered here). So can we spread out the hotspots of a single line and convert update to insert at the same time? let's see how to do it.

We introduce the concept of slot, the original row we use multiple row to represent, the results are summarized by sum. In order not to make slot a bottleneck, we rand slot, then convert update to insert, and resolve conflicts through the on duplicate key update clause.

We create a sku inventory table.

CREATE TABLE `tb_sku_ stock` (`id`bigint (20) unsigned NOT NULL AUTO_INCREMENT, `sku_ id` bigint (20) NOT NULL, `sku_ stock`int (11) DEFAULT '0mm, `slot` int (11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_sku_ slot` (`sku_ id`, `slot`), KEY `idx_sku_ id` (`sku_ id`) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4

The unique index idx_sku_slot in the table is used to constrain different slot of the same sku_id.

Inventory increase operation and reduction operation should be dealt with separately, let's first look at the increase operation.

Insert into tb_sku_stock (sku_id,sku_stock,slot) values (101010101, 10, round (rand () * 9) + 1) on duplicate key update sku_stock=sku_stock+values (sku_stock)

We add 10 stocks to sku_id=101010101, and use round (rand () * 9) + 1 to control the slot to less than 10 (can be relaxed or reduced according to the situation). When the unique key does not conflict, it will always be insert, and once duplicate occurs, update will be executed. (update is also distributed)

Let's take a look at reducing inventory, reducing inventory is not as simple as increasing inventory, the biggest problem is to do pre-inspection, can not be overdeducted.

Let's first look at the total inventory check, for example, we deduct 10 inventory.

Select sku_id, sum (sku_stock) as ssfrom tb_sku_stockwhere sku_id= 101010101group by sku_id having ss > = 10 for update

Mysql queries use mvcc to achieve lock-free concurrency, so for real-time consistency we need to add for update to do real-time checking.

If the inventory is deductible enough, then we will perform the insert into select insert operation.

Insert into tb_sku_stock (sku_id, sku_stock, slot) select sku_id,-10 as sku_stock,round (rand () * 9 + 1) from (select sku_id, sum (sku_stock) as ss from tb_sku_stock where sku_id= 101010101 group by sku_id having ss > = 10 for update) as tmpon duplicate key update sku_stock= sku_stock+ values (sku_stock)

The whole operation is performed in a db interaction, if you control the amount of data in a single table and unique key coordination performance is very high.

Eliminate select...for update

In large OLTP systems, there are some tasks that need to be performed periodically, such as orders that are settled periodically, agreements that are cancelled periodically, and many check and reconciliation procedures that check the status data within a certain time range. These tasks generally need to scan a status field in the table.

These queries are basically based on similar status status fields, and because the degree of discrimination is very low, the index is basically not very useful in such scenarios.

In order to ensure that the scanned data will not be executed concurrently, an exclusive lock is added to the data, usually select...for update, so that this part of the data will not be read repeatedly. But it also means that the current db thread will block on this lock, which is a serial operation.

Because it is an exclusive lock, the insert and update of the data will be affected, and Gap lock (gap lock) will be triggered in the case of repeatable read (repeatable readable) and no unqiue key.

There is a way to eliminate select...for update and improve the ability to process data concurrently.

CREATE TABLE `tb_ order` (`id` bigint (20) unsigned NOT NULL AUTO_INCREMENT, `order_ id` bigint (20) NOT NULL, `order_ status` int (11) NOT NULL DEFAULT '0mm, `task_ id` int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Let's simply create an order table. Task_id is the task id. First, let the data structure support multi-task parallelism.

Select order_id from tb_order where order_status=0 limit 10 for update

The general practice is to lock the line through select...for update. Let's find another way to achieve the same effect without the problem of concurrent execution.

Update tb_order set task_id=10 where order_status=0 limit 10 Chinese query OK, 4 rows affectedselect order_id from tb_order where task_id=10 limit 4

Suppose we currently have a lot of parallel tasks (1-10). Suppose the task_id=10 task executes and update preempts its own data rows first. This operation is basically in the singular ms, and then you can get the rows belonging to the current task with your own taskid through select, and you can also take the exact limit, because update returns the number of affected rows.

There is a problem here, that is, what to do if the executed task terminates for some reason. The simple way is to use a pocket job to periodically check the task for more than a certain period of time, and then set the task_id to be empty.

Author: Wang Qingpei (Qitoutiao Tech Leader)

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.