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.

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

Concurrant Query:
UPDATE coupon
SET request_guid = ?
WHERE request_guid IS NULL

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!



Options: ReplyQuote

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