MySQL Forums
Forum List  »  Performance

lockfree approach to creating a single unique row without unique index
Posted by: Andrew Nuss
Date: January 03, 2015 03:43PM

Hi,

I want to maintain a table of all unique urls that I have ever encountered and associate them with a surrogate id. I don't want to use pessimistic locks. The url is up to 5000 chars, too big to put a unique index on it. Instead, aside from the primary key (id), I put a NON-unique index on a "uniquifier" column which is a sha256 checksum of the url, 64 chars.

My game plan is to have a getCreate function that creates a candidate row which is initially marked isPermanent=false. Given that it is remotely possible that there are several competing threads trying to get the new surrogate id for the same url, these threads could try to put duplicate candidates for the same url/checksum. The goal is with a (pessmistic) lockfree update statement, to have only one thread succeed in marking exactly one of those new rows "permanent", and then each of the threads cleaning up the garbage rows.

I tried this update and it failed because mysql does not allow it with a "You can't specify target table 'LyUrl' for update in FROM clause:

update LyUrl u
set u.isPermanent = true
where u.uniquifier = :uniquifier and u.isPermanent = false and u.url = :url
and not exists (
from LyUrl uu
where uu.uniquifier = :uniquifier and uu.isPermanent = true and uu.url = :url
)
and u.id = (
select min(uuu.id)
from LyUrl uuu
where uuu.uniquifier = :uniquifier and uuu.url = :url
)

(NOTE: this is actually hibernate syntax on the not exists.)

Can anyone advise whether there is a valid mysql update statement that would do the intended job? I was thinking maybe a mirror table might help? Again the idea is that exactly one of the competing threads will succeed in selecting just one candidate row to mark permanent and the rest of the threads will update no rows at all.

Thanks,
Andy

Options: ReplyQuote


Subject
Views
Written By
Posted
lockfree approach to creating a single unique row without unique index
1648
January 03, 2015 03:43PM


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.