Mysql高并发更新 [英] Mysql High Concurrency Updates

问题描述

我有一个mysql表：

I have a mysql table:

CREATE TABLE `coupons` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(255),
    `user_id` INT,
    UNIQUE KEY `code_idx` (`code`)
) ENGINE=InnoDB;

该表包含成千上万的代码，最初是 user_id 为NULL。
现在，我有一个Web应用程序，它为同时访问该应用程序的数千个用户分配了唯一的代码。考虑到流量很高，我不确定什么是正确的处理方式。
我写的查询是：

The table consists of thousands/millions of codes and initially user_id is NULL for everyone. Now I have a web application which assigns a unique code to thousands of users visiting the application concurrently. I am not sure what is the correct way to handle this considering very high traffic. The query I have written is:

UPDATE coupons SET user_id = <some_id> where user_id is NULL limit 1;

应用程序运行此查询的并发性为1000 req / sec。

And the application runs this query with say a concurrency of 1000 req/sec.

我观察到的是整个表被锁定了，而且扩展性不好。
我应该怎么办？
谢谢。

What I have observed is the entire table gets locked and this is not scaling well. What should I do? Thanks.

推荐答案

这个问题可能更适合DBA（但我不是DBA），但我

This question might be more suitable for DBA's (and I'm not a DBA) but I'll try to give you some ideas of what's going on.

在执行更新查询时，InnoDB实际上不会锁定整个表。接下来的工作是：它放置一个记录锁，以防止任何其他事务插入，更新或删除 coupons.user_id 值为NULL的行。

InnoDB does not actually lock the whole table when you perform you update query. What it does is the next: it puts a record lock which prevents any other transaction from inserting, updating, or deleting rows where the value of coupons.user_id is NULL.

当前使用查询（取决于user_id为NULL），您将无法进行并发，因为您的事务将一个接一个地运行，而不是并行运行。
即使您的 coupons.user_id 上的索引也无济于事，因为当您锁定InnoDB时，如果您没有阴影索引，它会为您创建一个影子索引。结果将是相同的。

With your query you have at the moment(which depends on user_id to be NULL), you cannot have concurrency because your transaction will run one after another, not in parallel. Even an index on your coupons.user_id won't help, because when putting the lock InnoDB create a shadow index for you if you don't have one. The outcome would be the same.

因此，如果您想提高吞吐量，我可以想到两种选择：

So, if you want to increase your throughput, there are two options I can think of:

1. 以异步模式将用户分配给优惠券。将所有分配请求放入队列，然后在后台处理该队列。可能不适合您的业务规则。


2. 减少锁定记录的数量。这里的想法是在执行更新时锁定尽可能少的记录。为此，您可以在表中添加一个或多个索引列，然后在的 WHERE 子句中使用索引更新查询。

1. Assign a user to a coupon in async mode. Put all assignment request in a queue then process the queue in background. Might not be suitable for your business rules.
2. Decrease the number of locked records. The idea here is to lock as less records as possible while performing an update. To achieve this you can add one or more indexed columns to your table, then use the index in your WHERE clause of Update query.

列的示例是product_id或类别，也许是用户位置（国家/地区，邮政编码）。
，那么您的查询将如下所示：

An example of column is a product_id, or a category, maybe a user location(country, zip). then your query will look something like this:

更新优惠券SET user_id = WHERE product_id = user_id为NULL LIMIT 1;

UPDATE coupons SET user_id = WHERE product_id = user_id is NULL LIMIT 1;

现在InnoDB将只锁定 product_id =< product_id> 的记录。这样，您将可以并发。

And now InnoDB will lock only records with product_id = <product_id>. this way you you'll have concurrency.

希望这会有所帮助！

这篇关于Mysql高并发更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！