MySQL Forums
Forum List  »  Connector/JDBC and Java

Slow Concurrent Updates
Posted by: Felix Baker
Date: July 31, 2017 02:31AM

Hi Guys,

We have a requirement for a high concurrency table 100+ requests per second.

It is a table that contains a bunch of unique key-codes, these are assigned a request_guid as they are requested.

The table contains an integer primary key, a unique keycode and a null request_guid.

We are running the below query, it works ok with 10,000 records however with 1million+ records it grinds to a halt and each update takes 16+ seconds.

Table:
(id INT
keycode VARCHAR(50)
request_guid VARCHAR(45) NULL)

Concurrant Query:
UPDATE coupon
SET request_guid = ?
WHERE request_guid IS NULL
ORDER BY RAND() LIMIT 1;

The RAND() order is being used otherwise we get locking issues when the same row is trying to be updated simultaneously.

Can anyone think of a better way of doing this to improve performance? Potentially the keys should be in a separate table?

I hope the above makes sense, thanks for your help!

Cheers,

Felix

Options: ReplyQuote


Subject
Written By
Posted
Slow Concurrent Updates
July 31, 2017 02:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.