CODEWITHSUNDEEP
mysql
KayakinKoder
KayakinKoder

Reputation: 3461

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

From my reading I believe SELECT FOR UPDATE would be used to accomplish the following, but I read a comment akin to "row locking does not stop reads", which makes me think I am missing something. Pardon my pseudocode.

table 'first_names'

`name`   `used`
mike     1
jeff     null
mark     null

I need to get a name from this table, but absolutely 100% ensure it has not ever been used before and does not get used by anything else. (a fake example, lets say we autoassign user names -- which must be unique -- when someone creates an account on our website) To accomplish this, when I get/use a name from this table we set used = 1.

But the problem of course is two separate processes doing a select where used <> 1 could both get "jeff", if process #1 has not update used = 1 before process #2 runs select where used <> 1.

Does SELECT FOR UPDATE in a transaction solve this problem? I know that it locks the row so that another process cannot write to that row, but we are doing a read, a select. Meaning process #1 and process #2 both run a select where null <> 1 at the same time, so get the same row (jeff). process #1 writes to the row, update used = 1, releases the lock, and now process #2 also writes to the row....and now the row jeff has been used twice.

Upvotes: 1

Views: 74

Answers (1)

JeffUK
JeffUK

Reputation: 4241

A different approach would be to have a log/transaction table. 

 NameUsed
----------
 Mike

You first check that the name hasn't been used by using

select count(*) from log where nameused='jeff'

This returns '0' to both processes, relatively certain the name is free both processes run:

Insert into log (NameUsed) values('jeff');

The trick is that NameUsed is a UNIQUE column and your DBMS will only allow one of your processes to successfully insert the name, one will fail with 'Must be Unique' which is more helpful than 'Locked by another user'.

This method optimises for write speed and minimised locking of your main table.

Checking if a name has been used is little slower though, but not much with proper indexing.

Upvotes: 1

Related Questions