Tagging and how to test a tag exists

Question

I plan to store tags (like the ones on Stackoverflow) using the Toxi scheme Tags are stored in a separate table.

create table tags
(
   tagSeq int unsigned,
   name varchar(255),
   PRIMARY KEY (tagSeq),
   UNIQUE INDEX (name)
);

In the add a tag usecase, multiple concurrent threads will be attempting to check if a particular tag exists; thus it will be a point of contention. If the tag exists, the threads just pull out the tagSeq and adds a row in the intermediate table that links it with the item being tagged.

How do I design this if exist use tagSeq, if not create use case such that I have high concurrency while maintaining data integrity ? What isolation level should I use for example ?

My current solution is for every incoming thread to just perform an INSERT. If I get a UNIQUE KEY constraint violation, I just read back the tagSeq. If not, I create it. Am I on track ?

Answer 1

why don't you simply lock the tables during the check/insert?

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html