mysql 队列 高并发_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;

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 = where user_id is NULL limit 1;

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.

解决方案

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 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.

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:

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.

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.

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:

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

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

Hope this helps!