I want to create a table with a long varchar column, maybe it's the url.
according to dns spec, the url's max length is fixed. but I have
to deal with url having long params such as
I want the url is unique when inserting it.
I googled and found
this post suggests use md5 of url. But in theory, there will be
conflict that two different urls will have the same md5(even it's
probablitiy is very small). I want to a absolute correct solution.
one method i can come up with is using select ... for update
1. begin transaction
2. select url from tb where md5='xxxx' for update
3. if the url is not exist, insert into this url; else do nothing
also I need to set the transaction isolation to SERIALIZABLE
is this solution correct in multithread/process environment?
another method is using trigger to check whether the url exist. I
don't know whether this method will be faster than previous one. how
to ensure it's correctness in multithread environment?