MySQL select for update, to ensure a row/value is only used one time? -

from reading believe select update used accomplish following, read comment akin "row locking not stop reads", makes me think missing something. pardon pseudocode.

table 'first_names'  `name`   `used` mike     1 jeff     null mark     null 

i need name table, absolutely 100% ensure has not ever been used before , not used else. (a fake example, lets autoassign user names -- must unique -- when creates account on our website) accomplish this, when get/use name table set used = 1.

but problem of course 2 separate processes doing select used <> 1 both "jeff", if process #1 has not update used = 1 before process #2 runs select used <> 1.

does select update in transaction solve problem? know locks row process cannot write row, doing read, select. meaning process #1 , process #2 both run select null <> 1 @ same time, same row (jeff). process #1 writes row, update used = 1, releases lock, , process #2 writes row....and row jeff has been used twice.

a different approach have log/transaction table.

 nameused ----------  mike 

you first check name hasn't been used using

select count(*) log nameused='jeff'

this returns '0' both processes, relatively name free both processes run:

insert log (nameused) values('jeff');

the trick nameused unique column , dbms allow 1 of processes insert name, 1 fail 'must unique' more helpful 'locked user'.

this method optimises write speed , minimised locking of main table.

checking if name has been used little slower though, not proper indexing.